Sql Server search stored procedures

It contains sql query script to get the list of stored procedures in sql server by searching or filtering according to:

1. Name of stored procedures, name of schema, created date, modified date etc either in current or in all databases.

2. Text in the definition of stored procedure.  

Search query in current database

Get list of all stored procedures name of current database

SELECT

ROUTINE_NAME AS ProcName,

ROUTINE_SCHEMA AS SchemaName

FROM INFORMATION_SCHEMA.ROUTINES

Get name of all stored procedures of current database where procedure name contains specific text

SELECT

ROUTINE_NAME AS ProcName,

ROUTINE_SCHEMA AS SchemaName

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME LIKE '%text%'

Get all procedures name of a database where of procedure name has prefix

SELECT

ROUTINE_NAME AS ProcName,

ROUTINE_SCHEMA AS SchemaName

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_NAME LIKE 'usp%'

Get all procedures name of current database which has created today

SELECT

ROUTINE_NAME AS ProcName,

ROUTINE_SCHEMA AS SchemaName

FROM INFORMATION_SCHEMA.ROUTINES

WHERE CAST(CREATED AS DATE) = CAST(GETDATE() AS DATE)

Get list of all stored procedures name of current database which has modifed today

SELECT

ROUTINE_NAME AS ProcName,

ROUTINE_SCHEMA AS SchemaName

FROM INFORMATION_SCHEMA.ROUTINES

WHERE CAST(LAST_ALTERED AS DATE) = CAST(GETDATE() AS DATE)

Get the name of all stored procedures of given database of undwer specific schame

SELECT

ROUTINE_NAME AS ProcName,

ROUTINE_SCHEMA AS SchemaName

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_SCHEMA = 'dbo'

List all procedures which defintion contains give text

DECLARE @SearchText AS VARCHAR(100)

SET @SearchText = 'ntLogID'

SELECT *

FROM INFORMATION_SCHEMA.ROUTINES

WHERE ROUTINE_DEFINITION LIKE '%' + @SearchText + '%'

Search query in all databases

Get all procedures name of all database

--Deleting #tblProcList table if already persent

IF OBJECT_ID('#tblProcList') IS NOT NULL

     DROP TABLE #tblProcList

CREATE TABLE #tblProcList(

     DbName VARCHAR(200),

     SchemaName VARCHAR(200),

     ProcName VARCHAR(200)

)

INSERT #tblProcList

EXECUTE sys.sp_MSforeachdb 'SELECT

''?'',

ROUTINE_SCHEMA,

ROUTINE_NAME

     FROM [?].INFORMATION_SCHEMA.ROUTINES'

SELECT * FROM #tblProcList

Get all procedures name of all database where procedure name contains

-- Change the search text

DECLARE @SearchText AS VARCHAR(200)= 'text'

DECLARE @vcSearchQuery VARCHAR(MAX)

--Deleting #tblProcList table if already persent

IF OBJECT_ID('#tblProcList') IS NOT NULL

     DROP TABLE #tblProcList

CREATE TABLE #tblProcList(

     DbName VARCHAR(200),

     SchemaName VARCHAR(200),

     ProcName VARCHAR(200)

)

SET @vcSearchQuery = 'SELECT

''?'',

ROUTINE_SCHEMA,

ROUTINE_NAME

     FROM [?].INFORMATION_SCHEMA.ROUTINES

     WHERE ROUTINE_NAME LIKE ''%' + @SearchText + '%'''

INSERT #tblProcList

EXECUTE sys.sp_MSforeachdb @vcSearchQuery

SELECT * FROM #tblProcList

Get procedures name of all databases which has created today

--Deleting #tblProcList table if already persent

IF OBJECT_ID('#tblProcList') IS NOT NULL

     DROP TABLE #tblProcList

CREATE TABLE #tblProcList(

     DbName VARCHAR(200),

     SchemaName VARCHAR(200),

     ProcName VARCHAR(200)

)

INSERT #tblProcList

EXECUTE sys.sp_MSforeachdb 'SELECT

''?'',

ROUTINE_SCHEMA,

ROUTINE_NAME

     FROM [?].INFORMATION_SCHEMA.ROUTINES

     WHERE CAST(CREATED AS DATE) = CAST(GETDATE() AS DATE)'

SELECT * FROM #tblProcList

Get procedures name of all databases which has modified today

--Deleting #tblProcList table if already persent

IF OBJECT_ID('#tblProcList') IS NOT NULL

     DROP TABLE #tblProcList

CREATE TABLE #tblProcList(

     DbName VARCHAR(200),

     SchemaName VARCHAR(200),

     ProcName VARCHAR(200)

)

INSERT #tblProcList

EXECUTE sys.sp_MSforeachdb 'SELECT

''?'',

ROUTINE_SCHEMA,

ROUTINE_NAME

     FROM [?].INFORMATION_SCHEMA.ROUTINES

     WHERE CAST(LAST_ALTERED AS DATE) = CAST(GETDATE() AS DATE)'

SELECT * FROM #tblProcList

Get all procedures name of all database where procedure definition contains given text

-- Change the search text

DECLARE @SearchText AS VARCHAR(200)= 'text'

DECLARE @vcSearchQuery VARCHAR(MAX)

--Deleting #tblProcList table if already persent

IF OBJECT_ID('#tblProcList') IS NOT NULL

     DROP TABLE #tblProcList

--Creating table #tblProcList

CREATE TABLE #tblProcList(

     DbName VARCHAR(200),

     SchemaName VARCHAR(200),

     ProcName VARCHAR(200)

)

--Preparing search query

SET @vcSearchQuery = 'SELECT

''?'',

ROUTINE_SCHEMA,

ROUTINE_NAME

     FROM [?].INFORMATION_SCHEMA.ROUTINES

     WHERE ROUTINE_DEFINITION LIKE ''%' + @SearchText + '%'''

--Executing search query

INSERT #tblProcList

EXECUTE sys.sp_MSforeachdb @vcSearchQuery

--Displaying the search result

SELECT * FROM #tblProcList