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 (https://blog.sqlauthority.com),

,
Previous Post
SQL SERVER – Versions, CodeNames, Year of Release
Next Post
SQLAuthority News – SQL Server 2005 Powers Global Forensic Data Security Tool

Related Posts

34 Comments. Leave new

  • 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 ?

    Reply
  • How can i search in the textbox the name- Mark Daniel. As it contains space between the name

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

    Reply
  • 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

    Reply
  • Thanks dear if you are not here than what happen with us only god knows that…..???

    Thanks a lot !!!!!!!!!!

    Reply
  • Nelson Thimothiyose
    November 29, 2011 2:51 pm

    Is there any way to find out , the table names used with in a procedure.???

    Reply
  • 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

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

    Reply
  • rakesh nirmal
    April 4, 2012 10:39 am

    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.

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

    Reply
  • thankx

    Reply
  • thnaks you

    Reply
  • 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’

    Reply
  • i want particular text in the sp without returning

    wholwe sp

    Reply

Leave a Reply

Menu