------------------------------------------------------------------
--                                                              --
--  File:   DB_Tables.SQL                                       --
--          Microsoft TPC-E Benchmark Kit Ver. 1.12.0-1025      --
--          Copyright Microsoft  2010, 2011, 2012               --
--                                                              --
------------------------------------------------------------------
USE tpce
GO

-- set the MAX Degree of Parallelism to 0 for this test
sp_configure    'max degree of parallelism',0
GO

-- reconfigure the server so the MAXDOP will take effect
RECONFIGURE WITH OVERRIDE
GO

SET NOCOUNT ON
GO

PRINT   ''
PRINT   ''
PRINT   '------------------------------------'
PRINT   '|  TPC-E Database Tables - Begin   |'
PRINT   '------------------------------------'
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO

DECLARE @obj_id             int,
        @table_name         varchar(50),
        @obj_type           char(2),
        @numtypes           nvarchar(80),
        @no                 varchar(35),
        @yes                varchar(35),
        @none               varchar(35),
        @name               varchar(20),
        @user_type_id       varchar(10),
        @max_length         int,
        @precision          int,
        @scale              int,
        @is_nullable        varchar(3),
        @index_id           smallint,
        @group_id           int,
        @index_name         varchar(50),
        @index_description  varchar(50),
        @group_name         varchar(50),
        @keys               varchar(100),
        @is_unique          varchar(3),
        @is_primary_key     varchar(3),
        @is_unique_key      varchar(3),
        @cnst_id            int,
        @cnst_type          varchar(50),
        @cnst_des           varchar(200),
        @cnst_name          varchar(50),
        @cnst_seq           tinyint,
        @file_group         varchar(50)

CREATE TABLE #TPCE_TABLES   (   Table_Name          varchar(30),
                                Table_Object_Id     int,
                                Column_Name         varchar(20),
                                Column_Type         varchar(10),
                                Column_Length       int,
                                Column_Precision    int,
                                Column_Scale        int,
                                Column_Nullable     varchar(3)
                            )

SELECT  @numtypes   = N'tinyint,smallint,decimal,int,real,money,float,numeric,smallmoney',
        @no         = 'No',
        @yes        = 'Yes',
        @none       = 'None'

-- create a temporary table with the TPC-E table names
-- these table names are in the specification order, not alphabetical
CREATE TABLE #TPCE_TABLE_LIST   (   table_name  varchar(50) )

-- insert the Customer Tables
INSERT INTO #TPCE_TABLE_LIST VALUES('ACCOUNT_PERMISSION')
INSERT INTO #TPCE_TABLE_LIST VALUES('CUSTOMER')
INSERT INTO #TPCE_TABLE_LIST VALUES('CUSTOMER_ACCOUNT')
INSERT INTO #TPCE_TABLE_LIST VALUES('CUSTOMER_TAXRATE')
INSERT INTO #TPCE_TABLE_LIST VALUES('HOLDING')
INSERT INTO #TPCE_TABLE_LIST VALUES('HOLDING_HISTORY')
INSERT INTO #TPCE_TABLE_LIST VALUES('HOLDING_SUMMARY')
INSERT INTO #TPCE_TABLE_LIST VALUES('WATCH_ITEM')
INSERT INTO #TPCE_TABLE_LIST VALUES('WATCH_LIST')
-- insert the Broker Tables
INSERT INTO #TPCE_TABLE_LIST VALUES('BROKER')
INSERT INTO #TPCE_TABLE_LIST VALUES('CASH_TRANSACTION')
INSERT INTO #TPCE_TABLE_LIST VALUES('CHARGE')
INSERT INTO #TPCE_TABLE_LIST VALUES('COMMISSION_RATE')
INSERT INTO #TPCE_TABLE_LIST VALUES('SETTLEMENT')
INSERT INTO #TPCE_TABLE_LIST VALUES('TRADE')
INSERT INTO #TPCE_TABLE_LIST VALUES('TRADE_HISTORY')
INSERT INTO #TPCE_TABLE_LIST VALUES('TRADE_REQUEST')
INSERT INTO #TPCE_TABLE_LIST VALUES('TRADE_TYPE')
-- insert the Market Tables
INSERT INTO #TPCE_TABLE_LIST VALUES('COMPANY')
INSERT INTO #TPCE_TABLE_LIST VALUES('COMPANY_COMPETITOR')
INSERT INTO #TPCE_TABLE_LIST VALUES('DAILY_MARKET')
INSERT INTO #TPCE_TABLE_LIST VALUES('EXCHANGE')
INSERT INTO #TPCE_TABLE_LIST VALUES('FINANCIAL')
INSERT INTO #TPCE_TABLE_LIST VALUES('INDUSTRY')
INSERT INTO #TPCE_TABLE_LIST VALUES('LAST_TRADE')
INSERT INTO #TPCE_TABLE_LIST VALUES('NEWS_ITEM')
INSERT INTO #TPCE_TABLE_LIST VALUES('NEWS_XREF')
INSERT INTO #TPCE_TABLE_LIST VALUES('SECTOR')
INSERT INTO #TPCE_TABLE_LIST VALUES('SECURITY')
-- insert the Dimension Tables
INSERT INTO #TPCE_TABLE_LIST VALUES('ADDRESS')
INSERT INTO #TPCE_TABLE_LIST VALUES('STATUS_TYPE')
INSERT INTO #TPCE_TABLE_LIST VALUES('TAXRATE')
INSERT INTO #TPCE_TABLE_LIST VALUES('ZIP_CODE')


DECLARE tpce_tables_cursor CURSOR FOR   SELECT  table_name
                                        FROM    #TPCE_TABLE_LIST
                                        
OPEN tpce_tables_cursor

FETCH FROM tpce_tables_cursor INTO @table_name

WHILE (@@FETCH_STATUS >= 0)
BEGIN
    SELECT  @obj_id = object_id,
            @obj_type   = TYPE  FROM    sys.all_objects
                                WHERE   object_id = object_id(@table_name)

DECLARE tables_cursor CURSOR FOR    SELECT  name,
                                            user_type_id,
                                            max_length,
                                            precision,
                                            scale,
                                            is_nullable
                                    FROM    sys.all_columns
                                    WHERE   object_id   = @obj_id

OPEN tables_cursor

FETCH FROM tables_cursor INTO   @name,
                                @user_type_id,
                                @max_length,
                                @precision,
                                @scale,
                                @is_nullable

WHILE (@@FETCH_STATUS = 0)
BEGIN

    SELECT  @user_type_id   = type_name(@user_type_id),
            @max_length     = convert(int, @max_length),
            @is_nullable    = case when @is_nullable = 0 then @no else @yes end

    INSERT INTO #TPCE_TABLES VALUES(@table_name, @obj_id, @name, @user_type_id, @max_length, @precision, @scale, @is_nullable)

    FETCH FROM tables_cursor INTO   @name,
                                    @user_type_id,
                                    @max_length,
                                    @precision,
                                    @scale,
                                    @is_nullable
END
CLOSE       tables_cursor
DEALLOCATE  tables_cursor

SELECT  @table_name AS 'Table Name'

SELECT  Column_Name As 'Column Name',
        Column_Type As 'Data Type',
        Column_Length As 'Length',
        Column_Precision As 'Precision',
        Column_Scale As 'Scale',
        Column_Nullable AS 'Nullable'
FROM    #TPCE_TABLES
WHERE   Table_Name  = @table_name

SELECT  @obj_id     = Table_Object_Id
FROM    #TPCE_TABLES
WHERE   Table_Name  = @table_name

--FileGroup Data
SELECT  @file_group = d.name
FROM    sys.data_spaces As d
WHERE   d.data_space_id = ( SELECT  i.data_space_id
                            FROM    sys.indexes As i
                            WHERE   i.object_id = @obj_id AND
                                    i.index_id < 2)

SELECT  @file_group As 'Table Located on File Group'

-- get the indexes defined on the object
DECLARE index_cursor CURSOR LOCAL STATIC FOR    SELECT  I.index_id,
                                                        I.data_space_id,
                                                        I.name,
                                                        I.is_unique,
                                                        I.is_primary_key,
                                                        I.is_unique_constraint
                                                FROM    sys.indexes I JOIN
                                                        sys.stats S ON I.object_id = S.object_id AND
                                                        I.index_id = S.stats_id
                                                WHERE   I.object_id = @obj_id

OPEN    index_cursor

FETCH FROM index_cursor INTO    @index_id,
                                @group_id,
                                @index_name,
                                @is_unique,
                                @is_primary_key,
                                @is_unique_key

-- if there is not an index, then quit this loop
IF (@@FETCH_STATUS < 0)
BEGIN
    DEALLOCATE  index_cursor
    RAISERROR(15472, -1, -1, @table_name)

END

-- create temp table
CREATE TABLE #INDEX_TABLE   (   Index_Name          varchar(50),
                                Index_ID            int,
                                Index_Description   varchar(50),
                        		Is_Unique           varchar(3),
                                Is_Primary_Key      varchar(3),
		                        Is_Unique_Key       varchar(3),
                        		Group_Name          varchar(50),
		                        Index_Keys          varchar(50)
                            )

WHILE (@@FETCH_STATUS >= 0)
BEGIN
    -- First we'll figure out what the keys are.
	DECLARE     @i          int,
                @thiskey    nvarchar(131)

    SELECT  @keys   = INDEX_COL(@table_name, @index_id, 1),
            @i      = 2

    IF (INDEXKEY_PROPERTY(@obj_id, @index_id, 1, 'isdescending') = 1)
        SELECT  @keys   = @keys + '(-)'

    SELECT  @thiskey    = INDEX_COL(@table_name, @index_id, @i)
		
    IF ((@thiskey IS NOT NULL) AND (INDEXKEY_PROPERTY(@obj_id, @index_id, @i, 'isdescending') = 1))
        SELECT  @thiskey    = @thiskey + '(-)'

    WHILE (@thiskey IS NOT NULL )
    BEGIN
        SELECT  @keys   = @keys + ', ' + @thiskey,
                @i      = @i + 1

        SELECT  @thiskey    = INDEX_COL(@table_name, @index_id, @i)

        IF ((@thiskey IS NOT NULL) AND (INDEXKEY_PROPERTY(@obj_id, @index_id, @i, 'isdescending') = 1))
            SELECT  @thiskey    = @thiskey + '(-)'
    END

    SELECT  @group_name = NULL

    SELECT  @group_name = name FROM sys.data_spaces WHERE data_space_id = @group_id
    
    SELECT  @index_description  = convert(varchar(210),
				                    case when @index_id = 1 then 'clustered' else 'nonclustered' end
                                  + case when @is_unique <> 0 then ', unique' else '' end
                                  + case when @is_primary_key <>0 then ', primary key' else '' end
                                  + case when @is_unique_key <>0 then ', unique key' else '' end
                                  + ' located on ' + @group_name)

    -- set the values for display
    SET     @is_unique      = CASE WHEN @is_unique = 0 THEN @no ELSE @yes END
    SET     @is_primary_key = CASE WHEN @is_primary_key = 0 THEN @no ELSE @yes END
    SET     @is_unique_key  = CASE WHEN @is_unique_key = 0 THEN @no ELSE @yes END


    -- INSERT ROW FOR INDEX
    INSERT INTO #INDEX_TABLE values	(@index_name, @index_id, @index_description, @is_unique, 
			                         @is_primary_key, @is_unique_key, @group_name, @keys)

    -- get the next index
    FETCH FROM index_cursor INTO    @index_id,
                                    @group_id,
                                    @index_name,
                                    @is_unique,
                                    @is_primary_key,
                                    @is_unique_key
END

SELECT  Index_Name As 'Index Name',
        Index_Description As 'Index Description',
        Index_Keys As 'Index Keys'
FROM    #INDEX_TABLE

DROP TABLE #INDEX_TABLE
CLOSE       index_cursor
DEALLOCATE  index_cursor

-- Create temp table for the constraints
CREATE TABLE #CONSTRAINT_TABLE  (   cnst_seq        tinyint,
                                    cnst_id			int			    NOT NULL,
                                    cnst_type       varchar(30)    NOT NULL,
                                    cnst_desc       varchar(65)    NULL,
		                            cnst_name		varchar(50)     NOT NULL,
                                    cnst_keys       varchar(50)	NULL
                                )

-- STATIC CURSOR OVER THE TABLE'S CONSTRAINTS
DECLARE constraint_cursor CURSOR LOCAL STATIC FOR   SELECT  object_id,
                                                            type,
                                                            name
                                                    FROM    sys.objects
                                                    WHERE   parent_object_id    = @obj_id
                                              FOR   READ ONLY

OPEN    constraint_cursor

FETCH FROM constraint_cursor INTO   @cnst_id,
                                    @cnst_type,
                                    @cnst_name

SET     @cnst_seq   = 0

WHILE (@@FETCH_STATUS >= 0)
BEGIN
    IF (@cnst_type = 'C')
    BEGIN
        -- check constraint
        SET     @i          = 1
        SET     @cnst_des   = NULL
        SELECT  @cnst_des   = TEXT FROM syscomments WHERE id = @cnst_id AND colid = @i

        SELECT  @cnst_type = CASE WHEN parent_column_id <> 0
	            				THEN 'CHECK on column ' + COL_NAME(@obj_id, parent_column_id)
				            	ELSE 'CHECK Table Level ' END
        FROM    sys.check_constraints
        WHERE   object_id = @cnst_id

        INSERT INTO #CONSTRAINT_TABLE VALUES (  @cnst_seq,
                                                @cnst_id,
                                                @cnst_type,
                                                @cnst_des,
                                                @cnst_name,
                                                @keys)
                    
        

	END
    IF  (@cnst_type = 'F ')
    BEGIN
        -- obtain the two table ids
        DECLARE @fkey_id        int,
                @rkey_id        int,
                @fkey_col       smallint,
                @rkey_col       smallint
        
        SELECT  @fkey_id = parent_object_id,
                @rkey_id = referenced_object_id
        FROM    sys.foreign_keys
        WHERE   object_id = @cnst_id

        DECLARE fkey_cursor CURSOR LOCAL FOR    SELECT  parent_column_id,
                                                        referenced_column_id
                                                FROM    sys.foreign_key_columns
                                                WHERE   constraint_object_id    = @cnst_id

        OPEN fkey_cursor

        FETCH FROM fkey_cursor INTO @fkey_col,
                                    @rkey_col

        SELECT  @keys       = COL_NAME(@fkey_id, @fkey_col),
                @cnst_des   = COL_NAME(@rkey_id, @rkey_col)

        FETCH FROM fkey_cursor INTO @fkey_col,
                                    @rkey_col

        WHILE (@@FETCH_STATUS >= 0)
        BEGIN
            SELECT  @keys       = @keys + ', ' + COL_NAME(@fkey_id, @fkey_col),
                    @cnst_des   = @cnst_des + ', ' + COL_NAME(@rkey_id, @rkey_col)

            FETCH FROM fkey_cursor INTO @fkey_col,
                                        @rkey_col
		END

        CLOSE       fkey_cursor
		DEALLOCATE fkey_cursor

        -- ADD ROWS FOR BOTH SIDES OF FOREIGN KEY
        INSERT INTO #CONSTRAINT_TABLE VALUES(   @cnst_seq,
                                                @cnst_id,
                                                'FOREIGN KEY',
                                                @keys,
                                                @cnst_name,
                                                ' ' )
        SELECT  @cnst_seq   = @cnst_seq + 1

        INSERT INTO #CONSTRAINT_TABLE VALUES(   @cnst_seq,
                                                ' ',
                                                ' ',
                                                'REFERENCES ' + DB_NAME()
                                                + '.' + RTRIM(SCHEMA_NAME(OBJECTPROPERTY(@rkey_id,'schemaid')))
                                                + '.' + OBJECT_NAME(@rkey_id) + ' (' + @cnst_des + ')',
                                                ' ',
                                                ' ' )

		END

    SELECT  @cnst_seq   = @cnst_seq + 1

    FETCH FROM constraint_cursor INTO   @cnst_id,
                                        @cnst_type,
                                        @cnst_name

END

SELECT  cnst_name As 'Constraint Name',
        cnst_type As 'Constraint Type',
        cnst_desc As 'Constraint Keys'
FROM    #CONSTRAINT_TABLE
ORDER   BY cnst_seq

DROP TABLE #CONSTRAINT_TABLE

CLOSE       constraint_cursor
DEALLOCATE  constraint_cursor

FETCH FROM tpce_tables_cursor INTO @table_name

PRINT   ' '
PRINT   '=============================================================================================================================================================='
PRINT   '=============================================================================================================================================================='
PRINT   ' '


END

CLOSE       tpce_tables_cursor
DEALLOCATE  tpce_tables_cursor

DROP TABLE #TPCE_TABLE_LIST
DROP TABLE #TPCE_TABLES
GO

-- set the MAX Degree of Parallelism to 1 for this runtime
sp_configure    'max degree of parallelism',1
GO

-- reconfigure the server so the MAXDOP will take effect
RECONFIGURE WITH OVERRIDE
GO

PRINT   ''
PRINT   ''
PRINT   '------------------------------------'
PRINT   '|  TPC-E Database Tables - End     |'
PRINT   '------------------------------------'
PRINT   ' '
SELECT  CONVERT(CHAR(30), GETDATE(), 21)
GO
