Post reply

Find all stored procedures that use a particular db.

  • May 9, 2018 at 11:10 am

    #403326

    Is there a way to list all of the stored procedures that use a particular database?

  • May 9, 2018 at 11:24 am

    #1989907

    It might not be an exact science (depending on what your database is called), but how about:

    SELECT s.[name] AS SchemaName,
       p.[name] AS ProcedureName
    FROM sys.procedures p
      JOIN sys.schemas s ON p.schema_id = s.schema_id 
      JOIN sys.sql_modules sm ON p.object_id = sm.object_id
    WHERE sm.definition LIKE '%YourDatabaseName%';

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 9, 2018 at 11:29 am

    #1989908

    Works fine. Thanx

  • May 9, 2018 at 11:32 am

    #1989910

    You might also, however, want to consider Redgate's SQL Search: https://www.red-gate.com/dynamic/products/sql-development/sql-search/download

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • May 9, 2018 at 11:39 am

    #1989913

    K. Thanx.

  • May 9, 2018 at 12:21 pm

    #1989928

    Here is some SQL I stole that will search across all databases on the server.

    DECLARE @SQL VARCHAR(8000);
    DECLARE @SearchText NVARCHAR(255);

    SET @SearchText = 'DwEsIbnrAdtvFact';

    DECLARE @Results TABLE
    (
        [SERVERNAME] VARCHAR(255),
        [DBName] VARCHAR(255),
        [NAME] VARCHAR(255),
        XTYPE VARCHAR(255)
    )
    ;

    SELECT @SQL =
        'SELECT DISTINCT @@SERVERNAME, ''?'' AS [DBName], so.[name], so.xtype
        FROM [?].dbo.sysobjects so WITH(NOLOCK)
            inner join [?].dbo.syscomments sc on
                so.id = sc.id
            INNER JOIN [?].sys.databases d ON
                d.Name=''?''
                AND d.name NOT IN ( ''tempdb'',''master'',''msdb'',''ReportServer'')
        WHERE sc.text like ''%'+ @SearchText + '%'''
    INSERT INTO @Results
    EXEC sp_MSforeachdb @SQL

    SELECT *
    FROM @Results
    ORDER BY DBName, XType, [Name]
    ;

    -------------------------------------------------------------
    we travel not to escape life but for life not to escape us
    Don't fear failure, fear regret.

  • May 9, 2018 at 2:25 pm

    #1989954

    One more place to check is in the base_object_name of sys.synonyms.  That might hide the fact that a query is really using a different database.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply