Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Speed up Development on Backend and Middle Tier with SQL Scripts

By Ameet Phadnis,

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. 

Total article views: 8622 | Views in the last 30 days: 2
 
Related Articles
SCRIPT

Fix Column Check Constraint Naming

This stored procedure can be used for applying a custom column check constraint naming convention

FORUM

Alter all store procedure parameter size- INFORMATION_SCHEMA.PARAMETERS

INFORMATION_SCHEMA.PARAMETERS , Alter store procedure parameter size

FORUM

Stored Procedure Metadata

Query which columns returned by a stored procedure

FORUM

stored procedure---finding all tables that contain a specific column

stored procedure---finding all tables that contain a specific column

SCRIPT

Fix Default Constraint Naming

This stored procedure can be used for applying a custom default constraint naming

Tags
miscellaneous    
programming    
t-sql    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones