Monday, June 27, 2016

T-SQL: Summary of all Stored Procs in DB

SELECT  @@Servername AS ServerName ,
        DB_NAME() AS DB_Name ,
        o.name AS SprocName ,
        o.Create_date ,
        sm.[definition] AS 'Stored Procedure script'
FROM    sys.objects o
        INNER JOIN sys.sql_modules sm ON o.object_id = sm.object_id
WHERE   o.[type] = 'P' -- Stored Procedures
        -- AND sm.[definition] LIKE '%insert%'
        -- AND sm.[definition] LIKE '%update%'
        -- AND sm.[definition] LIKE '%delete%'
        -- AND sm.[definition] LIKE '%tablename%'

ORDER BY o.name;

No comments: