Get Stored Procedure List in SQL Server by Modified Date

Recently, I was tasked with retrieving the Stored Procedure list in SQL Server by Modified Date. In this comprehensive article, I’ll share the exact methods I use to retrieve stored procedures by their modified dates – techniques that have been proven with multiple examples.

Get Stored Procedure List in SQL Server by Modified Date

Monitoring stored procedure changes is essential for:

• Change Management: Tracking deployment history and rollback requirements
• Compliance Auditing: Meeting SOX, HIPAA, and other regulatory requirements
• Performance Troubleshooting: Identifying recent changes that may impact performance
• Security Reviews: Monitoring unauthorized modifications to critical procedures
• Team Collaboration: Understanding who changed what and when

Understanding SQL Server System Catalogs

Before diving into the methods, it’s important to understand how SQL Server stores metadata about stored procedures. SQL Server maintains this information in several system catalog views that I regularly use in enterprise environments:

Key System Views for Stored Procedure Metadata

Method 1: Using sys.procedures (Recommended Approach)

This is my go-to method. The sys.procedures view provides direct access to stored procedure metadata with excellent performance characteristics.

Basic Query for Modified Procedures

-- Get stored procedures modified on a specific date
DECLARE @TargetDate DATE = '2025-07-15'

SELECT 
    name AS ProcedureName,
    create_date AS CreatedDate,
    modify_date AS ModifiedDate,
    SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.procedures
WHERE CAST(modify_date AS DATE) = @TargetDate
ORDER BY modify_date DESC

After executing the above query, I got the expected output as shown in the screenshot below.

Advanced Query with Date Range

DECLARE @StartDate DATETIME = '2025-07-15 00:00:00'
DECLARE @EndDate DATETIME = '2025-08-15 23:59:59'

SELECT 
    name AS ProcedureName,
    SCHEMA_NAME(schema_id) AS SchemaName,
    create_date AS CreatedDate,
    modify_date AS ModifiedDate,
    CASE 
        WHEN create_date = modify_date THEN 'Created'
        ELSE 'Modified'
    END AS ChangeType
FROM sys.procedures
WHERE modify_date BETWEEN @StartDate AND @EndDate
ORDER BY modify_date DESC, name ASC

After executing the above query, I got the expected output as shown in the screenshot below.

Method 2: Using sys.objects with Filtering

When working with mixed environments that include functions and other programmable objects, I use sys.objects for broader coverage.

Comprehensive Object Query

-- Get all programmable objects modified in the last 30 days
DECLARE @DaysBack INT = 30

SELECT 
    o.name AS ObjectName,
    SCHEMA_NAME(o.schema_id) AS SchemaName,
    o.type_desc AS ObjectType,
    o.create_date AS CreatedDate,
    o.modify_date AS ModifiedDate,
    DATEDIFF(DAY, o.modify_date, GETDATE()) AS DaysAgo
FROM sys.objects o
WHERE 
    o.type IN ('P', 'FN', 'IF', 'TF', 'TR') -- Procedures, Functions, Triggers
    AND o.modify_date >= DATEADD(DAY, -@DaysBack, GETDATE())
    AND o.is_ms_shipped = 0 -- Exclude system objects
ORDER BY o.modify_date DESC

After executing the above query, I got the expected output as shown in the screenshot below.

Object Type Reference Table

Method 3: Advanced Query with Procedure Definitions

Complete Query

-- Comprehensive stored procedure audit query
DECLARE @ModifiedSince DATETIME = DATEADD(DAY, -35, GETDATE())

SELECT 
    p.name AS ProcedureName,
    SCHEMA_NAME(p.schema_id) AS SchemaName,
    p.create_date AS CreatedDate,
    p.modify_date AS ModifiedDate,
    LEN(m.definition) AS DefinitionLength,
    CASE 
        WHEN p.create_date >= @ModifiedSince THEN 'NEW'
        WHEN p.modify_date >= @ModifiedSince THEN 'MODIFIED'
        ELSE 'EXISTING'
    END AS ChangeStatus,
    -- First 100 characters of definition for quick reference
    LEFT(REPLACE(REPLACE(m.definition, CHAR(13), ' '), CHAR(10), ' '), 100) AS DefinitionPreview
FROM sys.procedures p
    INNER JOIN sys.sql_modules m ON p.object_id = m.object_id
WHERE p.modify_date >= @ModifiedSince
ORDER BY p.modify_date DESC

After executing the above query, I got the expected output as shown in the screenshot below.

Advanced Filtering and Search Techniques

Method 4: Pattern-Based Searches

For enterprises with standardized naming conventions:

-- Search for specific procedure patterns modified recently
DECLARE @SearchPattern NVARCHAR(100) = '%Age%' -- Procedures related to customer Age
DECLARE @DaysBack INT = 30

SELECT 
    p.name AS ProcedureName,
    SCHEMA_NAME(p.schema_id) AS SchemaName,
    p.modify_date AS ModifiedDate,
    -- Extract procedure category from naming convention
    CASE 
        WHEN p.name LIKE 'usp_Customer_Insert%' THEN 'INSERT_OPERATIONS'
        WHEN p.name LIKE 'usp_Customer_Update%' THEN 'UPDATE_OPERATIONS'
        WHEN p.name LIKE 'usp_Customer_Delete%' THEN 'DELETE_OPERATIONS'
        WHEN p.name LIKE 'usp_Customer_Select%' THEN 'SELECT_OPERATIONS'
        ELSE 'OTHER_OPERATIONS'
    END AS OperationType
FROM sys.procedures p
WHERE p.name LIKE @SearchPattern
    AND p.modify_date >= DATEADD(DAY, -@DaysBack, GETDATE())
ORDER BY p.modify_date DESC, p.name

After executing the above query, I got the expected output as shown in the screenshot below.

Method 5: Change Frequency Analysis

For understanding modification patterns across development cycles:

-- Analyze procedure change frequency over time
WITH ChangeFrequency AS (
    SELECT 
        name AS ProcedureName,
        SCHEMA_NAME(schema_id) AS SchemaName,
        create_date,
        modify_date,
        DATEDIFF(DAY, create_date, modify_date) AS DaysSinceCreation,
        CASE 
            WHEN modify_date = create_date THEN 0
            ELSE DATEDIFF(DAY, create_date, modify_date)
        END AS ModificationAge
    FROM sys.procedures
    WHERE modify_date >= DATEADD(MONTH, -3, GETDATE()) -- Last 3 months
)
SELECT 
    ProcedureName,
    SchemaName,
    create_date AS CreatedDate,
    modify_date AS LastModified,
    DaysSinceCreation,
    CASE 
        WHEN ModificationAge = 0 THEN 'NEWLY_CREATED'
        WHEN ModificationAge <= 7 THEN 'RECENTLY_MODIFIED'
        WHEN ModificationAge <= 30 THEN 'ACTIVELY_MAINTAINED'
        ELSE 'STABLE'
    END AS ChangeCategory
FROM ChangeFrequency
ORDER BY modify_date DESC

After executing the above query, I got the expected output as shown in the screenshot below.

Performance Considerations and Best Practices

Optimization Tips for Large Databases

Working with databases containing thousands of stored procedures across enterprises in Miami and Las Vegas, I’ve learned these optimization techniques:

Query Performance Best Practices:

• Always use appropriate date range filters to limit result sets
• Create indexes on modify_date columns in custom tracking tables
• Use NOLOCK hint cautiously for read-only monitoring queries
• Implement pagination for large result sets

Resource Management:

• Schedule intensive auditing queries during off-peak hours
• Use SQL Server Agent for automated, recurring checks
• Implement proper error handling and logging
• Monitor query execution plans for optimal performance

Conclusion

The ability to efficiently track stored procedure modifications has proven invaluable for maintaining system integrity, compliance, and operational excellence.

The methods I’ve shared in this article represent battle-tested approaches that have served me well across diverse industries and environments. Whether you’re a database administrator, a developer tracking deployment changes, or a DBA troubleshooting performance issues, these techniques will help you maintain better visibility into your stored procedure.

Key Takeaways:

• Use sys.procedures for the most direct and efficient queries
• Implement automated monitoring for critical production environments
• Always include schema information in your tracking
• Consider timezone implications for multi-location deployments
• Establish proper security and access controls for monitoring activities

You may also like the following articles.

• How to Insert into Table from Stored Procedure with Parameters
• How to Insert Data Using Stored Procedure in SQL Server
• Stored Procedure Naming Convention

Bijay Kumar Sahoo

After working for more than 15 years in the Software field, especially in Microsoft technologies, I have decided to share my expert knowledge of SQL Server. Check out all the SQL Server and related database tutorials I have shared here. Most of the readers are from countries like the United States of America, the United Kingdom, New Zealand, Australia, Canada, etc. I am also a Microsoft MVP. Check out more here.