CreateInfraObjectType

This proc creates table infraObjectType which is is then JOINed with various queries where a list of object types are needed.  I find it much cleaner to put this into a table that can be referenced, rather than putting all the information into a verbose CASE statement.  The contents of this table come from BOL (book online).

This object is referenced by view vwDictCol which will be presented soon.

USE Admin

IF Object_ID('dbo.CreateInfraObjectType') Is Not Null

      DROP PROC dbo.CreateInfraObjectType

go

CREATE PROC dbo.CreateInfraObjectType

      @Debug bit = 0

AS

/*    DATE        AUTHOR            REMARKS

      4/4/13            PPaiva            Initial creation.

     

     

      DESCRIPTION

            Creates and populates table infraObjectType.

           

      USAGE

            CreateInfraObjectType 1

     

      DEBUG

            SELECT *

            FROM infraObjectType

*/

SET NOCOUNT ON

IF Object_ID('dbo.infraObjectType') Is Not Null

      DROP TABLE dbo.infraObjectType

CREATE TABLE dbo.infraObjectType (

      xType varchar(10) NOT NULL CONSTRAINT pk_infraObjectType PRIMARY KEY CLUSTERED,

      Type varchar(2) NOT NULL ,

      Description varchar(50) NOT NULL,

      DescShort varchar(50) NOT NULL

      )

INSERT INTO infraObjectType VALUES ('C', 'C', 'CHECK constraint', 'CheckCon')

INSERT INTO infraObjectType VALUES ('D', 'D', 'Default or DEFAULT constraint', 'DefaultCon')

INSERT INTO infraObjectType VALUES ('F', 'F', 'FOREIGN KEY constraint', 'FK')

INSERT INTO infraObjectType VALUES ('L', 'L', 'Log', 'Log')

INSERT INTO infraObjectType VALUES ('FN', 'FN', 'Scalar function', 'FunctionScalar')

INSERT INTO infraObjectType VALUES ('IF', 'IF', 'Inlined table-function', 'FunctionTable')

INSERT INTO infraObjectType VALUES ('P', 'P', 'Stored procedure', 'Proc')

INSERT INTO infraObjectType VALUES ('PK', 'K', 'PRIMARY KEY constraint', 'PK')

INSERT INTO infraObjectType VALUES ('(Not Used)', 'R', 'Rule', 'Rule')

INSERT INTO infraObjectType VALUES ('RF', 'FF', 'Replication filter stored procedure', 'RepFilterProc')

INSERT INTO infraObjectType VALUES ('S', 'S', 'System table', 'SysTable')

INSERT INTO infraObjectType VALUES ('TF', 'TF', 'Table function', 'FunctionTable')

INSERT INTO infraObjectType VALUES ('TR', 'TR', 'Trigger', 'Trigger')

INSERT INTO infraObjectType VALUES ('U', 'U', 'User table', 'Table')

INSERT INTO infraObjectType VALUES ('UQ', 'K', 'UNIQUE constraint', 'UniqCon')

INSERT INTO infraObjectType VALUES ('V', 'V', 'View', 'View')

INSERT INTO infraObjectType VALUES ('X', 'X', 'Extended stored procedure', 'ExProc')

IF @Debug = 1

      SELECT *

      FROM infraObjectType