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:
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
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 this could be written in almost any kind of language):
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
Dim lcmdCommand As New SqlCommand
Dim lxmlReader As Xml.XmlReader
Using lcnConnPlan As New SqlConnection(cstrConnectionString)
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.CommandText = "SET SHOWPLAN_XML ON"
' 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.
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...
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")
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...")
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)
-- Create a view with asterisk.
CREATE VIEW TestView AS SELECT * FROM Test
-- 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
-- Lets see now what the SHOWPLAN_XML returns
SET SHOWPLAN_XML ON
SELECT * FROM TestView
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.
This method has some restrictions you need to keep in mind:
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.