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:
the following loops :
for(putchar('c'), putchar('a'), putchar('r'))
putchar('t')
outputs:
Post a Comment