TSql – Tidbits

Posted: 2009-10-27 in Sql
Tags:

Short tidbits to help my fading memory

List all databases and tables on server

 SELECT name FROM master..sysdatabases
 -- list all tables in current db
 SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE '%'

Search Everything

Search All searchable columns and tables for a text

-- creates the queries that search everything, everywhere
 -- warning not very friendly to the db

DECLARE @searchString varchar(50)
 set @searchString = 'Leaflet no.1 provided by customer'

<!--more-->

SELECT 'select ''' + TABLE_NAME + ':' + COLUMN_NAME +''' as ''Table-Column'' , * from ' + TABLE_SCHEMA + '.' + TABLE_NAME + ' where ' + COLUMN_NAME + ' like ''%'+ @searchString + '%'''
 FROM INFORMATION_SCHEMA.COLUMNS
 where DATA_TYPE in('nchar', 'nvarchar', 'varchar', 'char')

 Search Stored Proceedures

v2

 DECLARE @searchString varchar(50)
 set @searchString = 'myStringToFind'

select object_name(object_id),definition
 from sys.sql_modules
 where definition like '%'+ @searchString + '%'
 order by object_name(object_id)
DECLARE @searchString varchar(50)
 set @searchString = 'myWord'

select object_name(m.object_id),m.definition,

<!--more-->
 case WHEN p.object_id is null THEN 'v' ELSE 'p' end as [type]
 from sys.sql_modules m
 left join sys.procedures p on m.object_id = p.object_id
 where
 definition like '%'+ @searchString + '%'
 -- and p.object_id is not null -- only views
 order by object_name(m.object_id)

General CTE Syntax

Note that the RECURSIVE section only joins back on the last recursive results not the sum of the CTE results.

 With nameOfCTE(columnsOfCTE)
 (
 --HOOK
 select something
 from somewhere
 union all
 --Recursive section (optional)
 select something
 from nameOfCTE
 where recursiveLink = nextRecursion'
 )

CTE’s Recursion Example for missing dates

 WITH dateTable AS (
 SELECT  CONVERT(nvarchar(20), min(timestamp), 112 )AS dt
 from logger_log
 UNION ALL
 SELECT CONVERT(nvarchar(20), DATEADD(dd, 1, dt), 112 )
 FROM dateTable s
 WHERE DATEADD(dd, 1, dt) <= getdate()
 )
 select t2.a, sum(CASE WHEN [timestamp] is null THEN 0 ELSE 1 END)
 from (
 select CONVERT(nvarchar(20), t1.dt, 112 )AS a, logger_log.[timestamp]
 from (select dt FROM dateTable ) as t1
 left join logger_log on CONVERT(nvarchar(20), [timestamp], 112 ) = t1.dt
 ) as t2
 group by t2.a
 order by t2.a desc
 OPTION (MAXRECURSION 32767)

Sql Sequential Guid’s

Statement 1: Guid’s should be your PK Ids or RowGuid, they make the development much easier.
Statement 2: Just becauses its your rowId doesn’t mean it should be your clustered index.

select NEWSEQUENTIALID()

C#Sequential Guid’s

from site stackoverflow…

 [DllImport("rpcrt4.dll", SetLastError = true)]
 static extern int UuidCreateSequential(out Guid guid);
 public static Guid SequentialGuid()
 {
 const int RPC_S_OK = 0;
 Guid g;
 if (UuidCreateSequential(out g) != RPC_S_OK)
 return Guid.NewGuid();
 else
 return g;
 }

Force an Index

Useful for testing execution plans and index optimisation

 SELECT i.indid, i.name FROM sysindexes i
 INNER JOIN sys.sysobjects o ON o.ID = i.id
 WHERE o.Name = 'tablename'

SELECT *
 FROM tablename WITH (INDEX = 1)
 WHERE Column1 = 'value'

Identity Insert

SET IDENTITY_INSERT myTable ON

insert into myTable (ID, etc,etc)
 select 1, 'etc','etc'

SET IDENTITY_INSERT myTable OFF

Cheers
Choco

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s