sp 라인수 구하기

BEGIN TRAN

SET NOCOUNT ON
 
DECLARE @Schema VARCHAR(512)
DECLARE @ProcName VARCHAR(512)
DECLARE @CompleteProcName VARCHAR(1024)
DECLARE @Procid INTEGER
DECLARE @lines INTEGER
 
-- temp table for procdure text
 
CREATE TABLE #mt (t text)
 
-- Temp table for results
 
CREATE TABLE #res (SP VARCHAR(512),lines INTEGER)
 
DECLARE curProcs CURSOR  FOR
 
SELECT S.[Name] as [Schema], O.[NAME],O.[object_id] 
FROM sys.objects O
JOIN sys.schemas S
ON S.Schema_ID = O.Schema_ID
WHERE O.type='P'
AND O.[NAME]  LIKE 'P_SBP_%'

OPEN curProcs
 
FETCH NEXT FROM curProcs INTO @Schema, @ProcName,@Procid
 
WHILE  @@FETCH_STATUS=0
 
BEGIN
 
TRUNCATE TABLE #mt
 
SET @CompleteProcName = @Schema + '.' + @ProcName
 
INSERT INTO #mt exec sp_helptext @CompleteProcName

SELECT @lines=count(*) FROM #mt
 
INSERT INTO #res (sp,lines) VALUES (@ProcName,@Lines)
 
FETCH NEXT FROM curProcs INTO @Schema,@ProcName,@Procid
 
END
 
CLOSE CurProcs
 
DEALLOCATE CurProcs
 
--SELECT SP as 'SP''s in # of Lines order', Lines FROM #Res ORDER BY lines desc
SELECT SP as 'SP''s in alphabetical order', Lines FROM #Res ORDER BY SP desc
 
DROP TABLE #mt
 
DROP TABLE #res
 
COMMIT TRAN