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 posts which demonstrate the code which can be searched 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%'
GO
Related Articles:
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
Reference: Pinal Dave (https://blog.sqlauthority.com),
34 Comments. Leave new
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!!
Thanks, just what I wanted.
In case you have tables which start with the same name:
SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%Employee%’
AND NOT OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%EmployeeDetails%’
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%’
In this case i have your_search_string_here = %
I need those strings which contain the % as string….
example it should give me all the strings like
‘abcd % abcd’,ad%sd’ etc…….
thx!
in my company every developer will write stored procedures and i need to find stored procedure by whome it is written. is any quary to find the stored procedure.
thanks
chaitanya
Hello Chaitanya,
The login information of stored any object owner is stored in principle_id columns in sys.objects table. If every developer would login with their own credentials then it would be enough to know the owner but in most cases all developers login with a common shared account. In that case information in principle_id column would not be much useful and you should ask developers to write owner name in comments on the top of sp or other object.
Regards,
Pinal Dave
I wish that people would use version control for SQL stuff also. That way it is always known who has written the script and who has modified it. Here’s how I’ve done it in one of my projects.
You make one centralized database that is used in testing. Then you generate CREATE TABLE scripts for that database and put them into version control. No procedures or functions are done in this step, just the database, tables, views, indexes and constraints.
Now if you have small team working with the project you can create development databases for every team member. If you have many developers in the project, build a team to handle DB stuff and create dev DBs for them.
Every new procedure or function or table or whatever is first implemented to dev DB. When developer wants to get it in testing (s)he commits it to the version control. After that it is run from the version control to the test DB and if possible, with automated scripts to rule out human errors.
All changes goes the same way. First to dev DB, then version control, finally test DB.
Finally production database is build directly from version control.
Currently I’m in a project with some thirty other people where nothing was originally put to version control and DB stuff still isn’t. It’s a nightmare! I don’t know who is responsible of what and from whom I might ask things if something goes wrong.
How to construct a search store procedure using like search?
Hi Pinal,
I need stored procedure To find all stored procedure used by table in database
and want to display the names of stored procedure along with table name.
@Gunvant
Why don’t you use Google.com, type display all tables in Sql Server 2005.
Use SQLAuthority Search to find out results to your search.
~IM.
Thanks it works like a charm!
This query is worked perfectly with all words but it is FAIL when I give word ‘ER’ it show name of all stored Procedures containing word like computER,Mother,researcher. Now how could find SP containing word ‘ER’?????
SELECT DISTINCT o.name ,o.xtype
FROM syscomments c
INNER JOIN sysobjects o ON c.id=o.id
WHERE c.TEXT LIKE ‘ER%’ and o.xtype=’P’
Another best ways:
First:
select object_name(object_id),definition from sys.sql_modules where definition like ‘%test%’
Second (Recommended) :
select ROUTINE_NAME, ROUTINE_DEFINITION from
INFORMATION_SCHEMA.ROUTINES where ROUTINE_DEFINITION like ‘%test%’
Reference from : Logiclabz
Thanks Pinal Dave, saved me a lot of time and effort. Much appreciated, and keep up the excellent posts!