none
locating stored procedures that start with ‘sp_’

Answers

  • SELECT *
    FROM DBName.INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME LIKE 'sp_%'


    Narsimha

    • Marked as answer by soldierfc Thursday, May 01, 2014 9:56 PM
    Thursday, May 01, 2014 9:47 PM
  • select * from sys.procedures p where p.name LIKE 'sp[_]%' AND TYPE = 'P'


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by soldierfc Thursday, May 01, 2014 9:56 PM
    Thursday, May 01, 2014 9:47 PM
  • SELECT S.name 'Schema Name',
           O.name AS 'Object Name'
    FROM SYS.sql_modules M
    INNER JOIN SYS.objects O 
    ON M.object_id = O.object_id
    INNER JOIN sys.schemas S
    ON O.schema_id = S.schema_id
    INNER JOIN SYS.syscomments SCMT
    ON SCMT.id= O.object_id
    WHERE  O.type_desc IN ('SQL_STORED_PROCEDURE') 
           AND O.name LIKE 'sp%'
    ORDER BY [Object Name]
    • Marked as answer by soldierfc Thursday, May 01, 2014 9:57 PM
    Thursday, May 01, 2014 9:56 PM

All replies

  • SELECT *
    FROM DBName.INFORMATION_SCHEMA.ROUTINES
    WHERE ROUTINE_NAME LIKE 'sp_%'


    Narsimha

    • Marked as answer by soldierfc Thursday, May 01, 2014 9:56 PM
    Thursday, May 01, 2014 9:47 PM
  • select * from sys.procedures p where p.name LIKE 'sp[_]%' AND TYPE = 'P'


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by soldierfc Thursday, May 01, 2014 9:56 PM
    Thursday, May 01, 2014 9:47 PM
  • SELECT S.name 'Schema Name',
           O.name AS 'Object Name'
    FROM SYS.sql_modules M
    INNER JOIN SYS.objects O 
    ON M.object_id = O.object_id
    INNER JOIN sys.schemas S
    ON O.schema_id = S.schema_id
    INNER JOIN SYS.syscomments SCMT
    ON SCMT.id= O.object_id
    WHERE  O.type_desc IN ('SQL_STORED_PROCEDURE') 
           AND O.name LIKE 'sp%'
    ORDER BY [Object Name]
    • Marked as answer by soldierfc Thursday, May 01, 2014 9:57 PM
    Thursday, May 01, 2014 9:56 PM
  • How do I query and find stored procedure that start with 'sp_'?

    SELECT name
    FROM   sys.objects
    WHERE  type IN ('P', 'PC')
      AND  name LIKE 'sp[_]%'
    ORDER  BY name


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, May 01, 2014 10:11 PM