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.