Query to show all stored procedure with their code

Question

hi friends Would you like to tell me that I want a query that returns a list of all the (user) stored procedures in a database by name with the number of lines of code for each one.

Pravin More · Answer

Hi Michell if you want number of line along with spname and code then use below code.... create 1 user defined function like below which return you count of line of spname you passed and call that function in select clause.......... create FUNCTION [dbo].[CountOfLine] ( -- Add the parameters for the function here @SPNAME varchar(max) ) RETURNS TABLE AS RETURN ( create table #tempcnt(code varchar(max)) insert into #tempcnt exec sp_helptext @SPNAME select count(*) from #tempcnt ) and write query like below Select NameOBJECT_DEFINITION(object_id) as CodeNumberOFLine= dbo.CountOfLine(Name) from sys.procedures Thanks Pravin.

Satyapriya Nayak · Answer

Narayan query is Correct.

Narayan · Answer

For SQL Server Select NameOBJECT_DEFINITION(object_id) from sys.procedures