The T-SQL code in Listing 1 creates six 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 six stored procedures are grouped as three sets of two each. The first pair creates/deletes primary key constraints. The second pair creates/deletes foreign key constraints. The third pair creates/deletes foreign key indexes.
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.
These stored procedures accept five or six parameters, and all of them are optional. The first five parameters are the same for all the routines.
The first parameter (@DBUltra) determines whether T-SQL script is generated or the actions are carried out immediately. T-SQL script might be used to build a distribution script file. Immediate actions take place in the current database. A value of zero (0) causes T-SQL script to be generated and a value of one (1) causes actions to be carried out immediately.
The next four parameters work together to form a combination of table selection criteria using names.
The second and third 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 fourth and fifth 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 in order to work with the desired subset of tables. If the parameters are omitted, or if 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 purposes because performance is not a significant issue when referencing tables with very modest row counts in an administrative routine. If this kind of selection task were being 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.
The sp_CreatePrimaryKeys stored procedure creates primary key constraints for selected tables. The sixth parameter (@PCUltra) determines whether the supporting indexes for the primary key constraints are clustered. A value of zero (0) creates nonclustered primary keys and a value of one (1) creates clustered primary keys.
You can create primary key constraints for the entire database with this stored procedure call:
EXECUTE sp_CreatePrimaryKeys 1
The sp_CreateForeignKeys stored procedure creates foreign key constraints for selected tables. The primary key constraints must already exist. There is a local variable, @FKey, that's used as a template for recognizing foreign key columns. The asterisk in the value is dynamically replaced by a potential parent table name and the result is compared to column names.
You can create foreign key constraints for the entire database with this stored procedure call:
EXECUTE sp_CreateForeignKeys 1
The sp_CreateForeignKeyIndexes stored procedure creates foreign key indexes for selected tables. The foreign key constraints must already exist. The sixth parameter (@DBAdmin) allows a FILLFACTOR percentage to be specified for the indexes.
You can create foreign key indexes for the entire database with this stored procedure call:
EXECUTE sp_CreateForeignKeyIndexes 1
The sp_DeletePrimaryKeys stored procedure deletes primary key constraints for selected tables. The foreign key constraints must be deleted first.
The sp_DeleteForeignKeys stored procedure deletes foreign key constraints for selected tables.
The sp_DeleteForeignKeyIndexes stored procedure deletes foreign key indexes for selected tables.
These routines 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. It's especially important to specify a table name prefix (if a prefix is being used) in the sp_CreateForeignKeys stored procedure or the routine will expect to find the prefix in foreign key column names.
An example database is not really complete without some example data. The INSERT statements below add some simple data that's intended to make apparent the relationships between tables.
INSERT Region (RegionCode,RegionName,Representative) VALUES ('NW','NW USA' ,'NW Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('SW','SW USA' ,'SW Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('NE','NE USA' ,'NE Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('SE','SE USA' ,'SE Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('CA','Canada' ,'CA Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('EU','Europe' ,'EU Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('DU','Down Under','DU Sales')
INSERT Region (RegionCode,RegionName,Representative) VALUES ('AO','All Others','AO Sales')
INSERT Customer (RegionID,Name,Address,Phone) VALUES (1,'Customer X','Address X','Phone X')
INSERT Customer (RegionID,Name,Address,Phone) VALUES (4,'Customer Y','Address Y','Phone Y')
INSERT Customer (RegionID,Name,Address,Phone) VALUES (7,'Customer Z','Address Z','Phone Z')
INSERT Vendor (Name,Address,Phone) VALUES ('Vendor X','Address X','Phone X')
INSERT Vendor (Name,Address,Phone) VALUES ('Vendor Y','Address Y','Phone Y')
INSERT Vendor (Name,Address,Phone) VALUES ('Vendor Z','Address Z','Phone Z')
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (1,'Product X-1',2.00,4.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (1,'Product X-2',4.00,6.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (2,'Product Y-1',3.00,5.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (2,'Product Y-2',5.00,7.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (2,'Product Y-3',5.00,9.00)
INSERT Product (VendorID,Description,WholesaleCost,RetailPrice) VALUES (3,'Product Z-1',6.00,8.00)
INSERT Purchase (CustomerID,OrderNumber,OrderDate) VALUES (1,1001,'07/01/2005')
INSERT Purchase (CustomerID,OrderNumber,OrderDate) VALUES (2,1002,'07/02/2005')
INSERT Purchase (CustomerID,OrderNumber,OrderDate) VALUES (2,1003,'07/03/2005')
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (1,1,1,1)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (2,1,1,1)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (2,3,2,2)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (3,2,1,1)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (3,4,2,2)
INSERT PurchaseItem (PurchaseID,ProductID,Quantity,LineNumber) VALUES (3,5,3,3)
INSERT Shipment (Carrier,TrackingNumber,ShipDate) VALUES ('UPS' ,'1','07/01/2005')
INSERT Shipment (Carrier,TrackingNumber,ShipDate) VALUES ('UPS' ,'2','07/02/2005')
INSERT Shipment (Carrier,TrackingNumber,ShipDate) VALUES ('FedEx','1','07/03/2005')
INSERT Shipment (Carrier,TrackingNumber,ShipDate) VALUES ('FedEx','2','07/05/2005')
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (1,1)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (2,2)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (3,2)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (4,3)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (5,4)
INSERT PurchaseItemShipment (PurchaseItemID,ShipmentID) VALUES (6,4)
INSERT Payment (PurchaseID,Method,Amount) VALUES (1,'PayPal', 4.00)
INSERT Payment (PurchaseID,Method,Amount) VALUES (2,'PayPal',14.00)
INSERT Payment (PurchaseID,Method,Amount) VALUES (3,'Check' ,16.00)
INSERT Payment (PurchaseID,Method,Amount) VALUES (3,'Check' ,16.00)
INSERT Payment (PurchaseID,Method,Amount) VALUES (3,'Check' ,15.00)
I know, I know, natural data key proponents will point out that my use of assumed IDENTITY values in the VALUES clause is exactly the failing of surrogate keys. They are right in a sense. The INSERT statements above make some assumptions about parent rows because child rows are not connected to parent rows using natural data. However, customers do not place their orders using a batch of INSERT statements. Customers use an application, and an application does not have to make any assumptions about parent rows. An application can handle IDENTITY values dynamically, but my example data must be static.
Click for the stored procedures to create and delete constraints and indexes
Home: 
