Manage Learn to apply best practices and optimize your operations.

Stored procedures: Create and manage standard objects

This set of tools will quickly generate the necessary T-SQL code to create the objects needed for a proposed surrogate key architecture.

It would be extremely tedious to manually write the T-SQL code for each of the standard objects for each table in a database of any size. In order to save others from such boring repetition, I will now present a set of several tools to generate the standard objects. The tools can quickly generate the necessary T-SQL code to create the objects. The routines to generate the standard objects, and the generated objects themselves, take advantage of the consistency of the proposed surrogate key architecture.

The tool set consists of 12 stored procedures to create and manage the standard objects. Each standard object is created by a separate stored procedure, so that's nine of the 12 tools. There's a stored procedure to create all of the standard objects (or just a subset of them) with one call. There's a stored procedure to delete all of the standard objects with one call. Finally, there's a stored procedure to examine which standard objects already exist in the database.

The T-SQL code in Listing 1 creates 12 system stored procedures in the master database. I chose the master database for convenience, but the stored procedures could be created in user databases instead (remove the "sp_" prefixes from the names before creating the stored procedures in a user database). The listing also creates two additional system stored procedures in the master database, sp_DBAWorkMaster and sp_DBAWorkFormat. These two routines are used internally by the other 12 routines. The sp_DBAWorkMaster routine is a central location for defining the names of standard columns and the naming convention of standard objects. The sp_DBAWorkFormat routine is used to format data type declarations.

The 12 main stored procedures and their actions are listed below.

Name Action
sp_CreateTriggerCI creates trgGRCITableName
sp_CreateTriggerCU creates trgGRCUTableName
sp_CreateProcedureCI creates uspGRCITableName
sp_CreateProcedureCU creates uspGRCUTableName
sp_CreateProcedureCD creates uspGRCDTableName
sp_CreateProcedureCS creates uspGRCSTableName
sp_CreateProcedureKD creates uspGRKDTableName
sp_CreateProcedureKS creates uspGRKSTableName
sp_CreateFunctionKS creates udfGRKSTableName
sp_CreateAllObjects creates all standard objects
sp_DeleteAllObjects deletes all standard objects
sp_ReviewAllObjects checks for existing standard objects

NOTE: The naming of things within this T-SQL code is not very helpful for understanding how it works. Parameters, variables, tables and columns are named rather capriciously. I humbly beg your forgiveness for my programming idiosyncrasies! These stored procedures definitely reflect my weird style.

The stored procedure parameters are listed here for easy reference:

CREATE PROCEDURE dbo.sp_CreateTriggerCI
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @DBWhose varchar(2000) = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateTriggerCU
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @DBWhose varchar(2000) = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateProcedureCI
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateProcedureCU
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateProcedureCD
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateProcedureCS
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateProcedureKD
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateProcedureKS
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateFunctionKS
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_CreateAllObjects
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @DBWhose varchar(2000) = NULL,
    @PCAdmin varchar(20)   = NULL

CREATE PROCEDURE dbo.sp_DeleteAllObjects
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @DBUltra bit = 0,
    @PCUltra bit = 0

CREATE PROCEDURE dbo.sp_ReviewAllObjects
    @DBIntra varchar(8000) = NULL,
    @DBExtra varchar(8000) = NULL,
    @PCIntra varchar(100)  = NULL,
    @PCExtra varchar(100)  = NULL,
    @PCUltra bit = 0

The first four parameters for all 12 of the routines are the same and all of them are optional. They work together to form a combination of table selection criteria using names.

The first and second parameters (@DBIntra/@DBExtra) are lists of table names separated by pipes (vertical bars). The @DBIntra parameter specifies table names to be included. The @DBExtra parameter specifies table names to be excluded.

The third and fourth parameters (@PCIntra/@PCExtra) offer table selection based on pattern matching of names. The @PCIntra parameter includes tables by using a LIKE operator on the names. The @PCExtra parameter excludes tables by using a NOT LIKE operator.

The effects of these four parameters are combined with AND operators. Often only one of the parameters (or none) would be used for a given call, but it may be useful to provide @DBExtra and/or @PCExtra in combination with @PCIntra to work with the desired subset of tables. If the parameters are omitted or null values are provided, they are effectively ignored for selection purposes.

The method used to select tables in these routines is not necessarily the most efficient way of handling delimited strings. The CHARINDEX function was used for simplicity because performance is not a significant issue when referencing tables with modest row counts in an administrative routine. If this kind of selection task was done in a production routine that referenced tables with larger row counts, it would probably be better to parse the delimited string and use the result set for a join.

It's awkward to embed single quotes in a string literal with T-SQL. For convenience when specifying string parameters, caret symbols (^) are converted to single quotes in the @DBWhose parameter (see below for an example).

NOTE: In most cases in these routines, a parameter or variable of a particular name is used for the same basic purpose everywhere it appears. However, I use certain names as generic parameters or variables. For example, the parameters @DBIntra/@DBExtra and @PCIntra/@PCExtra are always used to select things by name, but the parameters @DBUltra, @PCUltra and @PCAdmin are not necessarily used the same way in each routine.

The fifth parameter (@DBWhose) for the first two routines (to create triggers) is optional and it's used to specify criteria under which the generated triggers will take action. If no value is provided for the parameter then the generated triggers will always take action. If a value is provided it's used within an IF statement to conditionally execute the trigger code. For example, if the value "@@ROWCOUNT = 1" is provided then the trigger will take action only when a single row is involved. As another example, if the value "@@ROWCOUNT > 1 OR APP_NAME() = ^MyApplication^" is provided then the trigger will take action when more than one row is involved or when an application named "MyApplication" is used.

The sixth parameter (@PCUltra) for the first two routines (to create triggers) is optional and used internally for efficiency when the routines are called by the sp_CreateAllObjects stored procedure.

The fifth parameter (@PCUltra) for the next seven routines (to create stored procedures and a function) is optional and it's used internally for efficiency when the routines are called by the sp_CreateAllObjects stored procedure.

The fifth parameter (@DBWhose) for the sp_CreateAllObjects routine is optional and it's simply passed through to the parameter of the same name for the two routines that create triggers.

The sixth parameter (@PCAdmin) for the sp_CreateAllObjects routine is optional and defines which standard objects should be generated. It's simply a string of nine characters with each position representing a standard object in the order listed above. If a position contains "N", "0" or space then the corresponding standard object is omitted. If a position contains any other character then the corresponding standard object is generated.

The fifth parameter (@DBUltra) for the sp_DeleteAllObjects routine is optional and determines whether a T-SQL script is generated or the actions are carried out immediately. A value of zero (0) causes a T-SQL script to be generated and a value of one (1) carries out actions immediately.

The sixth parameter (@PCUltra) for the sp_DeleteAllObjects routine is optional and targets some additional objects that are outside the scope of this article. Look for more information in an upcoming article about a robust data auditing system.

The fifth parameter (@PCUltra) for the sp_ReviewAllObjects routine is optional and determines whether table name prefixes (if used and defined) are included in the output. A value of zero (0) means they will be omitted and a value of one (1) means they will be included.

These stored procedures contain a local variable, @TPre, that can be used to specify a table name prefix. If a table name prefix is identified with this variable, then the prefix can be omitted from table names provided as parameters.

The examples below should be executed in the context of the example database presented in my previous article. However, that database has no standard columns in any table. The routines to generate triggers sense the lack of standard columns and do not generate any code. Use the CREATE TABLE statement below to create a table with all six standard columns. Then observe how the generated routines are automatically adjusted to work with the standard columns in the Standard table. Notice that the CreateDate and ModifyDate columns in the Standard table are defined as accepting NULL in anticipation of being populated by a generated trigger, but the generated routines do not assume that the triggers are present and they provide default values for both columns.

  CREATE TABLE dbo.Standard
     (StandardID                              int IDENTITY(1,1),
      CreateUser                      varchar(40) NOT NULL,
      CreateDate                    smalldatetime     NULL,
      ModifyUser                      varchar(40) NOT NULL,
      ModifyDate                    smalldatetime     NULL,
      DetectWork                              int NOT NULL,
      RecordMask                          tinyint NOT NULL,
      ColumnX                                 int NOT NULL,
      ColumnY                                 int NOT NULL,
      ColumnZ                                 int NOT NULL)

This example generates T-SQL code to create two triggers and six stored procedures for each table. The T-SQL can be copied from the results, pasted into a new Query Analyzer connection window, and executed to create the objects in the database.

EXECUTE sp_CreateAllObjects NULL,NULL,NULL,NULL,'@@ROWCOUNT = 1','YYYYYYYYN'

This example generates T-SQL code to delete all generated objects.

EXECUTE sp_DeleteAllObjects NULL,NULL,NULL,NULL,0

This example returns a result set showing which generated objects exist in the database.

EXECUTE sp_ReviewAllObjects NULL,NULL,NULL,NULL,0


Columns and objects to support a surrogate key architecture

 Home: Introduction
 Part 1: Overview of proposed surrogate key architecture
 Part 2: Dealing with concurrent access
 Part 3: Standard routines for each table
 Part 4: Stored procedures: Create and manage standard objects

ABOUT THE AUTHOR:   
Brian Walker
Brian Walker is a senior database architect in an IS department that uses SQL Server 2000 and the .NET Framework. He has more than 25 years of experience in the IT industry with the last several years focused on databases and SQL Server. Walker is a software developer, database developer, database administrator and database consultant. He develops utility software as a hobby, including a large collection of SQL Server utilities.
Copyright 2006 TechTarget
This was last published in February 2006

Dig Deeper on SQL Server Stored Procedures

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close