You are here

Microsoft SQL Server - Find a particular text within Stored Procedure, View and Function or as Column name in a table

Submitted by Asif Nowaj, Last Modified on 2019-11-28

In this tutorial, we’ll discuss on how to find Stored Procedures, Views and Functions which contain a particular text in it. Also, we’ll discuss on how to find tables which has a column name with a particular text.

To demonstrate this topic, SQL Server 2008 R2 Express version is being used. The exact version information is

Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1)

You can find your database version information by executing SELECT @@VERSION

We’ll be using AdventureWorks2008R2 here. If you want to get the AdventureWorks database for 2008R2 please browse https://msftdbprodsamples.codeplex.com/releases/view/59211 and download AdventureWorks2008R2-Full Database Backup.zip. You need to restore the database to use this.

AdventureWorks 2008 R2 Download Link

Also, database user “tutorialuser” is being used here to demonstrate the same.

Please remember the user need to have at least DB DDLAdmin role permission to execute the query on system objects.

Role Permission

1. Find the stored procedures which contain a particular text
2. Find the Views which contain a particular text
3. Find the functions which contain a particular text
4. Find the list of tables which is containing a particular text in its column name

Find the stored procedures which contain a particular text

Okay, let’s move into our first objective to find the stored procedures which contain a particular text. To do so you can execute below query to find out the list of stored procedure name which is containing a text “StandardCost”.

SELECT ROUTINE_NAME, ROUTINE_DEFINITION

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%StandardCost%'

AND ROUTINE_TYPE='PROCEDURE'

This will provide you two stored procedure names. “uspGetWhereUsedProductID” and “uspGetBillOfMaterials”.

Routines Result

But method has a limitation. ROUTINE_DEFINITION contains only first 4000 characters of the stored procedure. So if there is any stored procedure which is big enough [contains > 4000 characters] and the text is being used after 4000 character, you will probably miss it by this method.

So what is the correct method to find out all stored procedure names which contain “StandardCost”.

-- To find the list of stored procedures which contain a particular text

SELECT OBJECT_NAME(OBJECT_ID) [SP_Name], OBJECT_DEFINITION(OBJECT_ID) SP_Definition

FROM sys.procedures

WHERE Object_definition(object_id) LIKE '%StandardCost%'

It will show the result as same like above as there is no stored procedures here which contains more than 4000 characters and contains “StandardCost”.

Stored Procedure Result

Find the Views which contain a particular text

Now let’s move onto the method to find out Views which contains a particular text, let’s suppose “StateProvinceID”.

-- To find the list of stored procedures which contain a particular text

SELECT OBJECT_NAME(OBJECT_ID) View_Name, OBJECT_DEFINITION(OBJECT_ID) View_Definition

FROM sys.views

WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%StateProvinceID%'

It will show below result of having 6 such views

Views Result

Find the functions which contain a particular text

Now let’s quickly see the query that will find the list of functions that contain a particular text.

Here, I’ll be using the INFORMATION_SCHEMA.ROUTINES
to get the function list as I didn’t find any other system object to get the details directly. So the limitation of 4000 characters that is discussed above is applied here.

Below is the query that will enlist all the functions that contain a particular text suppose “ListPrice” .

-- To find the list of functions which contain a particular text

SELECT ROUTINE_NAME Function_Name, ROUTINE_DEFINITION Function_definition

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%ListPrice%'

AND ROUTINE_TYPE ='FUNCTION'

Results shows like

Schema Routines Result

Find the list of tables which is containing a particular text in its column name

Now most important, how to find the list of tables which is containing a particular text in its column name.

Let’s support we are trying to find out the tables that contain column name with “StandardCost”.

-- To find list of tables which containing column name with a particular text

SELECT t.name AS Table_Name, c.name AS Column_Name

FROM sys.tables  AS t

INNER JOIN sys.columns c ON  t.OBJECT_ID = c.OBJECT_ID

WHERE  c.name LIKE '%StandardCost%'

Result shows

Table Column Result

These queries are sometime become very useful when you are moved into a new project and you still do not know much about the database and you are trying find out from where a column is getting updated or value is getting modified. Assuming that the application is not using tables to update the value.

{
"@context": "http://schema.org",
"@type": "BreadcrumbList",
"itemListElement": [{
"@type": "ListItem",
"position": 1,
"item": {
"@id": "https://everyething.com",
"name": "everyEthing",
"image": "https://everyething.com/sites/default/files/everyething_logo.png",
"url": "https://everyething.com"
}
},{
"@type": "ListItem",
"position": 2,
"item": {
"@id": "https://everyething.com/Microsoft-SQL-Server-Find-a-particular-text-within-Stored-Procedure-View-and-Function-or-as-Column-name-in-a-table",
"name": "Microsoft SQL Server - Find a particular text within Stored Procedure, View and Function or as Column name in a table",
"url": "https://everyething.com/Microsoft-SQL-Server-Find-a-particular-text-within-Stored-Procedure-View-and-Function-or-as-Column-name-in-a-table"
}
}]
}

{
"@context": "https://schema.org",
"@type": "Article",
"headline": "Microsoft SQL Server - Find a particular text within Stored Procedure, View and Function or as Column name in a table",
"image": "https://everyething.com/sites/default/files/images/ViewsResult.png",
"author": "Asif Nowaj",
"keywords": "SQL server find text in stored procedure sql server 2008 r2, SQL server find text in stored procedure sql server 2012, SQL server find text in stored procedure sql server, SQL server find text in stored procedure, SQL server get stored procedure containing text SQL Server, sql server find procedure containing text, sql server find column name in all stored procedures, sql find text in stored procedure, find stored procedure in sql server, sql query to find column name in all stored procedure, search sp containing text, sql query to find stored procedure containing text, stored procedure containing text, search column name in stored procedure sql server, sql server find stored procedure containing text, sql query to find stored procedure using a table, sql find stored procedure containing text",
"publisher": {
"@type": "Organization",
"name": "everyething",
"logo": {
"@type": "ImageObject",
"url": "https://everyething.com/sites/default/files/everyething_logo.png"
}
},
"url": "https://everyething.com/Microsoft-SQL-Server-Find-a-particular-text-within-Stored-Procedure-View-and-Function-or-as-Column-name-in-a-table",
"mainEntityOfPage": {
"@type": "WebPage",
"@id": "https://everyething.com/Microsoft-SQL-Server-Find-a-particular-text-within-Stored-Procedure-View-and-Function-or-as-Column-name-in-a-table"
},
"datePublished": "2019-11-28",
"dateCreated": "2017-03-06",
"dateModified": "2019-11-28",
"description": "SQL server find text in stored procedure sql server 2008 r2, SQL server find text in stored procedure sql server 2012, SQL server find text in stored procedure sql server, SQL server find text in stored procedure, SQL server get stored procedure containing text SQL Server, sql server find procedure containing text, sql server find column name in all stored procedures, sql find text in stored procedure, find stored procedure in sql server, sql query to find column name in all stored procedure, search sp containing text, sql query to find stored procedure containing text, stored procedure containing text, search column name in stored procedure sql server, sql server find stored procedure containing text, sql query to find stored procedure using a table, sql find stored procedure containing text",
"articleBody": "In this tutorial, we’ll discuss on how to find Stored Procedures, Views and Functions which contain a particular text in it. Also, we’ll discuss on how to find tables which has a column name with a particular text. To demonstrate this topic, SQL Server 2008 R2 Express version is being used. The exact version information is Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Express Edition with Advanced Services (64-bit) on Windows NT 6.1 (Build 7601: Service Pack 1) You can find your database version information by executing SELECT @@VERSION We’ll be using AdventureWorks2008R2 here. If you want to get the AdventureWorks database for 2008R2 please browse https://msftdbprodsamples.codeplex.com/releases/view/59211 and download AdventureWorks2008R2-Full Database Backup.zip. You need to restore the database to use this. Also, database user tutorialuser is being used here to demonstrate the same. Please remember the user need to have at least DB DDLAdmin role permission to execute the query on system objects. 1. Find the stored procedures which contain a particular text 2. Find the Views which contain a particular text 3. Find the functions which contain a particular text 4. Find the list of tables which is containing a particular text in its column name Find the stored procedures which contain a particular text Okay, let’s move into our first objective to find the stored procedures which contain a particular text. To do so you can execute below query to find out the list of stored procedure name which is containing a text StandardCost. SELECT ROUTINE_NAME, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%StandardCost%' AND ROUTINE_TYPE='PROCEDURE' This will provide you two stored procedure names. uspGetWhereUsedProductID and uspGetBillOfMaterials. But method has a limitation. ROUTINE_DEFINITION contains only first 4000 characters of the stored procedure. So if there is any stored procedure which is big enough [contains > 4000 characters] and the text is being used after 4000 character, you will probably miss it by this method. So what is the correct method to find out all stored procedure names which contain StandardCost. SELECT OBJECT_NAME(OBJECT_ID) [SP_Name], OBJECT_DEFINITION(OBJECT_ID) SP_Definition FROM sys.procedures WHERE Object_definition(object_id) LIKE '%StandardCost%' It will show the result as same like above as there is no stored procedures here which contains more than 4000 characters and contains StandardCost. Find the Views which contain a particular text Now let’s move onto the method to find out Views which contains a particular text, let’s suppose StateProvinceID. -- To find the list of stored procedures which contain a particular text SELECT OBJECT_NAME(OBJECT_ID) View_Name, OBJECT_DEFINITION(OBJECT_ID) View_Definition FROM sys.views WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%StateProvinceID%' It will show below result of having 6 such views Find the functions which contain a particular text Now let’s quickly see the query that will find the list of functions that contain a particular text. Here, I’ll be using the INFORMATION_SCHEMA.ROUTINES to get the function list as I didn’t find any other system object to get the details directly. So the limitation of 4000 characters that is discussed above is applied here. Below is the query that will enlist all the functions that contain a particular text suppose ListPrice . -- To find the list of functions which contain a particular text SELECT ROUTINE_NAME Function_Name, ROUTINE_DEFINITION Function_definition FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%ListPrice%' AND ROUTINE_TYPE ='FUNCTION' Results shows like Find the list of tables which is containing a particular text in its column name Now most important, how to find the list of tables which is containing a particular text in its column name. Let’s support we are trying to find out the tables that contain column name with StandardCost. -- To find list of tables which containing column name with a particular text SELECT t.name AS Table_Name, c.name AS Column_Name FROM sys.tables AS t INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID WHERE c.name LIKE '%StandardCost%' Result shows These queries are sometime become very useful when you are moved into a new project and you still do not know much about the database and you are trying find out from where a column is getting updated or value is getting modified. Assuming that the application is not using tables to update the value."
}

Discussion or Comment

If you have anything in mind to share, please bring it in the discussion forum here.

https://forum.everyething.com/sql-server-f38/