SQL Server Dictionary Search

Sometimes I am thrown into a situation where I need to find a column of parameter in tables, stored procedures and other objects. For the most part it may be a quick and dirty treasure hunting mission where I need to find all the different places a field such as “emailAddress” is found.

I would simply search for the column name using the following dictionary query that i wrote.

DECLARE @SearchString VARCHAR(100)
SET @SearchString = ’email’
SELECT
o.id [Object Id]
,o.type
,CASE o.type
WHEN ‘C’   THEN ‘CHECK CONSTRAINT                    ‘
WHEN ‘D’   THEN ‘DEFAULT OR DEFAULT CONSTRAINT       ‘
WHEN ‘F’   THEN ‘FOREIGN KEY CONSTRAINT              ‘
WHEN ‘K’   THEN ‘PRIMARY KEY OR UNIQUE CONSTRAINT    ‘
WHEN ‘L’   THEN ‘LOG                                 ‘
WHEN ‘P’   THEN ‘STORED PROCEDURE                    ‘
WHEN ‘R’   THEN ‘RULE                                ‘
WHEN ‘RF’  THEN ‘REPLICATION FILTER STORED PROCEDURE ‘
WHEN ‘S’   THEN ‘SYSTEM TABLE                        ‘
WHEN ‘TR’  THEN ‘TRIGGER                             ‘
WHEN ‘U’   THEN ‘USER TABLE                          ‘
WHEN ‘V’   THEN ‘VIEW                                ‘
WHEN ‘X’   THEN ‘EXTENDED STORED PROCEDURE           ‘
ELSE o.type
END [Object Type]
,o.name [Object Name]
,c.name [Column Name]
,c.colOrder
,t.name [Column Type]
,CASE
WHEN t.xtype IN (175,239,231,167) THEN ‘(‘+ CAST(c.length AS VARCHAR) +’)’
WHEN t.xtype IN  (106,60,108,59) AND c.scale IS NOT NULL THEN ‘(‘+CAST(c.prec AS VARCHAR)+’,’+ CAST(c.scale AS VARCHAR)+’)’
ELSE ” END [Length]
FROM
dbo.sysColumns c WITH(NOLOCK) INNER JOIN
dbo.sysObjects o WITH(NOLOCK) ON (o.id = c.id) INNER JOIN
dbo.sysTypes t WITH(NOLOCK) ON (t.xtype = c.xtype)
WHERE
1 = 1
–AND o.type IN (‘P’,’S’,’U’,’V’)

AND o.type IN (‘U’)
— AND c.name  LIKE ‘%’+@SearchString+’%’
AND ( c.name  LIKE ‘%’+@SearchString+’%’ OR o.name  LIKE ‘%’+@SearchString+’%’)
ORDER BY
o.type, o.name,c.colOrder

/*

WHEN ‘C’   THEN ‘CHECK CONSTRAINT                    ‘
WHEN ‘D’   THEN ‘DEFAULT OR DEFAULT CONSTRAINT       ‘
WHEN ‘F’   THEN ‘FOREIGN KEY CONSTRAINT              ‘
WHEN ‘K’   THEN ‘PRIMARY KEY OR UNIQUE CONSTRAINT    ‘
WHEN ‘L’   THEN ‘LOG                                 ‘
WHEN ‘P’   THEN ‘STORED PROCEDURE                    ‘
WHEN ‘R’   THEN ‘RULE                                ‘
WHEN ‘RF’  THEN ‘ REPLICATION FILTER STORED PROCEDURE’
WHEN ‘S’   THEN ‘SYSTEM TABLE                        ‘
WHEN ‘TR’  THEN ‘ TRIGGER                            ‘
WHEN ‘U’   THEN ‘USER TABLE                          ‘
WHEN ‘V’   THEN ‘VIEW                                ‘
WHEN ‘X’   THEN ‘EXTENDED STORED PROCEDURE           ‘

*/
I have recently created a 2nd Dictionary search which I use a lot to find objects that reference a particular table or object. Its simply a search through source code in the database.  While really simple, these queries are particularly useful and should be kept within easy reach.
DECLARE @SearchString VARCHAR(100)
SET @SearchString = ‘%object_name%’
SELECT
o.[name]
,o.[id]
,o.[xtype]
,c.[text]
FROM
[dbo].[sysobjects] o INNER JOIN
[dbo].[syscomments] c ON (o.id = c.id)
WHERE
c.[text] LIKE @SearchString
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