How to Find and Fix Nested Views and Functions

I had only been a DBA for a short time when I was asked for help to troubleshoot a broken view.  How difficult could it be, right?

Trying to troubleshoot nested views

As it turned out, pretty darn difficult, if it’s a nested view.  The view in question was a seemingly bottomless pit of legacy code.  It wound up nesting about seven layers deep and took hours of effort as this then-newbie DBA worked through every synonym in every called view to see where the break was.

It’s not hard to see how this scenario can happen with views and functions.  A view (or function) is promoted into production that solves a difficult or frequently needed business scenario.  Everyone is happy with it. And now, it’s there. Part of what beginning developers are taught is to write portable code that can be used over and over.  That is what happens.  The new view becomes the foundation to build other views.  It’s efficient.  It’s easy.  It gets the job done!

Until someone changes something in one of the underlying views, and everything falls apart like a Jenga tower.

Let’s take a look.

USE AdventureWorks2012;
GO

--Demo begin

--Let's start off by making a nested view .... this view will call three other views

CREATE VIEW dbo.NestedEmployeeView
AS
SELECT ve.BusinessEntityID,
       ve.Title,
       ve.FirstName,
       ve.MiddleName,
       ve.LastName,
       ve.Suffix,
       ve.JobTitle,
       PhoneNumber,
       PhoneNumberType,
       EmailAddress,
       EmailPromotion,
       AddressLine1,
       AddressLine2,
       City,
       StateProvinceName,
       PostalCode,
       CountryRegionName,
       AdditionalContactInfo,
       ved.StartDate,
       veh.EndDate,
       'NowYouSeeMeNowYouDont' AS AdditionalColumn
FROM HumanResources.vEmployee AS ve
    INNER JOIN HumanResources.vEmployeeDepartment AS ved
        ON ve.BusinessEntityID = ved.BusinessEntityID
    INNER JOIN HumanResources.vEmployeeDepartmentHistory AS veh
        ON veh.BusinessEntityID = ved.BusinessEntityID;

GO


--And just to make it a little more real, let's nest it a little farther 

CREATE VIEW dbo.masterNestedView
AS
SELECT TOP 1000
       BusinessEntityID,
       Title,
       FirstName,
       MiddleName,
       LastName,
       Suffix,
       JobTitle,
       PhoneNumber,
       PhoneNumberType,
       EmailAddress,
       EmailPromotion,
       AddressLine1,
       AddressLine2,
       City,
       StateProvinceName,
       PostalCode,
       CountryRegionName,
       AdditionalContactInfo,
       StartDate,
       EndDate,
	   AdditionalColumn,
       'BlahBlahBlah' AS Column2,
       'StopJudgingMyData' AS Column3,
       'ItsJustADemo!' AS Column4
FROM dbo.NestedEmployeeView;
GO

--Let's behold the results of our terrible SQL practices....

SELECT BusinessEntityID,
       Title,
       FirstName,
       MiddleName,
       LastName,
       Suffix,
       JobTitle,
       PhoneNumber,
       PhoneNumberType,
       EmailAddress,
       EmailPromotion,
       AddressLine1,
       AddressLine2,
       City,
       StateProvinceName,
       PostalCode,
       CountryRegionName,
       AdditionalContactInfo,
       StartDate,
       EndDate,
	   AdditionalColumn,
       Column2,
       Column3,
       Column4	
FROM dbo.masterNestedView;
GO


--Now we have a view that goes 3 views deep - and everyone in the company knows it's there and uses it.  They've decided to start making stored procedures that call the view.  Let's make one of our own.

CREATE PROCEDURE dbo.CallTheMasterView (@BusinessEntityID INT)
AS
BEGIN

    SET NOCOUNT ON;

    SELECT BusinessEntityID,
           FirstName,
           LastName,
           JobTitle,
           PhoneNumber,
           EmailAddress,
           AdditionalColumn,
           AddressLine1,
           AddressLine2,
           City,
           StateProvinceName,
           PostalCode,
           CountryRegionName,
           Column2,
           Column3,
           Column4
    FROM dbo.masterNestedView
	WHERE BusinessEntityID = @BusinessEntityID;

END;
GO

--Let's try it out....

EXEC dbo.CallTheMasterView @BusinessEntityID = 69;
GO


--Now, someone alters one of the underlying views because they realize they don't really need all that information...

ALTER VIEW dbo.NestedEmployeeView
AS
SELECT ve.BusinessEntityID,
       ve.Title,
       ve.FirstName,
       ve.MiddleName,
       ve.LastName,
       ve.Suffix,
       ve.JobTitle,
       PhoneNumber,
       PhoneNumberType,
       EmailAddress,
       EmailPromotion,
       AddressLine1,
       AddressLine2,
       City,
       StateProvinceName,
       PostalCode,
       CountryRegionName,
       AdditionalContactInfo,
       ved.StartDate,
       veh.EndDate
FROM HumanResources.vEmployee AS ve
    INNER JOIN HumanResources.vEmployeeDepartment AS ved
        ON ve.BusinessEntityID = ved.BusinessEntityID
    INNER JOIN HumanResources.vEmployeeDepartmentHistory AS veh
        ON veh.BusinessEntityID = ved.BusinessEntityID;

GO


--Now what happens?

EXEC dbo.CallTheMasterView @BusinessEntityID = 69;


DROP VIEW dbo.masterNestedView;
DROP VIEW dbo.NestedEmployeeView;
DROP PROCEDURE dbo.CallTheMasterView;

This is a small demonstration, but you get the idea.

It’s a good idea to check for nested views and functions (either on the local instance or using a linked server) before they get into production and come back to haunt you.  This script will help you find nested views and functions on your server, if you use synonyms instead of three or four-part names.

IF OBJECT_ID('tempdb..#ViewsFunctionsCheck') IS NOT NULL
    DROP TABLE #ViewsFunctionsCheck;

IF OBJECT_ID('tempdb..#ServerDBNames') IS NOT NULL
    DROP TABLE #ServerDBNames;

IF OBJECT_ID('tempdb..#ViewsFunctions') IS NOT NULL
    DROP TABLE #ViewsFunctions;


CREATE TABLE #ServerDBNames
(	
    DatabaseName sysname NULL,
    SchemaName sysname NULL,
    SynonymName sysname NULL,
    BaseObject NVARCHAR(MAX) NULL,
    ExtractedServerName sysname NULL,
    ExtractedDatabaseName sysname NULL,
    ExtractedObjectName sysname NULL
);

CREATE TABLE #ViewsFunctionsCheck
(
    Script NVARCHAR(MAX) NULL
);


CREATE TABLE #ViewsFunctions
(
    ServerName sysname NULL,
    DatabaseName sysname NULL,
    name sysname NULL,
    type_desc sysname NULL,
    create_date DATETIME NULL,
    modify_date DATETIME NULL
);


--Let's get the synonyms from this server....

DECLARE @synName SYSNAME
DECLARE @synCursor CURSOR
DECLARE @synSQL NVARCHAR(MAX)

SET @synCursor = 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 @synCursor

FETCH NEXT FROM @synCursor INTO @synName

WHILE (@@FETCH_STATUS = 0) 
BEGIN	

SET @synSQL = N'USE [' + @synName + N']
	INSERT INTO #ServerDBNames
SELECT ''' + @synName + N''',
       SCHEMA_NAME(SCHEMA_ID),
       name,
       base_object_name,
       COALESCE((PARSENAME(base_object_name, 4)), @@SERVERNAME) AS ExtractedServerName,
       PARSENAME(base_object_name, 3) AS ExtractedDatabaseName,
	PARSENAME(base_object_name, 1) AS ExtractedObjectName
FROM sys.synonyms;'

PRINT @synSQL;
EXECUTE sp_executesql @synSQL;

	FETCH NEXT FROM @synCursor INTO @synName
END
	
CLOSE @synCursor;
DEALLOCATE @synCursor;


INSERT INTO #ViewsFunctionsCheck
(
    Script
)
SELECT (N'SELECT ''' + dbn.ExtractedServerName + N''', ''' + dbn.ExtractedDatabaseName + N''', name, type_desc, create_date, modify_date FROM ['
        + dbn.ExtractedServerName + N'].[' + dbn.ExtractedDatabaseName + N'].sys.objects WHERE is_ms_shipped = 0 AND (type_desc = ''VIEW'' OR type_desc LIKE ''%FUNCTION'');'
       ) AS Script
FROM #ServerDBNames AS dbn
    LEFT OUTER JOIN sys.servers AS ser
        ON ser.name = dbn.ExtractedServerName
GROUP BY dbn.ExtractedServerName, dbn.ExtractedDatabaseName;


DECLARE @GetVFsName NVARCHAR(MAX)
DECLARE @GetVFsCursor CURSOR
DECLARE @GetVFsSQL NVARCHAR(MAX)

SET @GetVFsCursor = CURSOR FAST_FORWARD LOCAL
FOR SELECT Script 
	FROM #ViewsFunctionsCheck

OPEN @GetVFsCursor

FETCH NEXT FROM @GetVFsCursor INTO @GetVFsName

WHILE (@@FETCH_STATUS = 0) 
BEGIN	
	SET @GetVFsSQL = N'INSERT INTO #ViewsFunctions ' + @GetVFsName
	PRINT @GetVFsSQL;
	EXECUTE sp_executesql @GetVFsSQL;
	FETCH NEXT FROM @GetVFsCursor INTO @GetVFsName
END
	
CLOSE @GetVFsCursor;
DEALLOCATE @GetVFsCursor;


SELECT sdbn.DatabaseName, sdbn.SynonymName, vf.type_desc, sdbn.BaseObject
FROM #ServerDBNames AS sdbn
LEFT OUTER JOIN #ViewsFunctions AS vf
ON sdbn.ExtractedServerName = vf.ServerName
AND sdbn.ExtractedDatabaseName = vf.DatabaseName
AND sdbn.ExtractedObjectName = vf.name
WHERE vf.type_desc IS NOT NULL
ORDER BY sdbn.DatabaseName;


DROP TABLE #ViewsFunctions;
DROP TABLE #ServerDBNames;
DROP TABLE #ViewsFunctionsCheck;

Take the output from here and plug the synonym names into this to find out exactly where they are used as nested views/functions:

CREATE TABLE #Results
(
    DatabaseName sysname NULL,
    ObjectName sysname NULL,
    ObjectType sysname NULL
);

DECLARE @searchParam NVARCHAR(50);
SET @searchParam = N'YourResultsHere'; 
DECLARE @dbName sysname;
DECLARE @dbCursor CURSOR;
DECLARE @sql NVARCHAR(MAX);

SET @dbCursor = 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 @dbCursor;

FETCH NEXT FROM @dbCursor
INTO @dbName;

WHILE (@@FETCH_STATUS = 0)
BEGIN
    SET @sql
        = N'USE [' + @dbName + N'];
	INSERT INTO #Results
	SELECT ''' + @dbName
          + N''' AS DatabaseName, 
	SPECIFIC_CATALOG + ''.'' + SPECIFIC_SCHEMA + ''.'' + SPECIFIC_NAME AS ObjectName,
              ROUTINE_TYPE AS ObjectType
FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE N''%' + @searchParam + N'%''
OR SPECIFIC_NAME LIKE N''%' + @searchParam + N'%''

UNION ALL


SELECT '''     + @dbName
          + N''' AS DatabaseName,  
		  TABLE_CATALOG + ''.'' + TABLE_SCHEMA + ''.'' + TABLE_NAME AS ObjectName, 
		  ''VIEW'' AS ObjectType
FROM INFORMATION_SCHEMA.VIEWS
WHERE VIEW_DEFINITION LIKE N''%' + @searchParam + N'%''
OR TABLE_NAME LIKE N''%' + @searchParam + N'%''
ORDER BY ObjectType, ObjectName;';
    FETCH NEXT FROM @dbCursor
    INTO @dbName;

    PRINT @sql;
    EXECUTE sp_executesql @sql;
END;

CLOSE @dbCursor;
DEALLOCATE @dbCursor;

SELECT *
FROM #Results
ORDER BY DatabaseName, ObjectType, ObjectName;

DROP TABLE #Results;

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