Speed up Development on Backend and Middle Tier with SQL Scripts
Published: 23 Feb 2004
Unedited - Community Contributed
Ever wish you had a tool/robot that would allow you to generate your stored procedures and middle tier functions/classes for you and save you lot of time in coding? Most developers do get tired of coding with the same approach over and over. This article demonstrates how you can build your own SQL Procedures to simplify your repetitive work.
by Ameet Phadnis
Average Rating: 
Views (Total / Last 10 Days): 28519/ 89


          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, that’s 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 –


            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. Let’s 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 –




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 –



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



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 –




Combining Information_schema.Table_constraints and Information_schema. CONSTRAINT_COLUMN_USAGE, SQL will look like





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


            Information_schema.REFERENTIAL_CONSTRAINTS Ref on rkey.CONSTRAINT_NAME = Ref.Unique_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 –




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

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 +
     DATA_TYPE +
     Case When CHARACTER_MAXIMUM_LENGTH is not null Then
      '(' + Cast(CHARACTER_MAXIMUM_LENGTH as varchar(4)) + ')'
      Else ''
 @deleteStatement = @deleteStatement + Case When @deleteStatement = '' Then ''
     Else ' AND ' + Char(13) + Char(10)

-- 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'
 SET @procedurename = @procedurename + SubString(@table_Name, 4, Len(@table_Name))
 If Left(@table_Name, 4) = 'tlkp'
  SET @procedurename = @procedurename + SubString(@table_Name, 5, Len(@table_Name))
  -- In case none of the above standards are followed then just get the table name.
  SET @procedurename = @procedurename + @table_Name

--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
 -- 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)
 --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'

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)
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
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

 If @routineName is not null AND @routineName <> ''
  -- 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 + '('
   SET @functionname = 'Public MustOverride Sub ' + @routineName + '('
 -- 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'
 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'
  If Left(@routineName, 3) = 'Add'
   SET @functionname = @functionname + ') as integer'
   SET @functionname = @functionname + ') as IDataReader'
  -- In case of Update and Delete it is sub.
  SET @functionname = @functionname + ')'

 -- Print the function
 Print @functionname
 FETCH NEXT FROM curRoutineName INTO @routineName
Close curRoutineName
Deallocate curRoutineName



SQL Server Help provided by Microsoft.

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.

User Comments

Title: MetaData   
Name: Sanjay Modi
Date: 2007-04-10 2:25:25 PM
its realy helpful my best wishes to user

Sanjay Modi
Title: ing   
Name: Jorgen Pedersen
Date: 2005-09-05 12:10:14 PM
Hi. I like your article but I need to find the name of the columns constraint name for the DEFAULT value of the column. Can you help?
Yjanks in advance
Title: Thank you   
Name: Mab
Date: 2005-06-21 8:37:32 AM
Its really a very gud articles for the developers. Thanks for sharing such a nice article.


Community Advice: ASP | SQL | XML | Regular Expressions | Windows

©Copyright 1998-2019 ASPAlliance.com  |  Page Processed at 2019-11-20 7:47:56 PM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search