SPS Home    >   Dgreath    >   RDBMS    >   System Stored Procedures

SYSTEM STORED PROCEDURES

SQL Server provides a number of server-side stored procedures to simply database administration. The following procedures are executed from Query Analyzer.  To do so, launch Query Analyzer from the Windows Start menu and immediately connect to the SQL Server where the database resides. Enter the desired procedure as shown below:
User Datatypes, Addition of:
To avoid the possibility of data inconsistency, users can create standardized datatypes for things like zipcodes, phone numbers, social security numbers, and the like. To do so, use the stored procedure "sp_addtype" and specify the identity, datatype and length, and nullability (NULL or NOT NULL) as follows:

USE dbname
EXEC sp_addtype ZIPCODE, 'CHAR(5)', NULL
GO
 

Note: the single quotes around the datatype specification are required.

Once a datatype has been created, it can be used in the same way as the system datatypes.

User Datatypes, Deletion of:
User datatypes that are no longer needed can be deleted using the "sp_droptype" stored procedure as follows:

USE dbname
EXEC sp_droptype ZIPCODE
GO