SQL SERVER – 2005 2000 – Search String in Stored Procedure

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),

35 thoughts on “SQL SERVER – 2005 2000 – Search String in Stored Procedure

  1. 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

    Like

  2. 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

    Like

  3. 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%’

    Like

  4. 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…….

    Like

  5. 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

    Like

  6. 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

    Like

  7. 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.

    Like

  8. 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.

    Like

  9. @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.

    Like

  10. 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’

    Like

  11. 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

    Like

  12. 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 ?

    Like

  13. 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.

    Like

  14. 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

    Like

  15. 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

    Like

  16. 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.

    Like

  17. 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.

    Like

  18. 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.

    Like

  19. Pingback: SQL SERVER – Beginning New Weekly Series – Memory Lane – #002 « SQL Server Journey with SQL Authority

  20. Pingback: SQL SERVER – Weekly Series – Memory Lane – #007 « SQL Server Journey with SQL Authority

  21. To get All the stored Procedures with including whole SP Text with single query:
    1.To use in backup etc.

    SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_TYPE=’PROCEDURE’

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s