Introduction

Many projects do access

data through Stored Procedures. There are 5 standard stored procedures for

tables in the database. The standard procedures are for

1. Get: Getting a list

   from the table.

2. GetSingle:

   Getting single record from the table.

3. Add: Adding record to

   the table.

4. Update: Updating the

   edited record

5. Delete: Deleting

   single record.

Before I wrote my first

Stored Procedure generator, I used to create all these procedures manually.

So, in case of 10 tables database I had to write

50 stored procedures manually. This used to take time and I was getting

tired doing the same job over and over again. Also, in case of

complicated/large tables it used to leave room for ID10T (IDIOT) errors or

typing errors. So, I started thinking of designing a SQL procedure which

will generate all these procedures for me. The first Stored Procedure

Generator I wrote was using System tables. But System tables can change in

future versions of SQL Server, thats when I found out that SQL Server

provides Information Schema views on most of the information on the data.

So, I started writing another Stored Procedure generator which is well

organized and uses information schema views. In this article, I will explain

different ways of accessing meta data and also I

will give an example of Insert procedure generator.

This article

will initially explain the System Tables and System Stored Procedures,

then I will explain the recommended Information

Schema Views. I have structured it in this fashion to explain the complexity

of System Tables and how easy it is with Information Schema Views. Readers

can jump directly to Information Schema Views section.

The same

principle of building Stored Procedures do apply for Business functions or

building Data Access Layer functions that do access Stored Procedures. This article

will provide you some examples on building Stored Procedure Generator and

also building VB function Generator.

Free Download

of complete Stored Procedure Generator and DNN VB Function Generator is

available at

www.etekglobalInc.com

Accessing

Meta Data - System Tables

SQL Server stores

information about data in system tables. It also provides system stored

procedures to access this information or Information Schema Views to access

this information. Using the system tables needs to have in depth knowledge

on columns in these tables.

System Tables: Most common or used system tables are:

SysObjects:

This table keeps information on all objects in the database. The objects are

identified by the xtype column. Some of

xtypes are U  User Table, P  Stored Procedures, C  Check Constraint, F 

Foreign Key Constraint etc So, for example to get all tables in the

database you can write select statement as:

SELECT *

FROM sysObjects where xtype

= 'U'

SysColumns:

This system table stores information on all columns for tables. The column

identifying the relationship between columns and tables is the id column in

sysColumns table. Also, the data type for

columns is stored in xtype column. It relates to

the sysTypes table. For example, assume you have

a table called tblUsers. You would like to list

all columns, their data types, data size from this table. Your SQL would

look like

SELECT

syscolumns.name columnname,

systypes.name datatype,

syscolumns.length,

syscolumns.prec from

SysObjects JOIN syscolumns

ON SysObjects.id =

SysColumns.id JOIN

systypes ON

syscolumns.xtype = sysTypes.xtype Where

SysObjects.name = 'Users'

SysTypes:

This can be considered as lookup table to get all the Data types in the

database. To get all the data type names in the database your SQL will look

like this:

SELECT name

from Systypes

SysForeignKeys:

This keeps information regarding foreign key relationships in the database.

The way it is stored needs some explaination.

SysForeignkeys table has 4 important columns. The columns are:

Fkeyid:

This stores the ID related to SysObjects table.

The ID stored is for the table which contains the foreign key.

rkeyID:

The ID stored is for the referenced table that has the primary key.

Fkey:

This actually references to the column that is the foreign key in the table.

It is related to the colid column in the

sysColumns table.

Rkey:

References the primary key in the primary table. It is related to the

colid column in the

sysColumns table. 

To select

all tables that depend on Users table your select statement will look like 

SELECT

ChildTable.tablename,

ChildTable.columnname

FROM

sysforeignkeys JOIN (Select

SysObjects.Name tablename,

sysObjects.id,

sysColumns.name columnname,

sysColumns.colid

FROM SysObjects JOIN

sysColumns ON SysObjects.id =

sysColumns.id Where

sysobjects.xtype = 'U') PrimaryTable ON

sysforeignkeys.rkeyID =

PrimaryTable.ID AND sysforeignkeys.rkey =

PrimaryTable.colid JOIN (Select

SysObjects.Name tablename,

sysObjects.id,

sysColumns.name columnname,

sysColumns.colid

FROM SysObjects JOIN

sysColumns ON SysObjects.id =

sysColumns.id Where

sysobjects.xtype = 'U') ChildTable ON

sysforeignkeys.fkeyID =

ChildTable.ID AND sysforeignkeys.fkey =

ChildTable.colid WHERE

PrimaryTable.tablename = 'Users' 

Accessing

Meta Data - System Stored Procedures

Another way of accessing data about data is system stored procedures. I will

explain the system stored procedures for the above context except for

foreign keys as it is a bit complex. Also, while researching the foreign

keys system stored procedure I came across the Schema views.

Sp_Tables:

This stored procedure returns back all the table names. For example, to get

all user defined tables from XYZ database, your SQL will look like:

EXEC

sp_tables NULL, dbo,

XYZ, "'TABLE'"

Sp_columns:

This stored procedure

returns back all the columns for specified table. Lets consider the

example above. The best part about this stored procedure is it hides the

complexity of joins to get the data types and column names as shown in

system tables section. To get all columns in the Users table you will call

this procedure as:

Exec

sp_columns 'Users'

sp_datatype_info:

This stored procedure returns back information on all data types. Syntax is

going to be:

EXEC

sp_datatype_info

Accessing

Meta Data - Information Schema Views

All

the above ways do allow us to get the data about data. But the recommended

way is Information Schema views. The reasons are next versions of System

tables might change but the information schemas would remain the same. It

also hides the complexity. If you look back at the System Tables section you

will realize that you need to do some research or need to have in depth

knowledge of System Tables. But with information schema views it hides all

the join information etc. In this section, I will explain common used views.

Information_schema.Tables:

Returns information about Tables. To return all user defined table names in

the database you can write select as:

SELECT

TABLE_NAME from Information_schema.Tables WHERE

TABLE_TYPE = 'BASE TABLE'

Information_schema.Columns:

Returns information about columns in the table. To get column name, data

types and sizes for Users table, you can write SQL as:

SELECT

COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM

INFORMATION_SCHEMA.COLUMNS Where TABLE_NAME = 'Users'

For

data types that have fixed size like int,

datetime, the CHARACTER_MAXIMUM_LENGTH will

return null.

Information_schema.TABLE_CONSTRAINTS:

Returns information on all constraints. Users can get information on

specific tables. The Constraints are identified by the CONSTRAINT_TYPE

column. For example, to get all constraints on Users Table you can write SQL

as:

SELECT *

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where

Table_name = 'Users'

To get Primary key Constraint on the Users table you can write SQL as:

SELECT *

FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS Where

Table_name = 'Users' AND CONSTRAINT_TYPE = 'PRIMARY KEY'

Information_Schema.CONSTRAINT_COLUMN_USAGE:

Returns column information and the constraint associated with it.

For example, in the above example we got PRIMARY KEY CONSTRAINT

Information on Users table but we would like to have the column name. The

SQL will look like:

SELECT

COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE WHERE CONSTRAINT_NAME = 'PK_USERS'

Combining

Information_schema.Table_constraints and

Information_schema.

CONSTRAINT_COLUMN_USAGE, SQL will look like

SELECT

COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE JOIN

INFORMATION_SCHEMA.TABLE_CONSTRAINTS ON

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME =

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME WHERE

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME = 'Users' AND

CONSTRAINT_TYPE = 'PRIMARY KEY'

Information_schema.REFERENTIAL_CONSTRAINTS:

Returns information about foreign key constraints.

For

example, consider you have two tables in your database. One is users table

and one is UserRoles table. Users table has

UserID which is referenced in

UserRoles table. To get the foreign key column

information your SQL will look like 

SELECT

fkey.Table_name,

fkey.Column_name FROM

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rkey

JOIN

Information_schema.REFERENTIAL_CONSTRAINTS Ref

on rkey.CONSTRAINT_NAME =

Ref.Unique_Constraint_Name JOIN

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE fkey

ON Ref.CONSTRAINT_NAME =

fkey.CONSTRAINT_NAME WHERE

rkey.Table_Name = 'Users'

The

above SQL will get you the table name and column names which reference the

UserID table in the Users table.

Information_Schema.ROUTINES:

Returns information on Stored Procedure and functions. To get information on

all Stored Procedures in your database your SQL will be:

SELECT *

FROM Information_Schema.ROUTINES Where

Routine_type = 'PROCEDURE'

Information_Schema.PARAMETERS:

Returns information on Parameters for stored procedure. To

get information on Parameters for AddUser stored procedure your SQL will be:

SELECT

PARAMETER_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM

Information_Schema.PARAMETERS Where

Specific_name = 'AddUser'

This

section explained how you can use some of the information schema views to

extract data about your database.

Putting

it to work

This section will give

you two examples. The first one explains how you can create Delete Stored

Procedure for your table and the second example explains how you can build

VB declaration function using Stored Procedures created. You can create

Insert, Update, Get and GetSingle stored

procedures OR you can check the references section for information on

downloading free code.

Example 1: Stored Procedure Generation

This Stored Procedure

was designed following these standards -

1. Tables start with tbl and lookup tables

   start with tlkp.

2. All Stored procedures will have the format of procedure type and

   tablename without tbl

   and tlkp. For example,

   tblUsers table will have Get procedure name as

   GetUsers. In the following example it is

   going to be DeleteUsers.

CREATE   Procedure

prCreateDeleteProcedure

@table_Name

nvarchar(128),

@print bit

AS

Declare @SQLStatement

varchar(8000),

--Actual Delete Stored Procedure string

 @parameters varchar(8000),

-- Parameters to be passed to the Stored

Procedure

 @deleteStatement varchar(8000),

-- To Store the Delete SQL Statement

 @procedurename nvarchar(128),

-- To store the procedure name

 @DropProcedure nvarchar(1000)

--To Store Drop Procedure SQL Statement

--

Initialize Variables

SET @parameters = ''

SET @deleteStatement = ''

--Get

Parameters and Delete Where Clause needed for the Delete Procedure.

SELECT @parameters

= @parameters + Case When @parameters = '' Then ''

     Else ', ' + Char(13) + Char(10)

     End +

'@'

+  INFORMATION_SCHEMA.Columns.COLUMN_NAME + '

' +

     DATA_TYPE +

     Case When CHARACTER_MAXIMUM_LENGTH is not null Then

      '(' + Cast(CHARACTER_MAXIMUM_LENGTH as varchar(4))

+ ')'

      Else ''

     End,

 @deleteStatement = @deleteStatement

+ Case When @deleteStatement = '' Then ''

     Else ' AND ' + Char(13) + Char(10)

     End + INFORMATION_SCHEMA.Columns.COLUMN_NAME

+ ' = @' + + INFORMATION_SCHEMA.Columns.COLUMN_NAME

FROM INFORMATION_SCHEMA.Columns,

 INFORMATION_SCHEMA.TABLE_CONSTRAINTS,

 INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE

WHERE  INFORMATION_SCHEMA.TABLE_CONSTRAINTS.CONSTRAINT_NAME =

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.CONSTRAINT_NAME AND

 INFORMATION_SCHEMA.Columns.Column_name =

INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE.Column_name

AND

 INFORMATION_SCHEMA.Columns.table_name =

INFORMATION_SCHEMA.TABLE_CONSTRAINTS.TABLE_NAME AND

 INFORMATION_SCHEMA.TABLE_CONSTRAINTS.table_name

= @table_Name AND

 CONSTRAINT_TYPE = 'PRIMARY KEY'

--

the following logic can be changed as per your

standards. In our case tbl is for tables and

tlkp is for lookup tables. Needed to remove

tbl and tlkp...

SET @procedurename

= 'Delete'

If Left(@table_Name,

3) = 'tbl'

Begin

 SET @procedurename = @procedurename

+ SubString(@table_Name,

4, Len(@table_Name))

End

Else

Begin

 If Left(@table_Name, 4) = 'tlkp'

 Begin

  SET @procedurename = @procedurename

+ SubString(@table_Name,

5, Len(@table_Name))

 End

 Else

 Begin

  -- In case none of the above standards are

followed then just get the table name.

  SET @procedurename = @procedurename

+ @table_Name

 End

End

--Stores

DROP Procedure Statement

SET @DropProcedure = 'if exists (select * from

dbo.sysobjects where id =

object_id(N''[dbo].['

+ @procedurename + ']'') and OBJECTPROPERTY(id,

N''IsProcedure'') = 1)' + Char(13) + Char(10) +

    'Drop Procedure ' + @procedurename

-- In

case you want to create the procedure pass in 0 for @print else pass in 1

and stored procedure will be displayed in results pane.

If @print = 0

Begin

 -- Create the final procedure and store it..

 Exec (@DropProcedure)

 SET @SQLStatement = 'CREATE PROCEDURE ' + @procedurename

+  Char(13) + Char(10) + @parameters + Char(13) +

Char(10) + ' AS ' +

     + Char(13) + Char(10) + ' Delete FROM ' + @table_Name

+ ' WHERE ' + @deleteStatement + Char(13) +

Char(10)

 --

Execute the SQL Statement to create the procedure

 Exec (@SQLStatement)

End

Else

Begin

 --Print the Procedure to Results pane

 Print ''

 Print ''

 Print ''

 Print '--- Delete Procedure for ' + @table_Name

+ '---'

 Print @DropProcedure

 Print 'GO'

 Print 'CREATE PROCEDURE ' + @procedurename

 Print @parameters

 Print 'As'

 Print 'DELETE FROM ' + @table_Name

 Print 'WHERE ' + @deleteStatement

 Print 'GO'

End

GO

Example 2: Building VB functions based on Stored Procedures created -

This example is

specifically designed for DotNetNuke 2.0. The

following Stored procedure will generate function declaration for Data

Access Layer for specific stored procedure. This function needs to be

overridden.

CREATE PROCEDURE

prCreateDataProviderSubs

@TableName

nvarchar(4000)

AS

Declare @routineName

nvarchar(128), -- To Store Stored

Procedure Name

 @functionname

nvarchar(4000)

-- Actual VB Function or

Sub

-- As in

our case, we are removing tbl and

tlkp from Table Names when creating stored

procedure.

SET @TableName

= Replace(@TableName,

'|tbl', '|')

SET @TableName = Replace(@TableName,

'|tlkp', '|')

-- To

loop through all Stored Procedures to create Subs and Functions.

Declare

curRoutineName cursor For

SELECT  Routine_name

FROM  INFORMATION_SCHEMA.ROUTINES

WHERE  (CharIndex('|' +

SubString(Routine_name, 4, Len(Routine_name))

+ '|', @TableName) > 0 OR

 CharIndex('|' + SubString(Routine_name,

7, Len(Routine_name)) + '|', @TableName)

> 0 OR

 CharIndex('|' + SubString(Routine_name,

10, Len(Routine_name)) + '|', @TableName)

> 0)

Order by 1

Open

curRoutineName

FETCH NEXT FROM curRoutineName INTO @routineName

WHILE @@FETCH_STATUS =

0

Begin

 If @routineName is not null AND @routineName

<> ''

 Begin

  -- In case of Add, Get and

GetSingle the routines might return something.

  If Left(@routineName,

3) = 'Add' OR Left(@routineName, 3) = 'Get'

   SET @functionname = 'Public

MustOverride Function ' + @routineName

+ '('

  Else

   SET @functionname = 'Public

MustOverride Sub ' + @routineName

+ '('

 End

 -- Get all the parameter information and build

the parameter string for the function and sub.

 SELECT @functionname = @functionname

+ Case When Right(@functionname, 1) <> '(' Then

', '

      Else ''

      End +

     'ByVal ' + SubString(PARAMETER_NAME,

2, Len(PARAMETER_NAME)) +  ' as ' +

     Case When DATA_TYPE = 'int' or DATA_TYPE =

'smallint' Then 'integer'

      When DATA_TYPE = 'nvarchar' or DATA_TYPE =

'Char' or DATA_TYPE = 'varchar' Then 'string'

      When DATA_TYPE = 'datetime' Then 'date'

      When DATA_TYPE = 'bit' Then 'boolean'

      Else 'object'

     End

 FROM INFORMATION_SCHEMA.PARAMETERS

 WHERE  Specific_name = @routineName

 -- In

case of Add then the return is going to be integer. In case of Get or

GetSingle the return value is going to be

IDataReader

 If Left(@routineName,

3) = 'Add' OR Left(@routineName, 3) = 'Get'

 Begin

  If Left(@routineName, 3) = 'Add'

   SET @functionname = @functionname

+ ') as integer'

  Else

   SET @functionname = @functionname

+ ') as IDataReader'

 End

 Else

  -- In case of Update and Delete it is sub.

  SET @functionname = @functionname

+ ')'

 -- Print

the function

 Print @functionname

 FETCH NEXT FROM curRoutineName INTO @routineName

End

Close curRoutineName

Deallocate curRoutineName

References:

www.etekglobalinc.com

- Free Downloads of Stored Procedure Generator and DNN Middle Tier Function

Generator.

CodeSmith

- Automatically build sprocs from templates

using this free tool.