Inspecting SQL Server's tables

A few days ago, I was working on a small project for autogenerating source code from a SQL Server database, and I bumped into the problem of obtaining from a given server the description of the tables in it. If using MySQL this task is quite simple, however, using SQL Server the problem is not that simple.


Long story short, after a few hours I came up with this "small" piece of code:


SELECT DISTINCT
     sys.tables.object_id AS TableId,
     sys.columns.column_id AS ColumnId,
     sys.columns.name AS ColumnName,
     sys.types.name AS TypeName,
     sys.columns.precision AS NumericPrecision,
     sys.columns.scale AS NumericScale,
     sys.columns.is_nullable AS IsNullable,
     ( 
     SELECT
          COUNT(column_name)
     FROM
          INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
     WHERE
          TABLE_NAME = sys.tables.name AND
          CONSTRAINT_NAME =
          ( 
          SELECT
               constraint_name
          FROM
               INFORMATION_SCHEMA.TABLE_CONSTRAINTS
          WHERE
               TABLE_NAME = sys.tables.name AND
               constraint_type = 'PRIMARY KEY' AND
               COLUMN_NAME = sys.columns.name
          )
     ) AS IsPrimaryKey,
     sys.columns.max_length / 2 AS CharMaxLength
FROM
     sys.columns, sys.types, sys.tables
WHERE
     sys.tables.object_id = sys.columns.object_id AND
     sys.types.system_type_id = sys.columns.system_type_id AND
     sys.types.user_type_id = sys.columns.user_type_id AND
     sys.tables.name = '{0}'
ORDER BY
     IsPrimaryKey

Nice uh?


Basically the whole deal after this code is just inspecting into the sys database, which holds the information about the tables in the database. Obviously the '{0}' should be replaced with the table in question. I place the '{0}' because this code was meant to be running using C#, so, all I needed was a call to string.Format.


After retrieving the data with the tables, it would be nice to inspect the relationships between those tables, uh? Well, that is simpler than you might think, just we need to retrieve the relationships list, again from the same database. SQL code for doing that is this one:


SELECT
     sys.foreign_keys.name AS RelationshipName,
     sys.foreign_keys.object_id AS RelationshipId,
     sys.foreign_keys.parent_object_id AS ParentTableId,
     sys.foreign_keys.referenced_object_id AS ReferencedTableId,
     sys.foreign_key_columns.parent_column_id AS ParentColumnId,
     sys.foreign_key_columns.referenced_column_id AS ReferencedColumnId
FROM
     sys.foreign_keys, sys.foreign_key_columns
WHERE
     object_id = constraint_object_id; 

After having all that data, we are in conditions to generate any source code based on any database inspected from a SQL Server 2k5/2k8 database, which is a cool thing.

No comments:

Post a Comment

Commenting is allowed!