SQL Server has released SQL Server 2000 edition before 7 years and SQL Server 2005 edition before 2 years now. There are still few users who have not upgraded to SQL Server 2005 and they are waiting for SQL Server 2008 in February 2008 to SQL Server 2008 to release. This blog has is heavily visited by users from both the SQL Server products. I have two previous post which demonstrates the code which can be search string in stored procedure. Many users get confused with the script version and try to execute SQL Server 2005 version on SQL Server 2000, they do send me email or leave comment that this does not work. I am going to list both the post here with clearly indicating the SQL Server version. I am sure this will clear some of the doubts.
SQL Server 2000
USE AdventureWorks
GO
--Option 1
SELECT DISTINCT so.name
FROM syscomments sc
INNER JOIN sysobjects so ON sc.id=so.id
WHERE sc.TEXT LIKE '%Employee%'
GO
--Option 2
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE '%Employee%'
GO
SQL Server 2005
USE AdventureWorks
GO
--Searching for Empoloyee table
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
GO
--Searching for Empoloyee table and RateChangeDate column together
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%Employee%'
AND OBJECT_DEFINITION(OBJECT_ID) LIKE '%RateChangeDate%'
GOReference : SQL SERVER - 2005 - Search Stored Procedure Code - Search Stored Procedure Text, SQL SERVER - Find Stored Procedure Related to Table in Database - Search in All Stored procedure
pinaldave
thanks
Thank you very much! This was really helpful and saved me tons of work. Elena
Thanks..worked like a charm!
Thanks, this works very well. What I would have like is to see is sort of the line numbers associated with the objects, and perhaps a replace script as well. Any pointers would be welcome.
Pierre
This is what I was looking for, thanks
Eric
If you need a sp_depends alternative (sp_depends does not always work), here’s a crude script. I am selecting all columns but you can trip down as per your needs. This will basically search for a string in an object which is type P, RF, V, TR, FN, IF, TF, and R:
——-
select objs.name,* from sys.objects objs
INNER JOIN sys.sql_modules mods on
objs.object_id = mods.object_id
where objs.type = ‘P’
and definition like ‘%your_search_string_here%’
——-
Useful to see which object is referenced by which other object.
-Shishir
thank’s!!