One Way to Find Broken Code on Your Server

Before we approached our last major SQL Server upgrade, I was curious about what might break.  Yes, I had used the DEA to check our code against deprecated or discontinued code.  But I am talking about code that might not have been used in some time and would break because objects no longer existed, or other things like that.  So I wrote these scripts to refresh the sprocs, views and functions in our (non-production) environment.  These scripts automatically weed out schema-bound objects (which will not work with these), give you a list of what’s left, and then the scripts to refresh the metadata.  When those scripts are run, if or when they fail, they generally give you a very good reason why, which you can then give to your developers (hopefully, in enough time to get them fixed prior to the upgrade).  I have left the line of code that would automatically execute the scripts commented out, but it can be used at your discretion. I hope you find these helpful.

Stored Procedures:

IF OBJECT_ID('tempdb..#SprocInfo') IS NOT NULL
    DROP TABLE #SprocInfo;
IF OBJECT_ID('tempdb..#ScriptInfo') IS NOT NULL
    DROP TABLE #ScriptInfo;
IF OBJECT_ID('tempdb..#SBSprocInfo') IS NOT NULL
    DROP TABLE #SBSprocInfo;
CREATE TABLE #SBSprocInfo
(
    DatabaseName sysname,
    SchemaName sysname,
    SchemaBoundSprocName sysname
);
CREATE TABLE #SprocInfo
(
    DatabaseName sysname,
    SchemaName sysname,
    EligibleSprocName sysname
);
CREATE TABLE #ScriptInfo
(
    Script NVARCHAR(MAX)
);
PRINT 'Gathering preliminary info.... finding schema bound sprocs. This will not work with schema bound sprocs.';
INSERT INTO #SBSprocInfo
(
    DatabaseName,
    SchemaName,
    SchemaBoundSprocName
)
SELECT DB_NAME() AS DatabaseName,
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
OBJECT_NAME(i.object_id) AS SchemaBoundSprocName
FROM sys.indexes AS i
WHERE i.OBJECT_ID in (SELECT v.object_id FROM sys.procedures AS v);
SELECT *
FROM #SBSprocInfo;
PRINT 'Getting the eligible sprocs....';
INSERT INTO #SprocInfo
SELECT DB_NAME() AS DatabaseName, 
s.name AS SchemaName, 
o.name AS EligibleSprocName
FROM sys.objects AS o
INNER JOIN sys.schemas AS s 
ON o.schema_id = s.schema_id
WHERE type_desc = 'SQL_STORED_PROCEDURE'
AND is_ms_shipped = 0
AND o.name NOT IN (SELECT SchemaBoundSprocName COLLATE DATABASE_DEFAULT FROM #SBSprocInfo);
SELECT *
FROM #SprocInfo;
PRINT 'Building and executing the script!';
INSERT INTO #ScriptInfo
(
    Script
)
SELECT N'USE [' + DatabaseName + N']; EXECUTE sp_refreshsqlmodule N''' + SchemaName + '.' + EligibleSprocName + N''';'
FROM #SprocInfo;
DECLARE @FinalScriptName sysname;
DECLARE @FinalScriptCursor CURSOR;
SET @FinalScriptCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT Script
FROM #ScriptInfo;
OPEN @FinalScriptCursor;
FETCH NEXT FROM @FinalScriptCursor
INTO @FinalScriptName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT @FinalScriptName;
    --EXECUTE sp_executesql @FinalScriptName;
    FETCH NEXT FROM @FinalScriptCursor
    INTO @FinalScriptName;
END;
CLOSE @FinalScriptCursor;
DEALLOCATE @FinalScriptCursor;
SELECT *
FROM #ScriptInfo;
DROP TABLE #SprocInfo;
DROP TABLE #ScriptInfo;
DROP TABLE #SBSprocInfo;

Views:

IF OBJECT_ID('tempdb..#ViewInfo') IS NOT NULL
    DROP TABLE #ViewInfo;
IF OBJECT_ID('tempdb..#ScriptInfo') IS NOT NULL
    DROP TABLE #ScriptInfo;
IF OBJECT_ID('tempdb..#SBViewInfo') IS NOT NULL
    DROP TABLE #SBViewInfo;
CREATE TABLE #SBViewInfo
(
    DatabaseName sysname,
    SchemaName sysname,
    SchemaBoundViewName sysname
);
CREATE TABLE #ViewInfo
(
    DatabaseName sysname,
    SchemaName sysname,
    EligibleViewName sysname
);
CREATE TABLE #ScriptInfo
(
    Script NVARCHAR(MAX)
);
PRINT 'Gathering preliminary info.... finding schema bound views. This will not work with schema bound views.';
DECLARE @SBViewsName sysname;
DECLARE @SBViewsCursor CURSOR;
DECLARE @SBViewsSQL NVARCHAR(MAX);
SET @SBViewsCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      AND database_id > 4
      AND is_read_only = 0
      AND state_desc = 'ONLINE'
      AND name NOT LIKE 'ReportServer%'
ORDER BY name;
OPEN @SBViewsCursor;
FETCH NEXT FROM @SBViewsCursor
INTO @SBViewsName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @SBViewsSQL
        = N'USE [' + @SBViewsName + N'];

SELECT '''        + @SBViewsName
          + N''' AS DatabaseName,
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
OBJECT_NAME(i.object_id) AS SchemaBoundViewName
FROM sys.indexes AS i
WHERE i.OBJECT_ID in (SELECT v.object_id FROM sys.views AS v);';
    PRINT @SBViewsSQL;
    INSERT INTO #SBViewInfo
    EXECUTE sp_executesql @SBViewsSQL;
    FETCH NEXT FROM @SBViewsCursor
    INTO @SBViewsName;
END;
CLOSE @SBViewsCursor;
DEALLOCATE @SBViewsCursor;
SELECT *
FROM #SBViewInfo;
PRINT 'Getting the eligible views....';
DECLARE @ViewInfoName sysname;
DECLARE @ViewInfoCursor CURSOR;
DECLARE @ViewSQLCursor NVARCHAR(MAX);
SET @ViewInfoCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      AND database_id > 4
      AND is_read_only = 0
      AND state_desc = 'ONLINE'
ORDER BY name;
OPEN @ViewInfoCursor;
FETCH NEXT FROM @ViewInfoCursor
INTO @ViewInfoName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @ViewSQLCursor
        = N'USE [' + @ViewInfoName + N'];
INSERT INTO #ViewInfo
SELECT '''           + @ViewInfoName
          + N''' AS DatabaseName, 
s.name AS SchemaName, 
o.name AS EligibleViewName
FROM sys.objects AS o
INNER JOIN sys.schemas AS s 
ON o.schema_id = s.schema_id
WHERE type_desc = ''VIEW''
AND is_ms_shipped = 0
AND o.name NOT IN (SELECT SchemaBoundViewName COLLATE DATABASE_DEFAULT FROM #SBViewInfo)';
    PRINT @ViewSQLCursor;
    EXECUTE sp_executesql @ViewSQLCursor;
    FETCH NEXT FROM @ViewInfoCursor
    INTO @ViewInfoName;
END;
CLOSE @ViewInfoCursor;
DEALLOCATE @ViewInfoCursor;
SELECT *
FROM #ViewInfo;
PRINT 'Building and executing the script!';
INSERT INTO #ScriptInfo
(
    Script
)
SELECT N'USE [' + DatabaseName + N']; EXECUTE sp_refreshview N''' + SchemaName + '.' + EligibleViewName + N''';'
FROM #ViewInfo;
DECLARE @FinalScriptName sysname;
DECLARE @FinalScriptCursor CURSOR;
SET @FinalScriptCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT Script
FROM #ScriptInfo;
OPEN @FinalScriptCursor;
FETCH NEXT FROM @FinalScriptCursor
INTO @FinalScriptName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT @FinalScriptName;
    --EXECUTE sp_executesql @FinalScriptName;
    FETCH NEXT FROM @FinalScriptCursor
    INTO @FinalScriptName;
END;
CLOSE @FinalScriptCursor;
DEALLOCATE @FinalScriptCursor;
SELECT *
FROM #ScriptInfo;
DROP TABLE #ViewInfo;
DROP TABLE #ScriptInfo;
DROP TABLE #SBViewInfo;

Functions:

IF OBJECT_ID('tempdb..#FunctionInfo') IS NOT NULL
    DROP TABLE #FunctionInfo;
IF OBJECT_ID('tempdb..#ScriptInfo') IS NOT NULL
    DROP TABLE #ScriptInfo;
IF OBJECT_ID('tempdb..#SBFunctionInfo') IS NOT NULL
    DROP TABLE #SBFunctionInfo;
CREATE TABLE #SBFunctionInfo
(
    DatabaseName sysname,
    SchemaName sysname,
    SchemaBoundFunctionName sysname
);
CREATE TABLE #FunctionInfo
(
    DatabaseName sysname,
    SchemaName sysname,
    EligibleFunctionName sysname
);
CREATE TABLE #ScriptInfo
(
    Script NVARCHAR(MAX)
);
PRINT 'Gathering preliminary info.... finding schema bound functions. This will not work with schema bound functions.';
DECLARE @SBFunctionsName sysname;
DECLARE @SBFunctionsCursor CURSOR;
DECLARE @SBFunctionsSQL NVARCHAR(MAX);
SET @SBFunctionsCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      AND database_id > 4
      AND is_read_only = 0
      AND state_desc = 'ONLINE'
      AND name NOT LIKE 'ReportServer%'
 ORDER BY name;
OPEN @SBFunctionsCursor;
FETCH NEXT FROM @SBFunctionsCursor
INTO @SBFunctionsName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @SBFunctionsSQL
        = N'USE [' + @SBFunctionsName + N'];

SELECT '''        + @SBFunctionsName
          + N''' AS DatabaseName,
OBJECT_SCHEMA_NAME(i.object_id) AS SchemaName,
OBJECT_NAME(i.object_id) AS SchemaBoundFunctionName
FROM sys.indexes AS i
WHERE i.OBJECT_ID in (SELECT v.object_id FROM sys.views AS v);';
    PRINT @SBFunctionsSQL;
    INSERT INTO #SBFunctionInfo
    EXECUTE sp_executesql @SBFunctionsSQL;
    FETCH NEXT FROM @SBFunctionsCursor
    INTO @SBFunctionsName;
END;
CLOSE @SBFunctionsCursor;
DEALLOCATE @SBFunctionsCursor;
SELECT *
FROM #SBFunctionInfo;
PRINT 'Getting the eligible Functions....';
DECLARE @FunctionInfoName sysname;
DECLARE @FunctionInfoCursor CURSOR;
DECLARE @FunctionSQLCursor NVARCHAR(MAX);
SET @FunctionInfoCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT name
FROM sys.databases
WHERE source_database_id IS NULL
      AND database_id > 4
      AND is_read_only = 0
      AND state_desc = 'ONLINE'
      AND name NOT LIKE 'ReportServer%'
 ORDER BY name;
OPEN @FunctionInfoCursor;
FETCH NEXT FROM @FunctionInfoCursor
INTO @FunctionInfoName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @FunctionSQLCursor
        = N'USE [' + @FunctionInfoName + N'];
INSERT INTO #FunctionInfo
SELECT '''           + @FunctionInfoName
          + N''' AS DatabaseName, 
s.name AS SchemaName, 
o.name AS EligibleFunctionName
FROM sys.objects AS o
INNER JOIN sys.schemas AS s 
ON o.schema_id = s.schema_id
WHERE type_desc LIKE ''%Function%''
AND is_ms_shipped = 0
AND o.name NOT IN (SELECT SchemaBoundFunctionName COLLATE DATABASE_DEFAULT FROM #SBFunctionInfo)';
    PRINT @FunctionSQLCursor;
    EXECUTE sp_executesql @FunctionSQLCursor;
    FETCH NEXT FROM @FunctionInfoCursor
    INTO @FunctionInfoName;
END;
CLOSE @FunctionInfoCursor;
DEALLOCATE @FunctionInfoCursor;
SELECT *
FROM #FunctionInfo;
PRINT 'Building and executing the script!';
INSERT INTO #ScriptInfo
(
    Script
)
SELECT N'USE [' + DatabaseName + N']; EXECUTE sp_refreshsqlmodule N''' + SchemaName + '.' + EligibleFunctionName + N''';'
FROM #FunctionInfo;
DECLARE @FinalScriptName sysname;
DECLARE @FinalScriptCursor CURSOR;
SET @FinalScriptCursor = CURSOR FAST_FORWARD LOCAL FOR
SELECT Script
FROM #ScriptInfo;
OPEN @FinalScriptCursor;
FETCH NEXT FROM @FinalScriptCursor
INTO @FinalScriptName;
WHILE (@@FETCH_STATUS = 0)
BEGIN
    PRINT @FinalScriptName;
    --EXECUTE sp_executesql @FinalScriptName;
    FETCH NEXT FROM @FinalScriptCursor
    INTO @FinalScriptName;
END;
CLOSE @FinalScriptCursor;
DEALLOCATE @FinalScriptCursor;
SELECT *
FROM #ScriptInfo;
DROP TABLE #FunctionInfo;
DROP TABLE #ScriptInfo;
DROP TABLE #SBFunctionInfo;