Using the results from sp_help to automate the creation of History tables. (Part 1)

I decided that it would be fun to automate the creation of good history tables since that is something that many people leave until the end, or skip all together. I am going to start with a simple case and try to push through into Triggers and automatic Stored Procedure creation. To begin with, let’s take a look at the sp_help command and it’s results. sp_help is useful in looking at the details of objects, user defined types, and sql types. I am using the SQL Server Books Online from MSDN (http://doc.ddart.net/mssql/sql70/sp_help.htm) for reference.

A call to sp_help with no arguments will return a list of all objects, their owner, and their type. For example, calling sp_help on the master database of a SQLExpress returns 1831 rows.

I will be ignoring the second table for the foreseeable future, and concentrating on the first. I am going through the top list and getting the information about all ‘user table’ rows and presenting it in a ListBox. To help accomplish this, I have created a class SP_HelpResults with a constructor that accepts a DataSet. It then uses the number of tables returned along with the column names to determine what type of results the DataSet represents and parses it into usable objects. Once I have the objects describing an individual table, I will begin constructing the new table and Triggers. I will publish more details as the project continues.