Dead SQL Objects Code Query

Bring out your dead!

Just a quick post to share the script. At my current client, they have a large codebase that's not been cared for. I give them credit for generally avoiding dynamic SQL but the proliferation of autogenerated code has resulted in thousands, not an exaggeration, of objects that were generated and never used. I had run down fixing a dependency chain only to learn the rootmost object was broken as the day is long.

There has to be a better way of figuring this out. SQL Server Data Tools you say? Yeah, it took 2 days to reverse engineer the database and validate the dependencies only to run out of memory. I settled on sys.sp_refreshsqlmodule to at least give me a fighting chance of finding out what's broken in the database. The following query generates a list of all non-schemabound objects and then calls sys.sp_refreshmodules against the objects. if it raises an error, I drop it into a table so I can inspect it later as well as print out an error message. Quick and dirty, there's probably some logic issues in there, things that aren't dead yet, but this at least helps me triage the database.

-- This script attempts to generate a report on whether all the SQL modules are valid
-- in a database. This can generate false positives as there is no intelligence
-- built into the order to ensure object dependencies are followed.
SET NOCOUNT ON;
DECLARE 
    @SchemaName sysname
,   @ObjectName sysname
,   @QualifiedObjectName sysname
,   @QueryTemplate nvarchar(1000) = N'EXECUTE sys.sp_refreshsqlmodule @name;'
,   @ParamList nvarchar(20) = N'@Name sysname';


DECLARE ObjectCursor CURSOR READ_ONLY
FOR 
SELECT ALL
    SCHEMA_NAME(S.schema_id) AS SchemaName
,   S.name AS ObjectName
FROM 
    sys.objects AS S
    INNER JOIN
    (
        -- schema bound objects can't get recompiled
        SELECT
            SM.object_id
        FROM
            sys.sql_modules AS SM
        WHERE
            SM.is_schema_bound = 0
    ) AS SM
        ON SM.object_id = S.object_id
WHERE 
    S.is_ms_shipped = 0
    -- adjust this list as needed
    AND S.type in ('FN', 'IF', 'IT', 'P', 'TF', 'TR', 'V')
ORDER BY S.schema_id
;

DECLARE @ManualCleanup TABLE
(
    SchemaName sysname
,   ObjectName sysname
,   SQLStatement nvarchar(1000) NOT NULL
);

OPEN ObjectCursor

FETCH NEXT 
FROM ObjectCursor 
INTO @SchemaName
,   @ObjectName;

WHILE (@@fetch_status > -1)
BEGIN
    SET @QualifiedObjectName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@ObjectName);
    BEGIN TRY
        EXECUTE dbo.sp_executesql @queryTemplate, @ParamList, @Name = @QualifiedObjectName;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;
        INSERT INTO
            @ManualCleanup
        (
            SchemaName
        ,   ObjectName
        ,   SQLStatement
        )
        SELECT
            @SchemaName
        ,   @ObjectName
        ,   'EXECUTE sys.sp_refreshsqlmodule @name = N''' + @QualifiedObjectName + ''';';
    END CATCH

    FETCH NEXT 
    FROM ObjectCursor 
    INTO @SchemaName
    ,   @ObjectName;
END

CLOSE ObjectCursor
DEALLOCATE ObjectCursor


SELECT 
    MC.* 
FROM 
    @ManualCleanup AS MC;