Jul 29, 2015

Script to count total numbers of lines in a stored procedure in SQL Server

Below script can be used to count total numbers of line inside a stored procedure in SQL Server

--Specific Procedure
DECLARE @ProcedureName AS SYSNAME = 'YourSpName'


SELECT
     Name,
     LEN(OBJECT_DEFINITION([Object_id]))  - LEN(REPLACE(OBJECT_DEFINITION([Object_id]),CHAR(10),'')) AS TotalLine
FROM sys.procedures WHERE [object_id] = OBJECT_ID(@ProcedureName)

--All procedures
SELECT
     CONCAT(QUOTENAME(OBJECT_SCHEMA_NAME([Object_id])),'.', QUOTENAME(Name)) AS ProcedureName,
     LEN(OBJECT_DEFINITION([Object_id]))  - LEN(REPLACE(OBJECT_DEFINITION([Object_id]),CHAR(10),'')) AS TotalLine
FROM sys.procedures


Sample Output:
ProcedureName
TotalLine
[dbo].[uspLoadData]
52
[dbo].[uspCreateObject]
262
[dbo].[fg]
3


No comments:

Post a Comment