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 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 this could 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.
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.
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.