Understanding Your Data Via SQL Objects

Prior to ASP.NET Dynamic Data and Entity Framework, I began development of a similar solution realizing I spent a lot of time writing the Database I/O logic and admin screens rather than focusing on business rules and the "meat" of the application UI.

My solution included a tool that used SQL tables and stored procedures to create a fully operational class, much like CodeSmith and other code generation tools.  But because I like to build my own solutions I still use my CodeGen tool and am in the process up updating it to use templates to allow for a more flexible implementation of my objects in future.  Eventually, I may provide this tool for free and catapult myself into Internet fame and Theoretical Dollar fortune but as of now, I don't have the new Code Gen tool in a usable state.

So, how am I doing this?  Well, its information that is already readily available in SQL.

In your SQL database you have a series of objects the define the tables,views, procs, etc that you've created.

SYSOBJECTS

The first of these tables is the SYSOBJECTS table.  This contains a top level list of basically everything you've ever created in your database.

To see what objects are in your database is as simple as Select * FROM SYSObjects

This will return all the objects in your database.  However, there is some confusing and potentially useless data so I'll walk through what is what.  ID and Name will be the two most useful items.  ID will allow you to find the columns in these objects and the Name is the actual name you specified of your object.

You'll notice that primary keys, foreign keys, etc also appear in here.  Any object that is a child of an object (such as the keys) will have a "parent_obj" ID.

The XTYPE is the indicator for what the object is.  So a user table is 'U', system (SQL) stuff is 'S', Primary Keys: PK, Foreign Keys: F, Views: V, and Procs: P.  There are a few others as well.

For the purposes of this example, to find all the user tables you'd look for an XType of 'U' and a status > 0. Without the status check, you can end up with some tables that are not yours.  I'm not sure why this is (this column is undocumented in SQL), but I found that this fixes the problem as user tables have a positive status value.

So, at this point we know we have tables.  Now what?  Now, we need to inspect each table's design.

SYSCOLUMNS

Once we have ID of the object we want to inspect, we can find out what its contents are from the SYSCOLUMNS table.

SELECT * FROM SYSCOLUMNS WHERE ID = 12345 where 12345 would be your table's object ID.

This will return a lot of stuff but only a few fields, again, are important.  Name will be the name of the column, xtype will be the data type of that column (more on that later), length is the length of the field in terms of its dataype (IE, a string type datatype's max number of characters or the number of bytes for an integer), scale and precision is there, order of the column in the table, and if the field allows null.  There is additional info but for the sake of brevity I won't discuss them.

As you can see, you can tell a lot about your data via these objects.  You do have to do some mapping from the SQL types to the .NET types, but that isn't difficult.

Speaking of types, you get that information from SYSTYPES.  Simply join the xtype of SYSCOLUMNS to the xtype of SYSTYPES.  SYSTYPES has some default information about the data type but mainly, name is what you are concerned about.  I only look for types of a status of (0,2) because otherwise you get back some funky stuff (one day I'll figure out why this is).

Misc

I should mention that for tables and views you will get back all the columns displayed.  As of yet, I haven't figured out how to relate fields of a view back to its source table.

For procs you will only get the parameters from the syscolumns table.  If you're trying to write an object around a proc you have to prompt the user in the Code Gen tool for some values so you'll get a result set back.  You can then get the column names and associated data types of the fields returned to build your class definition.

There are other tables for permissions, users, keys, indexes, etc that you might want to explore.  I would be careful of trying to modify these tables as it may have undesirable results.

Putting It Together

I didn't include any code here because it would be quite length and I encourage you to play with your own implementations.

Some things you can do with this data:
1. Build an object using the table name and create correctly typed declarations/properties for each field.  As mentioned earlier, you will have to map data types in SQL to a .NET type IE bits are Boolean and most character fields are strings, etc.

2. Using the syscolumns you could enforce some basic validation is null allowed, max length, etc.

3. Identify primary keys in your classes, etc.

4. Create CRUD operations select, insert, update, and delete.