Adventures with a SQL Server Database Schema

Wednesday, July 18, 2007 4:42:11 PM

Being the main person in my department for developing database driven websites, I get tasked with creating all sorts of databases with web frontends.  This task has lead to a fairly solid process that keeps evolving with time.  The problem with this process is that there are too many manual steps, here is a breakdown of my workflow from a 1000 foot level:

  • Create database structure with relationships
  • Create Visual Studio Solution and add a new website
  • Create a new DataSet
  • Drag all of my tables onto it
  • Create stored procedures for adds, updates, deletes and FetchBy's
  • Add pages with GridViews and FormViews for each table, or nested within a page where appropriate/necessary
  • Customize controls for various data types (Date Pickers, Drop Down Lists, etc.)
  • Impliment Security
  • Impliment Sitemap
  • Refine the interface
As you can see, there are a number of steps here, and none of them are really generic enough to be reused from project to project, leading to many hours doing mundane things.

Because of this I have started working on a custom Object-Relational Mapper (ORM) to create the DataSet for me from the database structure, generate the stored procedures, create the base web structure with SiteMap, MasterPages, GridViews, FormViews, etc.

In doing this I have come across several little quirks.  One of those deals with determining the maximum length, in bytes, of a column.  Now, I know about COL_LENGTH and it worked great...on my local SQLExpress copy, however on our production server I could not get it to return anything other than NULL, so I set out to find a better solution.  Here is the code that I was using to pull some of the SQL attributes about the columns:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, (
SELECT COL_LENGTH(TABLE_NAME, COLUMN_NAME)) AS Size,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS

After discovering the NULL issue on our production server and not being able to resolve it easily I started probing around the sys.* tables and came up with the following solution:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
(
SELECT max_length FROM sys.columns WHERE name = COLUMN_NAME AND object_id =
(
SELECT object_id FROM sys.tables
WHERE name = TABLE_NAME and SCHEMA_ID =
(
SELECT schema_id FROM sys.schemas WHERE name = TABLE_SCHEMA
)
)
) AS Size,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS

We are getting closer, however now it is pulling view's as well which produce NULLs in the Size column, I just want tables for now.  One more change and we should be set:

SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME,
(
SELECT max_length FROM sys.columns WHERE name = COLUMN_NAME AND object_id =
(
SELECT object_id FROM sys.tables
WHERE name = TABLE_NAME and SCHEMA_ID =
(
SELECT schema_id FROM sys.schemas WHERE name = TABLE_SCHEMA
)
)
) AS Size,
COLUMN_DEFAULT,
IS_NULLABLE,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH,
NUMERIC_PRECISION,
NUMERIC_SCALE
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME in (SELECT name FROM sys.tables)

Note the last line.  This final query will give me most of the information I need to build out my columns.  Hopefully you will find this useful.

For those of you wondering about my ORM code, I have yet to release it however it is currently doing more than I set out to accomplish and it is all developed in C# with managed code with the exception of using SQLDMO to retrieve a list of servers.  I will hopefully have something available for download soon, so sit tight.

Comments


Leave Comment

  

  

  




Are you human? Prove it!