All of us sometimes have a need where we want to share database information between other developers and database administrators. For this reason I decided to begin work on an ASP.NET application that could be used to easily connect to a SQL Server, query the different system objects on the server, as well as the database schema. By querying the database schema we can easily store and retrieve the table, column, stored procedure, etc descriptions directly within the schema of the database.

This article describes how to create database definition with database object schemas.

Note that within this series I will make use of the Northwind Traders sample database. This can be downloaded from the following URL:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Part 1. Database Objects

I’d like to start with database objects.

The following list outlines the various common system objects found within SQL Server:

C = CHECK constraint
D = Default or DEFAULT constraint
F = FOREIGN KEY constraint
L = Log
FN = Scalar function
IF = Inlined table-function
P = Stored procedure
PK = PRIMARY KEY constraint (type is K)
RF = Replication filter stored procedure
S = System table
TF = Table function
TR = Trigger
U = User table
UQ = UNIQUE constraint (type is K)
V = View
X = Extended stored procedure

This query helps to find any database objects by database name, object type and/or object name.

   1:  DECLARE @sqlstr nvarchar(200)
   2:  DECLARE @objecttype varchar(20),
   3:          @objectname varchar(200)
   4:  DECLARE @#tblDBObjects table(
   5:                               dbName sysname,
   6:                               objName varchar(200),
   7:                               objtype char(2)
   8:                               )
   9:  Begin
  10:          set @objecttype = 'User table'
  11:          set @objectname = 'tbl_Orders' 
  12:   
  13:  If @objecttype = 'CHECK' 
  14:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''C'''''''
  15:    If @objecttype = 'Default' 
  16:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''D'''''''
  17:    If @objecttype = 'FOREIGN KEY' 
  18:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''F'''''''
  19:    If @objecttype = 'Log' 
  20:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''L'''''''
  21:    If @objecttype = 'Scalar function' 
  22:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''FN'''''''
  23:    If @objecttype = 'Inlined table-function' 
  24:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''IF'''''''
  25:    If @objecttype = 'Stored procedure' 
  26:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''P'''''''
  27:    If @objecttype = 'PRIMARY KEY' 
  28:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''PK'''''''
  29:    If @objecttype = 'Replication filter stored procedure' 
  30:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''RF'''''''
  31:    If @objecttype = 'System table' 
  32:    Select @sqlstr = 'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''S'''''''
  33:    If @objecttype = 'Table function' 
  34:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''TF'''''''
  35:    If @objecttype = 'Trigger' 
  36:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''TR'''''''
  37:    If @objecttype = 'User table' 
  38:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''U'''''''
  39:    If @objecttype = 'UNIQUE constraint' 
  40:    Select @sqlstr = 'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''UQ'''''''
  41:    If @objecttype = 'View' 
  42:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''V'''''''
  43:    If @objecttype = 'Extended stored procedure' 
  44:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects where xtype = ''''X'''''''
  45:    If (@objecttype = '') Or (@objecttype is Null)
  46:    Select @sqlstr =  'master..sp_msforeachdb '' select ''''?'''' as DBName, name, xtype From ?.dbo.sysobjects'''
  47:              End
  48:   
  49:    If (@sqlstr <> '') Or (@sqlstr is Not Null)
  50:     insert into @#tblDBObjects exec master..sp_executesql @sqlstr
  51:  Select * From @#tblDBObjects Where objName like @objectname

In the next installment we will analyze the code to retrieve the schema from database objects.