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 (https://blog.sqlauthority.com)
28 Comments. Leave new
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:
Thanks
Could you post something on how do use output from a stored procedure in a Create View script?
Refer this post
Red Gate’s SQL Search is free.
https://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.
The below 2 queries are not working in SQL 2012
– 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%’
But since “sys.syscomments” is marked for removal in future , I think we should use “sys.sql_modules” or “information_schema.routines”. Thoughts ?
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’
what about sp_depends ‘stored_procedurename’
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’
Hello, everybody, I am looking for those procedures, triggers or any other objects in the database that use the operator *= into their statments
I am trying with these examples above, as a result only retrieves 4 coincidences, when in fact the script must to return more that 4 coincidences. I am running this script under SQL 2012. Do I need other kind of script to retrieve those coincidences?
Thanks in advance!
Hello All. I have been looking on the web and this posting was “sort of” along the lines of what I was looking for. We are still on SQL 2000. Fingers crossed we will be on 2012 shortly. Of course we now have a request to “secure” a certain field. The field is a key field used in many stored procedures, some of which produce reports. It has been requested that only certain people be able to see the data in that field. Is there a way to setup something that would only allow a certain role or privilege to see the data in that column even if its in a stored procedure?
Any thoughts would be appreciated.
Hi Sir,
Thanks a lot but it is not quering the distinct records.
SELECT DISTINCT OBJECT_NAME(OBJECT_ID),
object_definition(OBJECT_ID)
FROM sys.Procedures
WHERE object_definition(OBJECT_ID) LIKE ‘%’ + ‘wip’ + ‘%’ AND TYPE = ‘P’
order by OBJECT_NAME(OBJECT_ID)
SELECT OBJECT_NAME(M.object_id), M.*
FROM sys.sql_modules M
JOIN sys.procedures P
ON M.object_id = P.object_id
WHERE M.definition LIKE ‘%wip%’
SELECT DISTINCT obj.Name SPName
FROM sys.syscomments sc
INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE ‘%’ + ‘wip’ + ‘%’
AND TYPE = ‘P’ order by SPName
@Narendran – Can you please explain what is not working?
This doesn’t work when the Stored Proc is in the Different Database and also table is in the different database.
Can you tell me how to search all of the databases on a server for stored procedures containing a string? Unfortunately, we have columns that are passed around from db to db.
I have this:
DECLARE @command varchar(2048);
SELECT @command = ‘USE ?
SELECT obj.Name SPName
, sc.TEXT SPText
FROM sys.syscomments sc
INNER
JOIN sys.objects AS obj
ON sc.Id = obj.OBJECT_ID
WHERE sc.TEXT LIKE ‘ + char (39) + ‘%employee%id%’ + char(39) + ‘
AND TYPE = ”P”
‘
EXEC sp_MSforeachdb @command
But it doesn’t tell me which databases the stored procedures are in.
–Stephen
Hello Dave,
This was a very interesting post and although it is rather old at the time I write this comment, it opened possibilities for me that I didn’t even knew existed.
Thank you very much for that and thank you everybody who left a comment to help me solve an issue I had.
Cheers,
Corné