Feb 7, 2013

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

1 comment:

  1. the following loops :
    for(putchar('c'), putchar('a'), putchar('r'))
    putchar('t')
    outputs:

    ReplyDelete