On occasion it is handy to have a SQL snippet to find SQL views in a database that are using a particular table, column, function, or other SQL. Fortunately SQL Server offers several information schema views to help out.
Using the system view INFORMATION_SCHEMA.VIEWS you can query the views definition (think the SQL you used to create the view). It is as simple as searching the system views VIEW_DEFINITION column. See the example below:
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.VIEWS WHERE VIEW_DEFINITION LIKE '%search criteria%'
Similarly you can search stored procedures using the example below:
SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%search criteria%'
Applies to: MS SQL Server 2005
1 Comment
anon
Note that the view_definition only returns the first 4k of text data, so it may not be searching the entire view.
15 Dec
Leave a Comment