About Me

Colorado
Paul has 18 years experience with Microsoft SQL Server. He has worked in the roles of production DBA, database developer, database architect, applications developer, business intelligence and data warehouse developer, and instructor for students aspiring for MCDBA certification. He has performed numerous data migrations and supported large databases (3 Terabyte, 1+ billion rows) with high transactions. He is a member of PASS, blogs about lessons learned from a developer’s approach to SQL Server administration, and has been the president of the Boulder SQL Server Users’ Group for 11 years, from January 2009 to 2020.

Thursday, April 4, 2013

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




No comments:

Post a Comment