====== How much code have I written? ======

This script iterates through a specified set of database code objects (SPs, views, functions etc) and uses a temporary SP (''[[##usp_GetCodeLines]]'') to report the number of lines of code in each object's definition.

<code tsql>DECLARE @sql VARCHAR(MAX) = 'DECLARE @objs TABLE (
  DbName VARCHAR(128)
, SchemaName VARCHAR(128)
, ObjName VARCHAR(128)
)'

SELECT @sql += '

INSERT INTO @objs (
  DbName
, SchemaName 
, ObjName 
)
SELECT 
  ''' + name + '''
, s.name
, o.name
FROM [' + name + '].sys.objects o
  INNER JOIN [' + name + '].sys.schemas s ON s.schema_id = o.schema_id
WHERE o.[type] IN (''P'', ''V'')'
FROM sys.databases
--WHERE name NOT IN ('master', 'msdb', 'model', 'tempdb')
--AND name NOT LIKE ('zz%')
WHERE name LIKE '%_Stg' OR name LIKE '%_Ifx' OR name LIKE '%_ID' 

SET @sql += '

SELECT * FROM @objs'

DECLARE @objs TABLE (
  DbName VARCHAR(128)
, SchemaName VARCHAR(128)
, ObjName VARCHAR(128)
, LinesOfCode INT DEFAULT 0
)

INSERT INTO @objs (
  DbName
, SchemaName 
, ObjName 
)
EXEC(@sql)

DECLARE @dbName VARCHAR(128)
DECLARE @objSchema VARCHAR(128)
DECLARE @objName VARCHAR(128)
DECLARE @rows INT = 0

WHILE 1 = 1
BEGIN

  SELECT TOP 1
    @dbName = DbName
  , @objSchema = SchemaName 
  , @objName = ObjName 
  FROM @objs
  WHERE LinesOfCode = 0
  
  IF @@ROWCOUNT = 0 BREAK
 
  IF @dbName = NULL
    BREAK
  
  EXEC @rows = #usp_GetCodeLines @dbName, @objSchema, @objName
  
  UPDATE @objs
  SET LinesOfCode = @rows
  WHERE DbName = @dbName
  AND SchemaName = @objSchema
  AND ObjName = @objName

  SET @dbName = NULL

END

SELECT *
FROM @objs
</code>

===== #usp_GetCodeLines =====
<code tsql>
USE [master] 
GO

IF OBJECT_ID('tempdb..#usp_GetCodeLines') IS NOT NULL
  DROP PROCEDURE #usp_GetCodeLines

GO

CREATE PROCEDURE #usp_GetCodeLines (
  @dbName VARCHAR(128)
, @objSchema VARCHAR(128)
, @objName VARCHAR(128)
)
AS 
BEGIN

SET NOCOUNT ON

DECLARE @code TABLE (
  Line VARCHAR(MAX)
)

INSERT INTO @code
EXEC('[' + @dbName + '].dbo.sp_helptext N''[' + @objSchema + '].[' + @objName + ']''')

RETURN @@ROWCOUNT

END

</code>

{{tag> tsql dynamic development}}