Query to List SQL Server Stored Procedures Along with Lines of Code for Each Procedure

Query to list SQL Server stored procedures along with lines of code for each procedure

select t.sp_name, sum(t.lines_of_code) - 1 as lines_ofcode, t.type_desc
from
(
    select o.name as sp_name, 
    (len(c.text) - len(replace(c.text, char(10), ''))) as lines_of_code,
    case when o.xtype = 'P' then 'Stored Procedure'
    when o.xtype in ('FN', 'IF', 'TF') then 'Function'
    end as type_desc
    from sysobjects o
    inner join syscomments c
    on c.id = o.id
    where o.xtype in ('P', 'FN', 'IF', 'TF')
    and o.category = 0
    and o.name not in ('fn_diagramobjects', 'sp_alterdiagram', 'sp_creatediagram', 'sp_dropdiagram', 'sp_helpdiagramdefinition', 'sp_helpdiagrams', 'sp_renamediagram', 'sp_upgraddiagrams', 'sysdiagrams')
) t
group by t.sp_name, t.type_desc
order by 1

Edited so it should also now work in SQL Server 2000- 2008 and to exclude Database Diagram-related sprocs and funcs (which appear like user created objects).

Query to list all stored procedures

As Mike stated, the best way is to use information_schema. As long as you're not in the master database, system stored procedures won't be returned.

SELECT * 
  FROM DatabaseName.INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE'

If for some reason you had non-system stored procedures in the master database, you could use the query (this will filter out MOST system stored procedures):

SELECT * 
  FROM [master].INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE' 
   AND LEFT(ROUTINE_NAME, 3) NOT IN ('sp_', 'xp_', 'ms_')

How to view the stored procedure code in SQL Server Management Studio

Right click on the stored procedure and select Script Stored Procedure as | CREATE To | New Query Editor Window / Clipboard / File.

You can also do Modify when you right click on the stored procedure.

For multiple procedures at once, click on the Stored Procedures folder, hit F7 to open the Object Explorer Details pane, hold Ctrl and click to select all the ones that you want, and then right click and select Script Stored Procedure as | CREATE To.

How to count all stored procedures in the SQL Server for a database?

As the OP pointed out in a comment, all of the earlier answers are wrong, because they include system procedures. He specifically asked for procedures that were "written by me" -- and later clarified in another comment "other than the system procedure, written by me or anybody working on that data base."

So to exclude system procedures, the only differentiating field I see in sys.procedures is the name. Therefore you need to add a WHERE clause to any of the other answers, like this:

select count(*) from sys.procedures
where name not like 'sp_%'

SQL Server: How to find what lines are executed

So the extended events are the solution, this is how I have done it:

IF EXISTS(SELECT * FROM sys.server_event_sessions WHERE name='testMSSQLTrace')  
   DROP EVENT SESSION testMSSQLTrace ON SERVER;  

DECLARE @cmd VARCHAR(MAX) = '';
SELECT @cmd = 'CREATE EVENT SESSION testMSSQLTrace 
ON SERVER
    ADD EVENT sqlserver.sp_statement_completed
        (WHERE (sqlserver.database_name = N''' + DB_NAME() + '''))
    ADD TARGET package0.ring_buffer
        WITH (
            MAX_MEMORY = 2048 KB,
            EVENT_RETENTION_MODE = NO_EVENT_LOSS,
            MAX_DISPATCH_LATENCY = 3 SECONDS,
            MAX_EVENT_SIZE = 0 KB,
            MEMORY_PARTITION_MODE = NONE,
            TRACK_CAUSALITY = OFF,
            STARTUP_STATE = OFF
        );'

EXEC (@cmd)

This creates an event that can be fired after every statement completion, this is done dynamicly to filter on the database

Then I have 3 procedures that make controlling this event easy

/*******************************************************************************************
    Starts the statement trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_StartTrace
AS
BEGIN 
    ALTER EVENT SESSION testMSSQLTrace
          ON SERVER
        STATE = START; 
END
GO

/*******************************************************************************************
    Ends the statement trace, this also clears the trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_StopTrace
AS
BEGIN
    ALTER EVENT SESSION testMSSQLTrace
          ON SERVER
        STATE = STOP; 
END
GO

/*******************************************************************************************
    Saves the statements trace
*******************************************************************************************/
CREATE OR ALTER PROC testMSSQL.Private_SaveTrace
AS
BEGIN
    DECLARE @xml XML;

    SELECT @xml = CAST(xet.target_data AS xml)
        FROM sys.dm_xe_session_targets AS xet INNER JOIN sys.dm_xe_sessions AS xe ON (xe.address = xet.event_session_address)  
        WHERE xe.name = 'testMSSQLTrace'  

    INSERT INTO testMSSQL.StatementInvocations (testProcedure, procedureName, lineNumber, statement)
        SELECT testMSSQL.GetCurrentTest(), 
            OBJECT_NAME(T.c.value('(data[@name="object_id"]/value)[1]', 'int')),
            T.c.value('(data[@name="line_number"]/value)[1]', 'int'), 
            T.c.value('(data[@name="statement"]/value)[1]', 'VARCHAR(900)')
        FROM @xml.nodes('RingBufferTarget/event') T(c)
        WHERE T.c.value('(data[@name="nest_level"]/value)[1]', 'int') > 3

END
GO

These procedures respectivly start and stop the trace and the last one stores the result in a table where it filters on the nest level so my own code is not traced.

Finally I use it a bit like this:

start trace
start tran/savepoint
run SetUp (users code)
run test (users code)
save trace
save trace to variable
rollback tran (also catch errors and stuff like that)
save variable back to table so the trace is not rolled back

Special thanks to @Jeroen Mosterd for originally coming up with a proposal for this solution in this SQL Server: How to parse code into its different statements SO post

How to identify all stored procedures referring a particular table

SELECT Name
FROM sys.procedures
WHERE OBJECT_DEFINITION(OBJECT_ID) LIKE '%TableNameOrWhatever%'

BTW -- here is a handy resource for this type of question: Querying the SQL Server System Catalog FAQ

Search text in stored procedure in SQL Server

Escape the square brackets:

...
WHERE m.definition Like '%\[ABD\]%' ESCAPE '\'

Then the square brackets will be treated as a string literals not as wild cards.

How do I find a stored procedure containing text?

SELECT ROUTINE_NAME, ROUTINE_DEFINITION
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%Foo%' 
    AND ROUTINE_TYPE='PROCEDURE'

---

SELECT OBJECT_NAME(id) 
    FROM SYSCOMMENTS 
    WHERE [text] LIKE '%Foo%' 
    AND OBJECTPROPERTY(id, 'IsProcedure') = 1 
    GROUP BY OBJECT_NAME(id)

---

SELECT OBJECT_NAME(object_id)
    FROM sys.sql_modules
    WHERE OBJECTPROPERTY(object_id, 'IsProcedure') = 1
    AND definition LIKE '%Foo%'

Programmatically retrieve SQL Server stored procedure source that is identical to the source returned by the SQL Server Management Studio gui?

EXEC sp_helptext 'your procedure name';

This avoids the problem with INFORMATION_SCHEMA approach wherein the stored procedure gets cut off if it is too long.

Update: David writes that this isn't identical to his sproc...perhaps because it returns the lines as 'records' to preserve formatting? If you want to see the results in a more 'natural' format, you can use Ctrl-T first (output as text) and it should print it out exactly as you've entered it. If you are doing this in code, it is trivial to do a foreach to put together your results in exactly the same way.

Update 2: This will provide the source with a "CREATE PROCEDURE" rather than an "ALTER PROCEDURE" but I know of no way to make it use "ALTER" instead. Kind of a trivial thing, though, isn't it?

Update 3: See the comments for some more insight on how to maintain your SQL DDL (database structure) in a source control system. That is really the key to this question.

Get the text of a stored procedure in SQL Server

Two ways:

select name, object_definition(object_id) 
from sys.procedures

or

select object_name(p.object_id), definition
from sys.sql_modules as m
join sys.procedures as p
   on m.object_id = p.object_id

---

Related Topics