SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

I receive following question many times by my team members.

How can I find if particular table is being used in the stored procedure?
How to search in stored procedures?
How can I do dependency check for objects in stored procedure without using sp_depends?

I have previously wrote article about this SQL SERVER – Find Stored Procedure Related to Table in Database – Search in All Stored procedure.

The same feature can be implemented using following script in 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

ResultSet:
Name
———————————–
uspGetEmployeeManagers
uspGetManagerEmployees
uspUpdateEmployeeHireInfo
uspUpdateEmployeeLogin
uspUpdateEmployeePersonalInfo

Name
———————————–
uspUpdateEmployeeHireInfo

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

95 thoughts on “SQL SERVER – 2005 – Search Stored Procedure Code – Search Stored Procedure Text

    • MSSQL Server 2005 keeps track of the dependencies for you: no need to search the object definition for possibly misleading text chunks. This code returns all stored procedures that access the table Site, showing each column that is referenced:
      DECLARE @tabName VARCHAR(60);
      SET @tabName = ‘Site’;
      SELECT DISTINCT
      pr.object_id AS procid,
      pr.name, tab.name, col.name,
      dep.is_selected, dep.is_updated, dep.is_select_all
      FROM sys.procedures pr
      INNER JOIN sys.sql_dependencies dep ON pr.object_id = dep.object_id
      INNER JOIN sys.columns col ON dep.referenced_major_id = col.object_id
      INNER JOIN sys.tables tab ON tab.object_id = col.object_id
      WHERE tab.name = @tabName
      AND dep.class IN (0, 1)
      AND dep.is_updated = 1
      – AND col.name = ‘Source’
      ORDER BY pr.name

  1. Pingback: SQL SERVER - 2005 2000 - Search String in Stored Procedure Journey to SQL Authority with Pinal Dave

  2. Hi Pinal,
    When i executed the below uqery provided by u:–

    GO
    SELECT Name
    FROM sys.procedures
    WHERE OBJECT_DEFINITION(object_id) LIKE ‘%tblseason%’
    GO

    I got belwo error:–

    Server: Msg 195, Level 15, State 10, Line 3
    ‘OBJECT_DEFINITION’ is not a recognized function name.

    Please tell me teh resolution.

    Meanwhiel i am using sp_depends for teh same purpose

    Thanks,
    Bobby

  3. Pingback: SQLAuthority News - Authors Most Visited Article on Blog Journey to SQL Authority with Pinal Dave

  4. Pingback: SQLAuthority News - Authors Most Visited Article on Blog Journey to SQL Authority with Pinal Dave

  5. Here is a query to look for a string in all the database’s procedures:
    ——————————————————-
    USE DataBaseName
    GO

    – create temporary Result table to gather names and text
    – of the procedures in the DataBaseName database :
    CREATE TABLE #Result
    (TextField varchar(max), ProcName varchar(100))

    – create temporary ProcName table with the names of
    – all the procedures in the database [DataBaseName]:
    CREATE TABLE #ProcList
    (ID int IDENTITY, ProcName varchar(100))

    – populate the ProcName table with the procedure names:
    INSERT #ProcList SELECT [name] from sys.procedures

    – get the number of procedures (to be used in the loop below):
    DECLARE @NumberOfProcs int
    SELECT @NumberOfProcs = COUNT(*) FROM sys.procedures

    – loop to populate the Result table:
    DECLARE @i INT
    SET @i = 1
    DECLARE @ProcName varchar(100)
    DECLARE @SQL varchar(2000)
    WHILE @i <= @NumberOfProcs
    BEGIN
    SELECT @ProcName = ProcName FROM #ProcList WHERE ID = @i
    SET @SQL = ‘INSERT INTO #Result (TextField) EXEC sp_helptext ‘ + @ProcName
    EXEC (@SQL)
    UPDATE #Result SET ProcName = @ProcName WHERE ProcName IS NULL
    SET @i = @i + 1
    END

    – look for a string you need [your string] in the Result table
    SELECT * FROM #Result WHERE TextField LIKE ‘%your string%’

    – clean up
    DROP TABLE #Result
    DROP TABLE #ProcList

  6. I found one issue with the above given code.
    suppose say my table name existing as a alias name in any stored procedure, then even that stored procedure is getting displayed as dependent on the stored procedure, which is incorrect.
    E.g.:
    SELECT Name FROM sys.procedures
    WHERE OBJECT_DEFINITION(OBJECT_ID) like ‘%books%’

    then suppose say, I have created a SP as follows,

    create procedure employeekidding
    as
    select empno,managerno*deptno as books from employee

    Issue:
    the above created procedure is getting displayed as if it is dependent on books table, which is not.

  7. This is Siddhesh
    I need a search algorithm which when ran on any server can reveal names of all the stored procedures stored on it .

    Does ant 1 has any idea?

  8. Hi This is manohar,

    I Need Search StoredProcedure.

    i have 2 columns in Table one Column is ‘JobId’ and secoud Column is ‘Location’

    select JobId,JObLocation From Jobs

    i need search Procedure Two Columuns at a time
    if one culumn left onethe column result should disply vise virsa

    and if i am giving half of text to search for any of column it should give the result with that …….

    please give Procedure for it

  9. Pingback: SQLAuthority News - Best Articles on SQLAuthority.com Journey to SQL Authority with Pinal Dave

  10. hi pinel,

    i’m new to this software field. actually i created one database table in db. could u tell me how to retrieve that table in stored procedure.

    • just open your databasetable and there you have to have two step there is Programabilty is there just click and get them

    • just open your databasetable and there you have to take two steps down there is Programabilty is there just click and get them

  11. Hi Mahalaxmi,

    Its very easy to get the details from table through stored procedure

    If the table name is dbo.employee

    SP Creation:

    create procedure dbo.getemployeedetails
    as
    begin

    select * from dbo.employee

    end

    execute this procedure in query window…

    exec dbo.employeedetails

  12. Hi all,

    SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_DEFINITION LIKE ‘%Student%’
    AND ROUTINE_TYPE=’PROCEDURE’

    IF this views only search the SPs less than 4000 character length. If my SP is more than 4000 character means I can’t get the results.

    Why System View INFORMATION_SCHEMA.ROUTINES ROUTINE_DEFINITION Column Size is NVARCHAR(4000)..?

  13. Sir i am need of creating function inside stored procedure can you resolve my doubt since i am new to sql server2005 i’m unable to find solution

  14. Hi Pinal,

    I have the code to search text in the SP. Is there a way to get LineNumbers for the text?. This helps me pinpoint exact places that need to change.

    Regards,
    Rohan

  15. Such a shame this sort of functionality can’t be tied in to the “Edit | Find” dialog in Management Studio.

    ApexSQL Edit does this really well but I’d rather not have to use two editors.

    Sigh.

  16. Colud you please help to reach out…

    I am storing VarBinary data in a column of table.(data may be …Img / Doc/ txt ect). now i want to create a physical file on server disk, using the table data by either stored procedure or trigger… which is executing on server side.

  17. hi sir
    this is chaitanya .is there any statement to find select statement which is written in stored procedure .if any statement is there plz tell the quary.

    thanks

  18. Hello Pinal,
    Data Synchronization In SQL Server database table using sql script or TSQL script or code.

    I want to Synchronize my SQL Server database table at server end using client database table’s records.

    Actually, I have to updated SQL Server database table data/records with the client database.

    The client is sitting in the districts, who is entering the data/records collected from villages and block where there is no Internate connection, so that

    data/records are entered offline at the client database these records must be updated and reflected at the SQL Server database at the end.

    If this can be accomplish by using TSQL ,Stored Procedure.

    Deepak Chandra

  19. Hi. I have scripted all stored procedures into a big script and now I have to replace “dbo.” with my new schema name given by
    (select Table_schema from INFORMATION_SCHEMA.TABLES where TABLE_NAME=@Schemaname)

    In my script some tables don’t have “dbo.”, some have “fn_” in front of them.

    Did anyone did this and have some code related to it?
    I would appreciate your help.
    Denise

    • I have missed to mention my tables from the script that needs to be updated with schema names may contain in front of them any of “UPDATE”, “DELETE”, INTO”, “TRUNCATE”, “dbo.”, “FROM” and also has to ignore while spaces and line breaks.

      Thank you very much in case anyone has any code already done.

  20. Dear
    Sir/Mam

    I am shipra, Jaipur.
    I want to know all the types of stored procedures in SQL Server 2008 of Asp.net C sharp code and their uses.Kindly sort out my Query.

    • @Mike,

      Tell me what do you get when you search for the string “how to decrypt stored procedure in sql 2005″ in Google.com

      ~IM.

  21. I want create complex view of SQL Server for a ERP Project. But I am not habituate with creating view in SQL Server 2005. I need a proper guideline.

  22. Find Text in table-valued function.

    I was able to modify the query to search stored procedures to search table-valued functions if it is of use to anyone. I needed to change column references in a number of places and this was helpful to me in verifying I found all the places I needed to modify.

    SELECT o.Name
    FROM sys.objects o
    WHERE type = ‘IF’ AND OBJECT_DEFINITION(OBJECT_ID) LIKE ‘%Employee%’

    The only difference from the statement that Pinal posted is that the Id for the OBJECT_DEFINITION function is from the sys.objects view instead of the sys.procedures view.

  23. create procupval
    as
    begin
    declare @price int
    select @price = avg(price) from Toys_DataBind
    while( @price < 22)
    begin
    update Toys_DataBind set price = price + 2.50
    break;
    end
    end

    exec upval

  24. following error occur when i execute the procedure…although it parse sucessfully…
    Msg 2714, Level 16, State 3, Procedure sp_GetInventory, Line 9
    There is already an object named ‘sp_GetInventory’ in the database.
    can any one tell me how to execute stored procedure????i am using SQL server 2005 first time..

  25. Hi,
    Please give me the solution for:

    I have Stored Procedure name: USP_Get_Daily_Report_PriorityWise

    ALTER PROC [dbo].[USP_Get_Daily_Report_PriorityWise]
    AS
    Begin
    set nocount on
    DECLARE @Loc_Table TABLE (Counts INT, Internal_Priority VARCHAR(20), Process_Area VARCHAR(1000))

    INSERT INTO @Loc_Table
    SELECT COUNT(*),Internal_Priority, Process_Area FROM AgedReportPivot (NOLOCK)
    WHERE (Internal_Priority IN (’1′,’2′,’3′,’4′,’Other’))
    AND (Status NOT IN (‘CANCELLED’, ‘RESOLVED’, ‘CLOSED’))
    GROUP BY Process_Area,Internal_Priority

    DECLARE @loc_Result Table (Process_Area VARCHAr(1000),a INT,b INT,c INT,d INT,Other VARCHAr(10))
    –DECLARE @loc_Result Table (Process_Area VARCHAr(1000),1 INT,2 INT,3 INT,4 INT,Other VARCHAr(10))

    INSERT INTO @loc_Result
    SELECT Process_Area,[1] as a,[2]As b,[3]AS c,
    [4] AS d,[Other] AS Others
    FROM(SELECT Counts,Internal_Priority,Process_Area
    FROM @Loc_Table
    ) p
    PIVOT
    (SUM(Counts)
    FOR Internal_Priority IN ([1],[2],[3],[4],[Other]))
    AS pvt

    ————————PSI last 30 days data———————————
    declare @PSIData int;
    SELECT @PSIData = COUNT(*) FROM dbo.PSIAgedRequestAll (NOLOCK)
    WHERE (Status NOT IN (‘CANCELLED’)) AND (Reported_Date >= GETDATE() – 30)
    AND (Reported_Date = GETDATE() – 30)
    AND (Reported_Date = GETDATE() – 30)
    AND (Reported_Date <= GETDATE())

    set nocount off
    END

    There are 8 columns:
    LastDays is Last 30 days Requests inflow (Open, Closed, Resolved etc).
    Priority1,Priority2, Priority3, Priority4,PriorityOthers, are all open request count.
    Total=Priority1+Priority2+Priority3+Priority4+PriorityOthers

    Result:
    Priority1 Priority2 Priority3 Priority4 PriorityOthers Process_Area LASTDAYS Total
    NULL NULL 1 NULL 7 Customer Masterdata 13 61.54
    NULL 1 6 NULL 15 P2P 48 45.83
    NULL NULL 3 NULL 1 CRM-Complaints 9 44.44
    NULL 2 3 NULL 6 Material MasterData 25 44
    NULL NULL 4 NULL NULL CRM Field Service 10 40
    NULL 2 11 NULL 1 TheEdge 36 38.89
    NULL 10 31 NULL 57 Finance 305 32.13
    NULL 1 24 NULL NULL Non-ERP 92 27.17
    NULL NULL 4 NULL 4 CRM Internet Sales 36 22.22
    NULL 1 9 NULL 10 MFG 92 21.74
    NULL 4 10 NULL NULL GEMS 65 21.54
    NULL 10 33 NULL 132 PSI 395 19.25
    NULL 3 3 1 20 EHS 163 16.56
    NULL 3 9 NULL 42 OTC 364 14.84
    NULL 4 11 NULL 1 APO 122 13.11
    NULL 2 3 NULL 14 BI 281 6.76
    NULL 1 3 NULL 6 ABAP 152 6.58
    NULL 9 5 NULL 2 Basis 362 4.42

    My Requirement in the same table:

    Now I want to fibercate data according to Priority wise and in the same need to show out of last 30 days request count how many are Priority 1, Priority 2 etc
    Ex:
    If Priority 3 request count for Process_Area P2P are 6. Out of last 30 days 48 request count for P2P,how many are Priority 3?

    Priority 3
    10 6

    Here 10 are Inflow Priority 3 out of 48 requests and 6 is Open for Priority 3.

  26. i m giving some CreateXmlFile
    procedure using visual studio

    ————-procedure WriteDataToXml—————————–

    Create proc Writedatatoxml @tbname nvarchar(20),@path nvarchar(100)
    as
    external name myxmlconvertionassembly.[createxml.converttoxml].writexml

  27. Hi,

    How can i get the particular character location/position from table column.

    ex. select * from TABLENAME Functionname(table_column,’;’)

  28. hello sir … I am using 3 updated query inside the stored procedure… Like..

    declare @qry nvarchar(500)
    begin transaction
    update table1 set status=’A’ where appno=@appno

    update table2 set entrydate=@entrydate where appno=@appno

    set @qry=’update ‘ + tablename+ ‘ set status=@status where appno=@appno
    exec sp_executesql @qry

    commit transaction

    when I am calling this SP from my .net code .. the queries inside transaction are not updating the table and SP give no error … what should I do?? how to troubleshoot this problem…… thanks in advance

  29. Hi,
    I have written stored procedure with the where clause(e.g description=’End of process’). Can i find stored procedures with the above description.

    Thanks
    Poongodi

  30. hello sir

    I want to search a particular string from text and retrieve the sub-string which is separated by ‘.’ and that sentence contains searching string

  31. Pingback: SQL SERVER – Find Column Used in Stored Procedure – Search Stored Procedure for Column Name « SQL Server Journey with SQL Authority

  32. CREATE PROCEDURE [dbo].[SP_BUSCA_CONTENIDO_ALL_SPS] @BUSCAR NVARCHAR(800)
    AS
    BEGIN
    DECLARE @COMANDO AS NVARCHAR(800)
    – create temporary Result table to gather names and text
    – of the procedures in the DataBaseName database :
    IF OBJECT_ID(‘tempdb..#Result’) IS NOT NULL drop table #Result
    IF OBJECT_ID(‘tempdb..#ProcList’) IS NOT NULL drop table #ProcList
    CREATE TABLE #Result
    (TextField varchar(max), ProcName varchar(100))

    – create temporary ProcName table with the names of
    – all the procedures in the database [DataBaseName]:
    CREATE TABLE #ProcList
    (ID int IDENTITY, ProcName varchar(100))

    – populate the ProcName table with the procedure names:
    INSERT #ProcList SELECT [name] from sys.procedures

    – get the number of procedures (to be used in the loop below):
    DECLARE @NumberOfProcs int
    SELECT @NumberOfProcs = COUNT(*) FROM sys.procedures

    – loop to populate the Result table:
    DECLARE @i INT
    SET @i = 1
    DECLARE @ProcName varchar(100)
    DECLARE @SQL varchar(2000)
    WHILE @i <= @NumberOfProcs
    BEGIN
    SELECT @ProcName = ProcName FROM #ProcList WHERE ID = @i
    SET @SQL = 'INSERT INTO #Result (TextField) EXEC sp_helptext ' + @ProcName
    EXEC (@SQL)
    UPDATE #Result SET ProcName = @ProcName WHERE ProcName IS NULL
    SET @i = @i + 1
    END

    – Visualiza el resultado de la busqueda en una tabla
    SET @COMANDO = 'SELECT * FROM #Result WHERE TextField LIKE '+CHAR(39)+'%'+@BUSCAR+'%'+CHAR(39)
    EXEC SP_EXECUTESQL @COMANDO

    – clean up
    DROP TABLE #Result
    DROP TABLE #ProcList

    END

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

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

  35. Pingback: SQL SERVER – Weekly Series – Memory Lane – #045 | Journey to SQL Authority with Pinal Dave

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