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 (
http://blog.SQLAuthority.com
),
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!
good example but there is a problem !!
if the string i’m looking for is at starting or at the end, this example doesnt return the that value. what will be the solution in that case ?
How can i search in the textbox the name- Mark Daniel. As it contains space between the name
Hi ,
I need SQL statement to search table and column names in a database only knowing String value.
eg; for say in a database i know employee Name ( xxxx ) only,
not the table or column name so how can i find table and column name using ( xxxx) Value .
Please help me i am using SQL server 2008.
[email removed]
Thanks in advance .
Tharak.
Hi all,
i have store procedure for search,which search my tables and give me the result.
now i need a store procedure which will show related search result just like google do….”did you mean etc” etc….how can i do it
Thanks dear if you are not here than what happen with us only god knows that…..???
Thanks a lot !!!!!!!!!!
Is there any way to find out , the table names used with in a procedure.???
Do you have a stored procedure for comparison of fields/columns in the same table? I want to see whether the names of field1, field2 and field 3 are the same, if yes then do something
I wnat to search for a substring in a table / set of tables may be that comtains several million records. Using Var like ‘%string%’ is possibly a solution but considering the number of records and the number of users we have a major performance challenge. Also trying a technique such as var like ‘%string’ Or like ‘String%’ may not help as it would concatenate the two strings and also provide the same result twice and using Distinct it can be filtered but again an expensive solution.
Full Text Index also does not seem to work as it does not provide the substring option.
Is there a direct way that can obtain the results in a better way?
As I can see, even a simple DB like MySQL has a substring search feature in it. So, I am sure I am missing something very big in SQL Server 2005 / 2008 which is an enterprise class DB serevr, Any help would be greatly appreciated. Thanks.
Whenever I face any tricky problem then I remember you and I used to find my problem’s solutions on your site. So thanks a lot from bottom of my heart.
Hi
i need one efficient query to find keyword appearance count from one table
for an example i have 3000 standard values in one table named as table A.and 7 crore records in another table named as B.
Table A data sample is
1.abcd
2.bcde
3.efgh
etc
Table B sample data
ID column B
123 abcd#!#bcde#!#efgh
234 efgh#!#bcde#!#abcd
etc
so i need to find the apcd appearance count from table B.
Hope we can do it with like statement.but thats not efficient with large amount of data.can any one please specify the efficient way to find the appearance count of table A values in table B.
thankx
Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority
Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority
thnaks you