Getting metadata from query using sp_describe_first_result_set

One of the tasks that eats a lot of my ETL development time is documenting the views and procedures I write. I need to know where exactly the data is coming from. sp_describe_first_result_set provides a flag for outputting the source for many of the columns in a query.

exec sys.sp_describe_first_result_set N'exec [Integration].[GetOrderUpdates] ''20100101'', ''20170122''', null, 1;

The final parameter “1” tells the procedure to dig a little to find the source for each of the columns. Now when I say “dig” I mean more like scraping the surface rather than deep research. It can identify the source table and column for a column that has been aliased, but even a simple function hides the source column from this procedure. For example this procedure is unable to identify the source column from the following expression.

CAST(o.OrderDate AS date) AS [Order Date Key],

But it is great start. I was able to identify the source of about half of the columns returned from this query and that is half that I don’t have to hunt down.

About Russ

Russ Loski is a data warehouse developer focusing on SSIS and SQL Server development. He has worked with SQL Server since 1998. He has used various technologies to move data from one source to others. He has worked for well-known companies such as the Bank of America, the Dallas Cowboys Football Club, T-Mobile and Coca Cola Enterprises. Currently he is developing a Data Warehouse for a large health care organization.
This entry was posted in Documentation, ETL, Testing, TSQL. Bookmark the permalink.

Leave a Reply