SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software Kodyaz SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014



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.

SQL Server search text in SQL objects where used list

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






Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems