none
Script that looks for 'dead code' RRS feed

  • General discussion

  • Dear collegues,

    Recently, I was assigned the task to find and remove dead code from our database. Do you think it a nice idea to write a technet wiki together with a script that tries to find dead code in a database? If so, maybe my current script can be used as a starting point.

    Writing my script I took it as a given that permissions were handed out on roles. Every object that is accessible by a role is therefore deemed to be in use. Improvements on the script below are welcome.

    (EDIT 1: I cannot get the indentation right on this forum, so I opted to indent the script in such a way that it looks ok in management studio)

    (EDIT 2: The first version of the script below skipped objects that were referenced without using the schema (e.g. EXEC my_proc instead of EXEC dbo.my_proc) and it skipped triggers and objects called from triggers)

    SET NOCOUNT ON;
    GO
    
    CREATE TABLE #ObjectsUsed
      ( ObjectName   nvarchar(128)   NOT NULL
      , SchemaName   nvarchar(128)   NOT NULL
      , ObjectType   char(2)         NOT NULL
      , ObjectId     int             NOT NULL
      , Pass         int             NOT NULL
      );
    GO
    
    ALTER TABLE #ObjectsUsed
      ADD CONSTRAINT PK_ObjectsUsed
        PRIMARY KEY (ObjectId);
    GO
    
    CREATE UNIQUE INDEX IX_U_ObjectsUsed#ObjectName$SchemaName
      ON #ObjectsUsed (ObjectName, SchemaName);
    GO
    
    CREATE INDEX IX_NU_ObjectsUsed#Pass
      ON #ObjectsUsed (Pass);
    GO
    
    /* The assumption is that every object with permissions defined on it, is called from a client app. */
    INSERT INTO #ObjectsUsed
    (      ObjectName
    ,      SchemaName
    ,      ObjectType
    ,      ObjectId
    ,      Pass
    )
    SELECT DISTINCT
           ObjectName = OBJ.name
    ,      SchemaName = SCHEMA_NAME(OBJ.[schema_id])
    ,      ObjectType = OBJ.[type]
    ,      ObjectId   = OBJ.[object_id]
    ,      Pass       = 0
    FROM   sys.database_permissions AS PERM
    INNER  JOIN sys.database_principals AS GRANTEE
           ON (GRANTEE.principal_id = PERM.grantee_principal_id)
    INNER  JOIN sys.objects AS OBJ
           ON (OBJ.[object_id] = PERM.major_id)
    WHERE  PERM.class = 1     /* OBJECT_OR_COLUMN */
    AND    PERM.minor_id = 0  /* Exclude columns  */
    AND    GRANTEE.name IN (N'IsahReaderRole', N'IsahUserRole');
    GO
    
    DECLARE @RowsInserted int = -1
    ,       @Pass int = 0;
    WHILE @RowsInserted <> 0
    BEGIN
        SET @Pass = @Pass + 1;
        SET @RowsInserted = 0;
    
        /* Insert the objects properly referenced from the already present objects */
        INSERT INTO #ObjectsUsed
        (      ObjectName
        ,      SchemaName
        ,      ObjectType
        ,      ObjectId
        ,      Pass
        )
        SELECT ObjectName = OBJ.name
        ,      SchemaName = SCHEMA_NAME(OBJ.[schema_id])
        ,      ObjectType = OBJ.[type]
        ,      ObjectId   = OBJ.[object_id]
        ,      Pass       = @Pass
        FROM   #ObjectsUsed AS CALLING
        CROSS  APPLY sys.dm_sql_referenced_entities(CALLING.SchemaName + N'.' + CALLING.ObjectName, N'OBJECT') AS REF
        INNER  JOIN sys.objects AS OBJ
               ON OBJ.[object_id] = REF.referenced_id
        WHERE  CALLING.Pass = @Pass - 1
        AND    REF.referenced_class = 1     /* OBJECT_OR_COLUMN */
        AND    REF.referenced_minor_id = 0  /* Exclude columns  */
        AND    NOT EXISTS ( SELECT 1 FROM #ObjectsUsed AS CURRENT_SET WHERE CURRENT_SET.ObjectId = OBJ.[object_id] )
            UNION
        /* Add the referenced objects for which the schema name was left out (I assume the default schema is dbo) */
        SELECT ObjectName = OBJ.name
        ,      SchemaName = SCHEMA_NAME(OBJ.[schema_id])
        ,      ObjectType = OBJ.[type]
        ,      ObjectId   = OBJ.[object_id]
        ,      Pass       = CALLING.Pass + 1
        FROM   #ObjectsUsed AS CALLING
        CROSS  APPLY sys.dm_sql_referenced_entities(CALLING.SchemaName + N'.' + CALLING.ObjectName, N'OBJECT') AS REF
        INNER  JOIN sys.objects AS OBJ
               ON OBJ.name = REF.referenced_entity_name AND OBJ.[schema_id] = SCHEMA_ID(N'dbo')
        WHERE  REF.referenced_class = 1     /* OBJECT_OR_COLUMN */
        AND    REF.referenced_minor_id = 0  /* Exclude columns  */
        AND    REF.referenced_id IS NULL
        AND    NOT EXISTS ( SELECT 1 FROM #ObjectsUsed AS CURRENT_SET WHERE CURRENT_SET.ObjectId = OBJ.[object_id] )
            UNION
        /* For every table added, add the triggers. The triggers do not show up in sys.dm_sql_referenced_entities */
        SELECT ObjectName = TRIG.name
        ,      SchemaName = SCHEMA_NAME(OBJ.[schema_id])
        ,      ObjectType = TRIG.[type]
        ,      ObjectId   = TRIG.[object_id]
        ,      Pass       = @Pass
        FROM   #ObjectsUsed AS CALLING
        INNER  JOIN sys.triggers AS TRIG
               ON TRIG.parent_id = CALLING.ObjectId
        INNER  JOIN sys.objects AS OBJ
               ON OBJ.[object_id] = TRIG.[object_id]
        WHERE  CALLING.Pass = @Pass - 1
        AND    CALLING.ObjectType = 'U'
        AND    NOT EXISTS ( SELECT 1 FROM #ObjectsUsed AS CURRENT_SET WHERE CURRENT_SET.ObjectId = TRIG.[object_id] );
    
        SET @RowsInserted = @@ROWCOUNT;
    END;
    GO
    
    SELECT ObsoleteObject = SCHEMA_NAME(OBJ.[schema_id]) + N'.' + OBJ.name
    ,      ObjectType     = OBJ.[type]
    ,      ObjectId       = OBJ.[object_id]
    FROM   sys.objects AS OBJ
    WHERE  OBJ.[type] IN ('FN', 'P', 'TR', 'U', 'V')
    AND    OBJ.[object_id] NOT IN ( SELECT ObjectId FROM #ObjectsUsed )
    /* Exclude the Visual Studio refactor log */
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'U' AND OBJ.name = N'__RefactorLog')
    /* Exclude Management Studio diagramming objects  */
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'U' AND OBJ.name = N'sysdiagrams')
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'P' AND OBJ.name = N'sp_alterdiagram')
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'P' AND OBJ.name = N'sp_creatediagram')
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'P' AND OBJ.name = N'sp_dropdiagram')
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'P' AND OBJ.name = N'sp_helpdiagramdefinition')
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'P' AND OBJ.name = N'sp_helpdiagrams')
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'P' AND OBJ.name = N'sp_renamediagram')
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'P' AND OBJ.name = N'sp_upgraddiagrams')
    AND    NOT (OBJ.[schema_id] = 1 AND OBJ.[type] = 'FN' AND OBJ.name = N'fn_diagramobjects')
    ORDER  BY ObjectType, ObsoleteObject
    GO
    
    DROP TABLE #ObjectsUsed;
    GO


    • Edited by Chris Sijtsma Wednesday, June 26, 2013 8:55 AM Removed a typo
    Tuesday, June 25, 2013 9:29 AM

All replies

  • Please define, what means 'dead code' ?

    m@te

    Tuesday, June 25, 2013 11:15 AM
  • Thank you m@te for bringing this to my attention.

    Our application is an old fashioned client server application. I define code as reachable when it can be invoked from a client application. So every, proc, function, view or table that has permissions defined on them, I call reachable. Every piece of code that can be fired if a reachable piece of code is executed, I also call reachable.

    Example:

    Suppose the proc P1 has a GRANT EXECUTE defined. I would call this proc reachable.

    Suppose proc P1 fires proc P2. Proc P2 updates table T2. The update of table T2 fires the update trigger TRG_U_2. This trigger fires the function F3.

    I define all these indirectly fired elements (P2, T2, TRG_U_2, F3) as reachable.

    I define 'dead code' as objects (be it views, triggers, tables, functions or stored procedures) that cannot be reached via any such path.

    I know that this definition is not perfect. A proc can contain a LOOP or ELSE branch that logically cannot be executed. A proc that has a GRANT defined on it, might not be called from any client. These occurances of code that will never be executed are examples that my definition (and my code) do not capture. If you have a better definition, and a piece of code that will find the 'dead code' as you define it, please post it.


    • Edited by Chris Sijtsma Tuesday, June 25, 2013 3:03 PM Clarification
    Tuesday, June 25, 2013 11:28 AM