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

, , ,
Previous Post
SQL SERVER – Fix : Error : Msg 3117, Level 16, State 4 The log or differential backup cannot be restored because no files are ready to rollforward
Next Post
SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database

Related Posts

89 Comments. Leave new

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

    Reply
  • Hi

    I Solved my Problem by Ur Post

    Thanks a lot …

    Reply
  • how can I find out datbase name from store procedure which is run in job’s step.

    Thanks,

    Reply
  • can u tell me abt syntax of store procedure {control structure (Looping & if ) }

    Reply
  • Could anyone give pls give any information about how can i use user defined function in sore procedure using sql server 2005??

    Reply
    • You can use function in the procedure like below

      create procedure proc_name
      as
      .
      .
      select dbo.function_name(parameter)

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

    Reply
  • You are a handy-man.
    Thanks a lot for undertaking all hard-work.

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

    Reply
  • how to add validation & condition (time) for store procedure

    Reply
  • Hi,

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

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

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

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

    Reply
  • I HAVE TO PASS ARGUEMENTS FROM ONE SP TO ANOTHER. WHAT SHOULD I DO????

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

    Reply
  • Janet Conklin
    April 20, 2012 6:32 pm

    Thanks for the excellent post!

    Reply
  • manjeet singh
    June 1, 2012 6:30 pm

    How I can Get all Store Procedure in which a table insert its value

    Reply
  • Sitakanta Mishra
    September 6, 2012 6:11 pm

    How to pass a database name as input parameter to a stored procedure in SQL Server 2008?

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

    Reply
  • select object_name(id) from syscomments where text like ‘%searchtext%’

    Reply
  • domace vijesti
    August 12, 2015 2:33 pm

    What’s up, everything is going sound here and ofcourse every
    one is sharing data, that’s genuinely fine, keep up
    writing.

    Reply

Leave a Reply

Menu