Some usefull (internal) SQL scripts

This is a small collection of usefull (internal) SQL scripts.

Search all stored procedures

If you need to search all stored procedures to find the procedures that contain a particular table or column.

DECLARE @searchText VARCHAR(200) = '<table or column name>'

SELECT [name], [create_date], [modify_date]

FROM SYS.PROCEDURES

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%'+ @searchText +'%'

AND [type] = 'P'

ORDER BY [name]

Search all tables

If you need to search all tables to find the tables that contains a particular column name.

DECLARE @searchText VARCHAR(200) = '<column name>'

SELECT t.name AS table_name, SCHEMA_NAME(schema_id) AS schema_name, c.name AS column_name

FROM SYS.TABLES AS t

INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID

WHERE c.name LIKE '%' + @searchText + '%'

ORDER BY schema_name, table_name

Find tables with name

If you need to find all the tables which contains a specific text in the name

DECLARE @searchText VARCHAR(200) = '<some text>'

SELECT Name, create_date, modify_date

FROM SYS.TABLES

WHERE name LIKE '%'+ @searchText +'%'

Indexes

When examining the execution time for complex queries, one can get information about which indexes are defined for tables in a given database.

SELECT

schema_name(schema_id) AS SchemaName, 

OBJECT_NAME(si.object_id) AS TableName, 

si.name AS IndexName,

(CASE is_primary_key WHEN 1 THEN 'PK' ELSE '' END) AS PK,

(CASE is_unique WHEN 1 THEN '1' ELSE '0' END) + ' ' +

(CASE si.type WHEN 1 THEN 'C' WHEN 3 THEN 'X' ELSE 'B' END)+' '+  -- B=basic, C=Clustered, X=XML

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,1,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,2,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,3,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,4,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,5,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

(CASE INDEXKEY_PROPERTY(si.object_id,index_id,6,'IsDescending') WHEN 0 THEN 'A' WHEN 1 THEN 'D' ELSE '' END) +

'' AS 'Type',

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,1) AS Key1,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,2) AS Key2,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,3) AS Key3,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,4) AS Key4,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,5) AS Key5,

INDEX_COL(schema_name(schema_id)+'.'+OBJECT_NAME(si.object_id),index_id,6) AS Key6

FROM SYS.INDEXES AS si

LEFT JOIN SYS.OBJECTS AS so ON so.object_id=si.object_id

WHERE index_id > 0 -- omit the default heap

AND OBJECTPROPERTY(si.object_id, 'IsMsShipped') = 0 -- omit system tables

AND NOT (schema_name(schema_id) = 'dbo' 

AND OBJECT_NAME(si.object_id) = 'sysdiagrams') -- omit sysdiagrams

ORDER BY SchemaName, TableName, IndexName

Get specific index usage

It is possible to find out which of the defined indexes are being used. The following script retrieves statistics for the indexes, where you can see how many times they have been called.

SELECT

OBJECT_NAME(S.OBJECT_ID) AS [OBJECT NAME],

I.[NAME] AS [INDEX NAME],

I.Type_Desc AS [Index Type],

USER_SEEKS,

USER_SCANS,

USER_LOOKUPS,

USER_UPDATES

FROM SYS.DM_DB_INDEX_USAGE_STATS AS S

INNER JOIN SYS.INDEXES AS I ON I.OBJECT_ID = S.OBJECT_ID 

AND I.INDEX_ID = S.INDEX_ID

WHERE I.Name IS NOT NULL

AND OBJECT_NAME(S.OBJECT_ID) = '<table name>'

ORDER BY S.OBJECT_ID, USER_SEEKS DESC, USER_SCANS DESC