List Stored Procedure In Sql Server
A database object known as a "stored procedure" houses pre-written queries (a group of T-SQL Statements).
In other words, a stored procedure is a section of code that is created to run whenever it is invoked.
You can create a procedure with or without parameters.
list of all Stored Procedure Modified in last N Days
SELECT name
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 10
-- Change 10 to any other day value
list of all Stored Procedure
SELECT
ROUTINE_SCHEMA, ROUTINE_NAME, CREATED, LAST_ALTERED
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
ORDER BY LAST_ALTERED;
OR
SELECT
NAME, CREATE_DATE, MODIFY_DATE
FROM SYS.OBJECTS
WHERE type = 'P'
ORDER BY MODIFY_DATE;
OR
SELECT
SCHEMA_NAME(schema_id) AS [Schema],
NAME,CREATE_DATE, MODIFY_DATE
FROM SYS.PROCEDURES
ORDER BY MODIFY_DATE;
list of all Stored Procedure Modified in last N Days
SELECT name, SCHEMA_NAME(schema_id) AS [Schema]
FROM sys.objects
WHERE type = 'P'
AND DATEDIFF(D,modify_date, GETDATE()) < 10
-- Change 10 to any other day value
Return The Procedure’s Definition
The INFORMATION_SCHEMA.ROUTINES view also has a ROUTINE_DEFINITION column, so you can easily return each stored procedure’s definition if required.
SELECT ROUTINE_DEFINITION
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';
OR
SELECT definition
FROM sys.objects o
INNER JOIN sys.sql_modules m
ON o.object_id = m.object_id
WHERE type = 'P';
List all stored procedures in SQL Server
SELECT * FROM sys.procedures;
--OR
SELECT ROUTINE_SCHEMA, ROUTINE_NAME
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE';
--OR
SELECT *
FROM AdventureWorks.INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE'
