none
How to get list of SP by search text along with DB RRS feed

  • Question

  • Hi,

      I have multiple databases in my sql server and I need to get the list of SP/Functions along with db name for the given text search (containing text). Please help me with the sql query to get the same.

    Thanks.

    Monday, June 17, 2019 4:08 PM

All replies

  • Please run a select against sys.objects table

    Select * from sysobjects Where type =''

    Where the value of Type depends on following look up

    AF = Aggregate function (CLR)

    C = CHECK constraint

    D = DEFAULT (constraint or stand-alone)

    F = FOREIGN KEY constraint

    FN = SQL scalar function

    FS = Assembly (CLR) scalar-function

    FT = Assembly (CLR) table-valued function

    IF = SQL inline table-valued function

    IT = Internal table

    P = SQL Stored Procedure

    PC = Assembly (CLR) stored-procedure

    PG = Plan guide

    PK = PRIMARY KEY constraint

    R = Rule (old-style, stand-alone)

    RF = Replication-filter-procedure

    S = System base table

    SN = Synonym

    SO = Sequence object

    U = Table (user-defined)

    V = View

    EC = Edge constraint 



    Applies to: SQL Server 2012 (11.x) through SQL Server 2017.



    SQ = Service queue

    TA = Assembly (CLR) DML trigger

    TF = SQL table-valued-function

    TR = SQL DML trigger

    TT = Table type

    UQ = UNIQUE constraint

    X = Extended stored procedure



    Applies to: SQL Server 2016 (13.x) through SQL Server 2017, Azure SQL Database, Azure SQL Data Warehouse, Parallel Data Warehouse.



    ET = External Table

    ____________________________________________________________
    Please remember to mark the replies as answers if they help.
    Monday, June 17, 2019 4:19 PM
  • To see the script, you can use Sp_helptext per Stored Procedure/Trigger etc
    Monday, June 17, 2019 4:21 PM
  • Loop your database list and run the following query against each database:

    SELECT [ROUTINE_CATALOG] AS [DatabaseName]
          ,[ROUTINE_SCHEMA] AS [Schema]
          ,[ROUTINE_NAME]
          ,[ROUTINE_TYPE]
          ,[ROUTINE_DEFINITION]
          ,[CREATED]
          ,[LAST_ALTERED]
      FROM [INFORMATION_SCHEMA].[ROUTINES]
    WHERE [ROUTINE_DEFINITION] LIKE '%SEARCH_KEYWORD%'


    A Fan of SSIS, SSRS and SSAS

    Monday, June 17, 2019 4:31 PM
  • Hi Rangasamy,

    You can try the following:

    DROP TABLE IF EXISTS #ListOfSPs; CREATE TABLE #ListOfSPs ( DBName VARCHAR(100), Routine_Schema VARCHAR(100), Routine_Name VARCHAR(100),

    ROUTINE_DEFINITION NVARCHAR(4000) ); EXEC sp_msforeachdb 'USE [?]; INSERT INTO #ListOfSPs SELECT ''?'', Routine_SCHEMA, Routine_Name, ROUTINE_DEFINITION FROM INFORMATION_SCHEMA.ROUTINES where routine_definition like ''%SEARCH_KEYWORD%'';'; SELECT * FROM #ListOfSPs;


    Monday, June 17, 2019 5:06 PM
  • Hi.. Thanks for your response. I'm getting the following exception,

    Msg 2812, Level 16, State 62, Line 11
    Could not find stored procedure 'sp_msforeachdb'.

    Monday, June 17, 2019 7:30 PM
  • Hi Rangasamy,

    Its fully qualified path master.sys.sp_msforeachdb


    Monday, June 17, 2019 7:35 PM
  • Just checking to see if the response was helpful, if not please comment on the thread so that we can assist further.

    ____________________________________________________________
    Please remember to mark the replies as answers if they help.
    Tuesday, June 18, 2019 1:14 AM
  • I'm still getting the same error,

    Msg 2812, Level 16, State 62, Line 11
    Could not find stored procedure 'sp_msforeachdb'.

    Any idea?

    Tuesday, June 18, 2019 3:44 PM
  • Hi Rangasamy,

    Please share a screen shot of the SSMS with the SQL and result pane.

    Tuesday, June 18, 2019 4:59 PM
  • 

    Hi Yitzhak,

     Please find screenshot below,

    Tuesday, June 18, 2019 7:04 PM
  • Hi Rangasamy,

    1. Sometimes DB has a case sensitive collation.
      Please try the following verbatim: EXEC master.sys.sp_MSforeachdb ...
    2. Additionally, you can check its existence by running this query:


    SELECT * FROM sys.all_objects 
    WHERE [name] LIKE 'sp_MSforeach%';

    Output:

    name	object_id	principal_id	schema_id	parent_object_id	type	type_desc	create_date	modify_date	is_ms_shipped	is_published	is_schema_published
    sp_MSforeachdb	-835749359	NULL	4	0	P 	SQL_STORED_PROCEDURE	2012-02-10 20:58:23.487	2012-02-10 20:58:34.937	1	0	0
    sp_MSforeachtable	-776307597	NULL	4	0	P 	SQL_STORED_PROCEDURE	2012-02-10 20:58:23.977	2012-02-10 20:58:35.047	1	0	0
    sp_MSforeach_worker	-391278113	NULL	4	0	P 	SQL_STORED_PROCEDURE	2012-02-10 20:58:22.860	2012-02-10 20:58:34.827	1	0	0



    Tuesday, June 18, 2019 8:14 PM