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
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”.
-- 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”.
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.
- 1630 reads