SQL Tip -- Check Tables for Data

One of the challenges of the Data Repository (DR) is that we often need sets of tables populated with data and, despite everyone’s best efforts, when the rubber hits the road – one of them is empty. The MT task to get it turned on missed it, or you were doing it yourself with a self-service Special IL (if you’re on C/S or MAGIC), and you skipped one yourself.

This is painful to find when you’re deep in the project. There should be a good way to check in advance which tables you need are populated or not. And you can certainly do that by doing a SELECT * FROM… per table. But how about a way to check a bunch of tables, in a batch operation, to see if they have data in them – and how much – in one go?

To this useful end, we’re going to use the capability of T-SQL to execute SQL commands created by other SQL commands in code. This is generally termed "dynamic SQL" and revolves around using the sp_executesql command. The attached code shows a simple and (hopefully) straightforward example of executing a stored procedure like this:

EXEC zcus.dbo.IatricDynamicTableVerify 'Livendb.dbo.DMisUserStatistics|Livefdb.dbo.MisAuditNprPatAccess_Main|Livefdb.dbo.HimRecAuditNpr_Main|Live

fdb.dbo.HimRecAudit_Main' ;

To check those four tables to see if they have data, how much in total, the first and last time they were updated and some very simple statistics about them. You’ll get result roughly like so:

When you dig into the code you’ll find that we create some tables to manage the "buckets" we want to check – that we use the IatricSplit() function (also attached to this tip) to break up the pipe(|)-delimited list of table names into a little table of its own – and then we loop (using WHILE instead of cursor, because cursors are evil…) through the list of table names passed in.

Within each loop we compose a SELECT statement in a text string, bringing in the name of the table to check, and then we EXEC the "created" statement to query the table for how many rows of data it has, when it was first and last updated and the average monthly volume of rows added to the table.

SET @cSQL = 'SELECT MAX('+CHAR(39)+@cTableID+CHAR(39)+') AS TableID'+

',COUNT(*) AS DataRowCount'+

',MIN(TBL.RowUpdateDateTime) AS FirstUpdated '+

',MAX(TBL.RowUpdateDateTime) AS LastUpdated '+

'FROM '+@cTableID+' TBL ;'

Putting this text string together is pretty simple, save perhaps for the use of CHAR(39) to embed single quotes inside of a single-quoted string. We do that because sometimes you do need to embed quotes and the CHAR() function is a straightforward way to do that.

Note also the use of the WHILE/@nIndex structure – this is a good, lightweight way to loop through a table of "things to do" while avoiding the use of a cursor.

Dynamic SQL can be used for lots of things; from this framework however you can expand the code to do batch operations on lists of tables which can be tremendously useful.

Extra Credit

Make a faster version that just gets the DataRowCount and use it to check that all of the tables you need for your Meaningful Use Stage 2 queries are populated. This gives you a counter-check to the MT process in mt_pr_2014_TableHealthCheck.

More Extra Credit

Review the attached code for the IatricSplit stored procedure as well. It may look a little strange, but it will parse out a very long string of delimited-by-something codes into a table of with each value in the ITEM field. So that this…

SELECT ITEMS.* FROM zcus.dbo.IatricSplit( '|','2WA|2N|2S') ITEMS

Gets you:

When used with Microsoft Reporting Services, you can easily build a delimited list of items picked in a parameter in the report, pass the string into the stored procedure and then break the list apart again into a table. And then… use that table in a JOIN or WHEN to limit results.

(end)