How to Search SQL Text Anywhere in SQL Server Database
Many developers face the problem to search SQL text anywhere in the sql database objects definition.
If sql developers have an sql search tool which can be used to find specific keywords in SQL Server object definitions, then the solution to search string in sql is easy.
On the other hand, most sql administrators and t-sql developers don't have such a sql search tool.
So just a I do, you may prefer a t-sql script which will search given text as a parameter and list all sql database objects as output.
Those database objects must contain sql objects like sql stored procedures, sql triggers, user defined functions, sql views, etc.
Here is a simple yet successfull solution to search text in sql database objects definitions.
The below t-sql select statement will benefit from Information_Schema.Routines view.
SELECT
Routine_Type,
Routine_Schema,
Routine_Name,
Routine_Definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
Routine_Definition LIKE N'%create%'
The Information_Schema.Routines view which we can use to find a keyword in sql definitions is based on sysobjects and syscolumns system tables.
The t-sql select statement which can be considered as a simple sql search tool, returns Routine_Type which identifies that the object is a stored procedure or a sql function.
Routine_Schema is the owner name of the stored procedure or the sql function.
Routine_Name is the name of the sql object which we are searchin for. This sql object's definition contains the filtered keyword or the search text.
Routine_Definition column returns the definition text of the stored procedure or the sql function.
Here in this column the sql text or the search keyword exists.
Note that if the procedure or the sql function is encrypted, it is impossible to make search text within those objects' definitions.
Therefore encrypted procedures and sql functions are not included in the select list of the above sql query.
Search SQL Text Example Code
Using sample database AdventureWorks2008R2 on a MS SQL Server 2008 R2 instance, I search sql text 'Product' to find the where used list.
Actually I'm looking for sql procedures and sql functions where sql table named Product is used.
SELECT
Routine_Type,
Routine_Schema,
Routine_Name,
Routine_Definition
FROM INFORMATION_SCHEMA.ROUTINES
WHERE
Routine_Definition LIKE N'%Product%'
The where used list of the keyword 'Product' is used in sql objects is as follows:
If you are looking for an alternative solution, you can refer to sql tutorial SQL Object Where Used List by a Search SQL Database Objects Text.
Related SQL tutorials for SQL Server database administrator and developers:
Find SQL Server Views Where Table is used and List Tables in a View