SQL + SSRS Tip: Get the Data Structure of a SQL Command

First…

This tip works if you are using SQL Server 2012 or better (Compatibility Level 110 and up). So before we get into the tip, check your Data Repository to see if you can use this tool:

When you run this query, you may find that you have varying compatibilities across your databases. Sometimes a specific application using a specific database may have a requirement, or your DBA simply may have not updated the compatibility levels as different upgrades, etc., were done.

In this case, we want the database where you’re going to set up the stored procedure to be at 110 or better. If the database holding the sp is not of the proper level, you can create the sp, but it will not run.

Then…

More than once, I’ve faced a situation where I needed to get the meta-data / data-structure of a complex query to create a table from that query. If you’re looking to create a physical table, you can do something like:

And that will get you a non-indexed, no-primary-key table (in SQL this is termed a heap) which is empty. Then you can add the indexes you need, etc.

But… Sometimes the table you want to create is an intermediate data-structure in a stored procedure and then you have to wrinkle out the code that creates it, or build a CREATE TABLE by hand, and generally get down in the dirt to get it done.

Then you have a scenario where you need to build a WITH RESULT SETS clause for calling a stored procedure that uses #temp tables from an SSIS package. Or you want to create a #temp table itself, on the fly, from the results of a stored procedure EXEC, and have the #temp table structure update automatically with each run.

SELECT TOP 0 * does not help you in that case!

Or you want to create documentation from the result set of the query (or stored procedure, or table-valued function) to paste into Excel. Again, you are out of luck.

In SQL 2012 and better, Microsoft has added a management view that can open the door to automating these processes:

sys.dm_exec_describe_first_result_set

With this table-valued function, we can get a little dataset of the names, data-types and NULL-ability of the columns in the result set. From that, we can start to do some more interesting things:

1. We can dynamically generate the code to create a physical or temp table from the meta-data
2. We can create a RESULT SETS clause from the same info
3. Or we can create a list of fields for documentation purposes
4. And since we are dynamically creating the code, we can add a PRIMARY KEY CLUSTERED on the fly as well

All we need to do is pass to the DESCRIBE a valid SQL command, either a SELECT or an EXEC, and then manage the results. Say we want to see what this query meta-data looks like:

We get a result-set like:

We can then process this result-set to create new output, like chunking out the code to create a temp table (yes, the field list has been shortened to save space):

We can do this on the fly, with any query, stored procedure, or view that we pass into the table-valued function. Of course, we must manipulate the result-set from the management query to get what we want… The code above was generated by:

Note in this code snippet that we’re using the FOR XML clause to create a delimited list of values (turning the rowsets into a concatenated string), which is also a super handy bonus tip! And it’s available from SQL 2008 up!

Using this same approach, we can generate other kinds of useful results:

WITH RESULT SETS

Or…

TAB DELIMITED LIST for EXCEL

Or we can use a stored procedure as well, and get the first result set from the sp. Note however, that the stored procedure needs to return a useful first result set. If the SP does not return what you want to see as the first set, you’re out of luck.

Creating a Temp Table in Code for use in Code

Another helpful use case scenario is to employ this technique to get the data structure of a stored procedure, use that structure to create a temp table, then fill the temp table with data, which you then compare against something else.

With this approach, the data-providing SP can change — fields can be added, removed, data-types can change — and your code will adapt dynamically.

The only “gotcha” here is that to run the dynamically created code and create a temp table that is visible to the calling process, it must be a ##GlobalTempTable rather than a #LocalTempTable.

Packaging It All Up…

To consolidate all of this and make your life easier, we’ve created a function called IatricDataStructureFromCommand which will let you call the code with a variety of parameters and get the different kinds of output we’ve discussed.

Enjoy!

Our Report Writing team can help you fix reports, create new ones, make old ones faster, and much more. Simply reach out to your Iatric Systems Account Executive or our NPR report writing team at reportwriting@iatric.com to discuss how we can help support your team!