TSQL Tuesday Logo
As a consultant, I’m often thrown into new databases and tables with a wide variety of schema variations. One of the challenges that I’ve often come across, is finding a field that belongs to a table or if the field has an alternative name. Another challenge is, even if you’ve familiar with the database, but the size is overwhelming with so many tables, that looking at a database diagram would be of little help.

 Luckily, if it’s in a SQL Server, then I can use this script to search tables, views, stored procedures, and comments – which is helpful when there is good documentation on changes in the views and stored procedures.

In this example, I’m searching the ReportServer database for the word: Event

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
DECLARE @SEARCH varchar(max) = '%Event%'; 

SELECT b.name, c.name
FROM		
sys.schemas a														
JOIN	sys.all_objects b with (nolock) ON b.schema_id = a.schema_id 
JOIN	sys.columns c with (nolock) ON c.object_id = b.object_id	
WHERE c.NAME LIKE @SEARCH;

SELECT 
SPECIFIC_CATALOG
,SPECIFIC_SCHEMA
,SPECIFIC_NAME
FROM INFORMATION_SCHEMA.ROUTINES with (nolock)
WHERE ROUTINE_DEFINITION LIKE @SEARCH;

SELECT DISTINCT 
S.name AS Schema_Name 
, O.name AS Object_Name 
, C.text AS Object_Definition 
FROM syscomments C 
INNER JOIN sys.objects O ON C.id = O.object_id 
INNER JOIN sys.schemas S ON O.schema_id = S.schema_id 
WHERE C.text LIKE @SEARCH 
OR O.name LIKE @SEARCH 

 

Giving the results in 3 tables: