SQL Object Where Used List by a Search SQL Database Objects Text
Do you frequently search SQL text in SQL Server database objects including sql stored procedures, user defined functions, sql triggers, view definitions, etc ?
I do. And I know it is a very common requirement for SQL Server database administrators and sql developers to search in which sql objects like an sql stored procedure, sql trigger, sql view or a database table is used.
This is a requirement for sql object where used or a text search in sql objects especially sql stored procedures, sql functions or views.
There are third part sql search tools in the market for use of sql developers and administrators like Idera SQL Search which is a part of SQL Admin Toolset.
This sql search tool is a very advanced sql tool which lets you find words or phrases in SQL code, enables you search and replace text, words or phrases in sql codes, etc.
Perhaps you may want to look at the product details of SQL Search at software vendor's product page.
But if you don't need those advanced features, just find words or text in sql code and display it, or if you don't want to pay money for this solution, you can continue reading this sql tutorial.
One way of to search sql text in sql objects like sql procedures, views and sql functions which uses a specific object is to run a t-sql SELECT statement on the text definitions of all database objects like SPs, views and tsql functions.
The text definitions of all objects are stored in text column of syscomments system table.
Below sql programmers will find a sql stored procedure which searches a specific keyword in all sql definitions of database objects.
CREATE PROCEDURE dbo.SearchInObjectsText
(
@SearchString nvarchar(max)
)
AS
SELECT
USER_NAME(uid) [user],
name,
CASE xtype
WHEN 'FN' THEN 'Function'
WHEN 'P' THEN 'Stored procedure'
WHEN 'TF' THEN 'Function'
WHEN 'TR' THEN 'Trigger'
WHEN 'V' THEN 'View'
ELSE xtype
END [type],
'sp_helptext ''' + USER_NAME(uid) + '.' + name + ''''
AS 'sp_helptext command'
FROM sysobjects (NoLock)
WHERE Id IN (
SELECT
DISTINCT id
FROM syscomments (NoLock)
WHERE [text] LIKE '%' + @SearchString + '%'
)
The above SQL Server stored procedure is created to seach sql text in all SQL Server database objects.
This sql search tool will search for text anywhere in sql database and sql codes.
As a result the output list will contain all database objects containing the search word or phrase in its metabase sql definition.
How to Find Keywords in SQL Server Database Objects with a Sample
T-SQL developers can call the SearchInObjectsText sql stored procedure for searching an sql object within other SQL Server database objects like sql stored procedures, sql views and user defined sql functions.
Here is a sample sql code which you can use to find keywords in SQL Server database objects.
DECLARE @SearchString nvarchar(max)
SET @SearchString = N'state'
EXEC SearchInObjectsText @SearchString
In my SQL Server sample database the above where used search for text 'state' returned me the following sql result set.
The search sql text 'state' using the SearchInObjectsText stored procedure returned me a list of sql procedures and a trigger where the searched text is used.
I hope sql developers find this T-SQL sample stored procedure useful for their productivity on MS SQL Server databases.
It is also possible to limit the T-SQL SELECT query by limiting the SQL Server search stored procedure text only.
For searching a text only in SQL stored procedures it is enough to add the criteria "xtype = 'P'" to the WHERE clause of the SELECT statement.
In the same manner "xtype = 'V'" criteria will limit the search only in the SQL VIEW definitions.
An alternative method to search SQL text in SQL Server stored procedures and sql functions is demonstrated with sql example at sql tutorial How to Search SQL Text Anywhere in SQL Server Database using INFORMATION_SCHEMA.ROUTINES System View.
Related SQL Server tutorials for database administrator and SQL programmers:
Find SQL Server Views Where Table is used and List Tables in a View