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;

One comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s