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:
