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 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 2017
download SQL Server 2016
download SQL Server 2014



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:

search SQL text using information_schema.routines view

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






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