Place: Any Developer Shop
Scenario: A developer wants to drop a column from a table
Time: Any Day – usually right before developer wants to go home
The developer rushes to the manager and following conversation begins:
Developer: I want to drop a column from one of the tables.
Manager: Sure, just document it where all the places it is used in our application and come back to me.
Developer: We only use stored procedures.
Manager: Sure, then documented how many stored procedures are there which are using your column and justify the modification. I will approve it once I see the documentation.
Developer back to the desk looking at hundreds of stored procedures in SSMS thinking how to find which stored procedure may be using his column. Suddenly he remembers a bookmark which he has saved earlier which had T-SQL Script to do so. Here quickly opened it and run the code.
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'Name Your Column Here' + '%'
AND TYPE = 'P'
Above T-SQL Script will search in the stored procedure text and return the name of the stored procedure if it will find the value specified in the WHERE condition. He was happy with his discovery and immediately created the list of the stored procedures and next action items as asked by the manager. He sent the list to the manager right after 10 minutes of his discussion with the manager. He rushed to manager to office to inform his promptness and realized that the manager had left for the day just few moments before.
Moral of the story: Work life balanced can be maintained if we work smart!
Let us see above T-SQL Script in action. Let us assume that in AdventureWorks2012 database we want to find the BusinessEntityID column in all the stored procedure. We can use run following T-SQL code in SSMS Query Editor and find the name of all the stored procedure.
USE AdventureWorks2012
GO
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE '%' + 'BusinessEntityID' + '%'
AND TYPE = 'P'
GO
Above T-SQL script will give results containing the name of the stored procedure and stored procedure text along with it.
While we are discussing this subject here are a couple of other additional related blog post which may interesting.
- Find Stored Procedure Related to Table in Database – Search in All Stored Procedure
- Search Stored Procedure Code – Search Stored Procedure Text
- Find Stored Procedure Create Date and Modified Date
A question to you: Is there any better way to find column used in a stored procedure? Please leave a comment with your solution. I will post the same in this blog with due credit.
Reference: Pinal Dave (
http://blog.sqlauthority.com
)
Hi Pinal,
I always use below method to search column or content which I required in the procedure.
SELECT
Object_Name(Object_Id)
FROM
sys.sql_modules
WHERE
definition LIKE ‘%Your column name%’
AND definition LIKE ‘% Procedure %’
sys.sql_modules:
Returns a row for each object that is an SQL language-defined module. Objects of type P, RF, V, TR, FN, IF, TF, and R have an associated SQL module. Stand-alone defaults, objects of type D, also have an SQL module definition in this view. For a description of these types, see the type column in the sys.objects catalog view.
Reference: http://msdn.microsoft.com/en-us/library/ms175081.aspx
Thanks
Good one!
Could you post something on how do use output from a stored procedure in a Create View script?
Refer this post
http://beyondrelational.com/modules/2/blogs/70/posts/10812/select-columns-from-exec-procedurename-is-this-possible.aspx
Red Gate’s SQL Search is free.
http://www.red-gate.com/products/sql-development/sql-search/
Ahh…. I see some one already posted it.. but anyways…
–================================
SQL Server 2005 and above
–================================
– Below searches all objects (Procedures, views, triggers, functions)
Select distinct object_name(object_id)
from sys.all_sql_modules
Where definition like ‘%search text%’
– If you just want to search stored procedures.
Select distinct object_name(object_id)
from sys.Procedures
Where object_definition(object_id) like ‘%search text%’
–================================
SQL Server 2000 and below
–================================
– Below searches in definition of all objects (Views, triggers, functions)
Select distinct Object_Name(id)
From Syscomments
Where text like ‘%search text%’
~ IM.
Excellent Imran – loved it!
But since “sys.syscomments” is marked for removal in future (http://msdn.microsoft.com/en-us/library/ms186293.aspx), I think we should use “sys.sql_modules” or “information_schema.routines”. Thoughts ?
excellent point. I totally agree!
to search syscomments is a very inefficient way and should be avoided.
I will suggest this script as below:
Declare @TableName sysname;
Declare @SchemaName sysname;
Declare @FullTableName nvarchar(256);
Declare @ColumnName sysname;
Set @SchemaName = ‘dbo’ –change your schema name
Set @TableName = ‘Your_Table’ –change your table name
Set @FullTableName = QuoteName(@SchemaName) + ‘.’ + QUOTENAME(@TableName)
Set @ColumnName = ‘Col_Name’ –change your column name
SELECT QuoteName(A.referencing_schema_name) + ‘.’ + QUOTENAME(A.Referencing_entity_name) As Object_Name
, O.type_desc As Object_Type
, M.definition As Object_Definition
FROM sys.dm_sql_referencing_entities (@FullTableName, ‘Object’) A
Inner Join
sys.objects O
On A.referencing_id = O.object_id
Inner Join
sys.sql_modules M
On A.referencing_id = M.object_id
CroSS Apply
(
Select *
From sys.dm_sql_referenced_entities(QuoteName(A.referencing_schema_name) + ‘.’ + QUOTENAME(A.Referencing_entity_name), ‘Object’) B
Where B.referenced_minor_name = @ColumnName
) C
Another very important “feature” of syscomments is that it contains only 4000 characters per record. If your object contains more than that, it will be broken up in multiple records. There’s a good chance that an word will be seperated between those 2 records and that you will be missing a dependency… Much safer to use sql_modules!
You can also use the INFORMATION_SCHEMA:
DECLARE @column_name SYSNAME;
SELECT ROUTINE_TYPE, ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE ‘%’ + @column_name + ‘%’
UNION
SELECT ‘VIEW’, TABLE_SCHEMA, TABLE_NAME
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE ‘%’ + @column_name + ‘%’
Hi Pinal,
Can you do the same for triggers?
Jerry
Hi Jerry,
yes you can do this. The corresponding type for triggers is TR:
SELECT obj.Name SPName, sc.TEXT SPText
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE ‘%’ + ‘Name Your Column Here’ + ‘%’
AND TYPE = ‘TR’
Pingback: SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name – Part 2 « SQL Server Journey with SQL Authority
what about sp_depends ‘stored_procedurename’
Pingback: Dataset.ReadXML or ‘O Schema, Where Art Thou’
how to add table new column name in insert_stored procedure
Hi pinal i want to list out if the tabled used inside the sp then i want to list out it
so search column like ‘tables’
can we get this in sql?????
EXEC sp_depends ‘procedure name’
How to get the list of all tables present in a particular(single) stored procedure ?
EXEC sp_depends ‘procedure name’