Introduction

Have you ever tried to determine column dependencies within your database? If yes, did you also try to determine these dependencies across different databases?

The first times I tried to analyze my databases, I became frustrated very quickly. All my efforts to analyze the code of views and SPs to get the necessary information led to the feeling that this is far too complex for me to handle within a short period of time.

But then after long hours of research, finally a thought reached my mind which turned out to be quite useful:

The concept

SQL Server provides the SHOWPLAN_XML SET statement, which lets you output the query plan of queries when set to ON. This query plan contains column references even across databases.

Lets have a look at a quick example:

   SET SHOWPLAN_XML ON
   GO
   SELECT * FROM sys.objects
   

If you execute these two batches, you should be able to click on the resulting execution plan. And inside this plan, you should be able to see a lot of the "ColumnReference" elements. Isn't this exactly what we need? Ok, it is still a little unorganized, since these references are spread among all levels of the XML document.

So in the next step we will try to extract only the relevant ColumnReference elements.

Extracting the relevant information

To store the column references, we also need a table:

CREATE TABLE [AdventureWorks].[dbo].[ColDep](
   [ID] [int] IDENTITY(1,1) NOT NULL,
   [Source]   [nvarchar] (255) NOT NULL,
   [Database] [nvarchar] (255) NOT NULL,
   [Schema]   [nvarchar] (255) NOT NULL,
   [Table]    [nvarchar] (255) NOT NULL,
   [Column]   [nvarchar] (255) NOT NULL
) ON [PRIMARY]

Now we need some external VB code, because there is no direct way to work with the resulting XML plan of the Showplan feature (of course thiscould be written in almost any kind of language):

Imports System.Data.SqlClient
Imports System.Xml
Public Module modColDepCheck
   ' Connect to the local server (.) and use a trusted connection
   Const cstrConnectionString = "Data Source=.;Trusted_Connection=yes;"
   Const cstrSQLText = "SELECT FirstName FROM Person.Contact WHERE LastName <> ''"
   ' Analyze the query stored in cstrSQLText    
   Sub Main()
      Dim lcmdCommand As New SqlCommand
      Dim lxmlReader As Xml.XmlReader
      Using lcnConnPlan As New SqlConnection(cstrConnectionString)
         lcnConnPlan.Open()
         lcmdCommand.Connection = lcnConnPlan
         lcmdCommand.CommandType = CommandType.Text
         ' Set Showplan_XML On so we can get the query plan with the dependencies inside
         lcmdCommand.CommandText = "USE AdventureWorks"
         lcmdCommand.ExecuteNonQuery()
         lcmdCommand.CommandText = "SET SHOWPLAN_XML ON"
         lcmdCommand.ExecuteNonQuery()
         ' Load the XML Plan 
         lcmdCommand.CommandText = cstrSQLText
         lxmlReader = lcmdCommand.ExecuteXmlReader()
         ' Now we parse the plan and insert each instance of ColumnReference into our 
         ' tracking table.
         Using lcnConnInsert As New SqlConnection(cstrConnectionString)
            ' Initialize insert connection, command + params.
            lcnConnInsert.Open()
            lcmdCommand.Connection = lcnConnInsert
            lcmdCommand.CommandText = "INSERT INTO AdventureWorks.dbo.ColDep SELECT @application, @database,@schema,@table,@column"
            lcmdCommand.Parameters.Add(New SqlParameter("@application", SqlDbType.NVarChar))
            lcmdCommand.Parameters.Add(New SqlParameter("@database", SqlDbType.NVarChar))
            lcmdCommand.Parameters.Add(New SqlParameter("@schema", SqlDbType.NVarChar))
            lcmdCommand.Parameters.Add(New SqlParameter("@table", SqlDbType.NVarChar))
            lcmdCommand.Parameters.Add(New SqlParameter("@column", SqlDbType.NVarChar))
            lcmdCommand.Parameters("@application").Value = cstrSQLText
            ' As long as we have elements to come...
            While lxmlReader.Read()
               If lxmlReader.Name = "ColumnReference" And lxmlReader.GetAttribute("Database") <> "" Then
                  lcmdCommand.Parameters("@database").Value = lxmlReader.GetAttribute("Database")
                  lcmdCommand.Parameters("@schema").Value = lxmlReader.GetAttribute("Schema")
                  lcmdCommand.Parameters("@table").Value = lxmlReader.GetAttribute("Table")
                  lcmdCommand.Parameters("@column").Value = lxmlReader.GetAttribute("Column")
                  lcmdCommand.ExecuteNonQuery()
               End If 'lxmlReader.Name = "ColumnReference" And lxmlReader.GetAttribute("Database") <> "" Then
            End While ' lxmlReader.Read()
         End Using 'lcnConnInsert 
      End Using ' lcnConnPlan
      Console.WriteLine("Done. Hit any key to continue...")
      Console.ReadKey()
      End
   End Sub
End Module

This code does the following: It activates the XML Showplan for the connection. From this point onwards, the connection will never execute statements until it encounters a SET SHOWPLAN_XML OFF statement. It will only output the XML query plan. We then issue the statement we want to check for column dependencies. The result will be an XML stream which we need to parse using the Read() and the GetAttribute() Methods of the XmlReader. The occurrences are directly saved into the ColDep table.

Try to play around with the cstrSQLText constant (the input query). As an example, you could use the following statement:

SELECT LastName FROM Person.Contact

It should return only the LastName column, since it is the only one being used.

Now try the following statement

      SELECT * FROM Person.Contact
      

The result should be all 15 columns being used.

And what happens if we use a view which uses an asterisk, which is based on a table that was modified without recompiling the view? Here is the script you need to simulate this

-- Create the table with one column
CREATE TABLE Test (OriginalColumn int NOT NULL)
GO
-- Create a view with asterisk. 
CREATE VIEW TestView AS SELECT * FROM Test
GO
-- Now add a column
ALTER TABLE Test ADD ColumnAdded int NULL
-- The table now returns all columns including the new one
SELECT * FROM Test
-- As expected, the view still returns only the columns
-- that were available when the view was created and not
-- the ColumnAdded column.
SELECT * FROM TestView
GO
-- Lets see now what the SHOWPLAN_XML returns
SET SHOWPLAN_XML ON
GO
SELECT * FROM TestView
GO

As expected, the query plan also only includes the columns that were available when the view was created.

You can try examples of your own. How about converts? Case statements? Anything that you can think of. Most of the stuff should work.

Restrictions

This method has some restrictions you need to keep in mind:

• Temporary tables created with SELECT INTO statements:
• If you try to execute the following query with SET SHOWPLAN_XML OFF, you should have no issue.

  SELECT 1 a INTO #temp 
  SELECT * FROM #temp
  DROP TABLE #temp
  GO
  

  But if you try to execute it with SET SHOWPLAN_XML ON, you will get an error "Invalid object name '#temp'." The reason for this is the fact that the table does not yet exist when the query is compiled. In regular execution mode, this would cause a re-compile of the query right after the creation of the temporary table. (This is actually the reason why creating temporary tables in stored procedures is usually a bad idea). More information about query recompiles can be found here: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx.

• 2. Dynamic SQL - Dynamic SQL cannot be parsed correctly. The query plan for dynamic SQL is the statement itself with no details. Try any kind of dynamic SQL statement to see the results of the SHOWPLAN_XML.

Conclusions

The discussed method is easy to implement and should be a good tool to view column dependencies across databases.

It can be used in an even more automated process which checks each view / trigger / stored procedure / function in every database

to generate an inventory.

You still have to keep the restrictions regarding dynamic SQL and temporary tables from SELECT INTO statements in mind.