/******************************************************************************* A/B table conversion simple case application will not need to access data that has rolled over ad hoc user or aggregation operation may need access to rolled over data *******************************************************************************/ /* Create database used for all local partitioning examples -- drop database LocalPartitionDemo */ if db_id('LocalPartitionDemo') is null create database LocalPartitionDemo go declare @filename varchar(260) select @filename = replace(filename,'LocalPartitionDemo.mdf','LocalPartitionDemo_data.ndf') from master.dbo.sysdatabases where name = 'LocalPartitionDemo' use LocalPartitionDemo if filegroup_id('DATA') is null alter database LocalPartitionDemo add filegroup [DATA] if file_id('LocalPartitionDemo_data') is null exec('alter database LocalPartitionDemo ' + 'add file (name = ''LocalPartitionDemo_data'', ' + 'filename = ''' + @filename + ''') to FILEGROUP [DATA]') if filegroupproperty('DATA', 'IsDefault') <> 1 alter database LocalPartitionDemo modify FiLEGROUP [DATA] default select @filename = replace(@filename,'LocalPartitionDemo_data.ndf','LocalPartitionDemo_index.ndf') if filegroup_id('INDEXES') is null alter database LocalPartitionDemo add filegroup [INDEXES] if file_id('LocalPartitionDemo_index') is null exec('alter database LocalPartitionDemo ' + 'add file (name = ''LocalPartitionDemo_index'', ' + 'filename = ''' + @filename + ''') to FILEGROUP [INDEXES]') if is_member('ApplicationUser') is null exec sp_addrole [ApplicationUser] if is_member('DataAnalyst') is null exec sp_addrole [DataAnalyst] go use LocalPartitionDemo if object_id('LocalPartitionDemo.dbo.SaturdayNightLive','U') is null and object_id('LocalPartitionDemo.dbo.SaturdayNightLive','V') is null begin CREATE TABLE [dbo].[SaturdayNightLive] ( [LogPKey] [int] NOT NULL , [Item] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [status] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [type] [smallint] NULL , [CreationDate] [datetime] NULL , [CompletionDate] [datetime] NOT NULL CONSTRAINT [DF_SaturdayNightLive__CompletionDate] DEFAULT (getdate()) , CONSTRAINT [PKN_SaturdayNightLive__LogPKey] PRIMARY KEY NONCLUSTERED ( [LogPKey] ) ON [INDEXES] ) ON [DATA] CREATE CLUSTERED INDEX [IXC_SaturdayNightLive__CompletionDate] ON [dbo].[SaturdayNightLive]([CompletionDate]) ON [DATA] end go -- create A and B versions of the table with matching constraints use LocalPartitionDemo if object_id('dbo.SaturdayNightLiveConversion','P') is not null drop procedure [dbo].[SaturdayNightLiveConversion] go create procedure [dbo].[SaturdayNightLiveConversion] as if (object_id('dbo.SaturdayNightLive','U') is not null) and (object_id('dbo.SaturdayNightLive_A','U') is null) and (object_id('dbo.SaturdayNightLive_B','U') is null) begin -- rename the existing table to be the "A" version exec sp_rename 'dbo.SaturdayNightLive' ,'SaturdayNightLive_A' ,'OBJECT' exec sp_rename 'PKN_SaturdayNightLive__LogPKey' ,'PKN_SaturdayNightLive_A__LogPKey' ,'OBJECT' exec sp_rename 'DF_SaturdayNightLive__CompletionDate' ,'DF_SaturdayNightLive_A__CompletionDate' ,'OBJECT' exec sp_rename 'dbo.SaturdayNightLive_A.IXC_SaturdayNightLive__CompletionDate' , 'IXC_SaturdayNightLive_A__CompletionDate' ,'INDEX' -- remove permissions granted on the table (will grant to view below) REVOKE SELECT ON [dbo].[SaturdayNightLive_A] TO [ApplicationUser] -- now create a "B" version CREATE TABLE [dbo].[SaturdayNightLive_B] ( [LogPKey] [int] NOT NULL , [Item] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [status] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [type] [smallint] NULL , [CreationDate] [datetime] NULL , [CompletionDate] [datetime] NOT NULL CONSTRAINT [DF_SaturdayNightLive_B__CompletionDate] DEFAULT (getdate()) , CONSTRAINT [PKN_SaturdayNightLive_B__LogPKey] PRIMARY KEY NONCLUSTERED ( [LogPKey] ) ON [INDEXES] ) ON [DATA] CREATE CLUSTERED INDEX [IXC_SaturdayNightLive_B__CompletionDate] ON [dbo].[SaturdayNightLive_B]([CompletionDate]) ON [DATA] -- create a view named the sname as the table had been named exec ('create view dbo.SaturdayNightLive ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from LocalPartitionDemo.dbo.SaturdayNightLive_A') -- grant permissions that table had to the view GRANT SELECT ON [dbo].[SaturdayNightLive] TO [ApplicationUser] GRANT SELECT ON [dbo].[SaturdayNightLive] TO [DataAnalyst] -- optionally create a view to abstract the table not currently in use -- only needed if application code must reference the data in this table exec('create view dbo.SaturdayNightLive_old ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from LocalPartitionDemo.dbo.SaturdayNightLive_B') -- grant permissions to this view as necessary GRANT SELECT ON [dbo].[SaturdayNightLive_old] TO [ApplicationUser] GRANT SELECT ON [dbo].[SaturdayNightLive_old] TO [DataAnalyst] -- optionally create a view of the union of the "in use" and the "not in use" tables -- needed only if data analyst needs to read the data in both tables -- union all - there should be no duplicates exec('create view dbo.SaturdayNightLive_all ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from dbo.SaturdayNightLive_A ' + 'union all ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from dbo.SaturdayNightLive_B') -- grant permissions to this view as necessary GRANT SELECT ON [dbo].[SaturdayNightLive_all] TO [DataAnalyst] end else raiserror('Unable to Conduct Conversion',16,1) go if object_id('dbo.SaturdayNightLiveRollover','P') is not null drop procedure [dbo].[SaturdayNightLiveRollover] go create procedure [dbo].[SaturdayNightLiveRollover] as declare @CurrentTable varchar(128) , @OtherTable varchar(128) , @SQLStr nvarchar(4000) , @rc int , @ec int create table #junk (CompletionDate datetime) set nocount on -- identify which table is currently in use select @CurrentTable = table_schema + '.' + table_name from information_schema.view_table_usage where view_name = 'SaturdayNightLive' and view_schema = 'dbo' -- identify the "not in use" table -- if a view is not defined on the "not in use" table -- use deductive LocalPartitionDemo to determine name i.e.: -- if @CurrentTable = 'SaturdayNightLive_A' -- then @OtherTable = 'SaturdayNightLive_B' -- else @OtherTable = 'SaturdayNightLive_A' select @OtherTable = table_schema + '.' + table_name from information_schema.view_table_usage where view_name = 'SaturdayNightLive_old' and view_schema = 'dbo' /* clear the "not in use" table and make it the underlying table for the view. if a view is defined for the "not in use" table makes its underlying table the current "in use" table if rows need to be carried over the can be transfered from the current "in use table" to the table about to take over as the the "in use" table. */ set @SQLStr = 'select top 1 CompletionDate from ' + @CurrentTable + ' with(holdlock,tablock) ' + 'truncate table ' + @OtherTable + ' ' + 'exec(''alter view dbo.SaturdayNightLive ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from ' + @OtherTable + ''') ' + 'exec(''alter view dbo.SaturdayNightLive_old ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from ' + @CurrentTable + ''') ' -- start a transaction on place a shared lock on the "in use" table to block changes (inserts) during rollover begin tran insert #junk exec @rc = sp_executesql @SQLStr set @ec = @@error if @rc <> 0 or @ec <> 0 goto ErrorHandler else commit tran return ErrorHandler: -- errors raised will still be returned to the caller and will cause the -- proc to return someting other than 0 the goal needs to be to handle -- all problems before they cause a "fatal to the batch" error to be raised select 'Rollover Failed. @rc = ' + cast(@rc as varchar(10)) + '; @ec = ' + cast(@ec as varchar(10)) if @@trancount > 0 while @@trancount > 0 begin rollback tran print 'Transaction Rolled Back' end go /* always have a tested rollback script */ use LocalPartitionDemo if object_id('dbo.SaturdayNightLiveRollback','P') is not null drop procedure [dbo].[SaturdayNightLiveRollback] go create procedure [dbo].[SaturdayNightLiveRollback] as declare @PrimaryTable varchar(128) , @PKey varchar(128) , @Dft varchar(128) , @IXN varchar(128) , @OtherTable varchar(128) -- create A and B versions of the table with matching constraints if (object_id('dbo.SaturdayNightLive','V') is not null) and (object_id('dbo.SaturdayNightLive_A','U') is not null) and (object_id('dbo.SaturdayNightLive_B','U') is not null) begin -- find out which table currently underlies the main view select @PrimaryTable = table_name from information_schema.view_table_usage where view_name = 'SaturdayNightLive' and view_schema = 'dbo' -- may have to do something else if your naming standard doesn't support this easy swap out set @PKey = replace('PKN_SaturdayNightLive_A__LogPKey','SaturdayNightLive_A',@PrimaryTable) set @Dft = replace('DF_SaturdayNightLive_A__CompletionDate','SaturdayNightLive_A',@PrimaryTable) set @IXN = 'dbo.SaturdayNightLive.' + replace('IXC_SaturdayNightLive_A__CompletionDate' ,'SaturdayNightLive_A',@PrimaryTable) -- identify the "not in use" table and drop the view(s) -- if a view is not defined on the "not in use" table use deductive LocalPartitionDemo to determine name i.e.: -- if @CurrentTable = 'SaturdayNightLive_A' -- then @OtherTable = 'SaturdayNightLive_B' -- else @OtherTable = 'SaturdayNightLive_A' select @OtherTable = table_name from information_schema.view_table_usage where view_name = 'SaturdayNightLive_old' and view_schema = 'dbo' drop view [dbo].[SaturdayNightLive] drop view [dbo].[SaturdayNightLive_old] drop view [dbo].[SaturdayNightLive_all] -- rename the Primary table to become the only table exec sp_rename @PrimaryTable,'SaturdayNightLive','OBJECT' exec sp_rename @PKey,'PKN_SaturdayNightLive__LogPKey','OBJECT' exec sp_rename @Dft,'DF_SaturdayNightLive__CompletionDate','OBJECT' exec sp_rename @IXN,'IXC_SaturdayNightLive__CompletionDate','INDEX' -- get all remaining data back into the table exec ('insert dbo.SaturdayNightLive ' + 'select * from dbo.' + @OtherTable) -- now drop "B" version exec ('drop table dbo.' + @OtherTable) -- restore permissions back to the table GRANT SELECT ON [dbo].[SaturdayNightLive] TO [ApplicationUser] end else raiserror('Unable to Rollback',16,1) go -- SaturdayNightLive test script use LocalPartitionDemo set nocount on -- convert to AB tables exec LocalPartitionDemo.dbo.SaturdayNightLiveConversion print '*****After Conversion*****' print 'SaturdayNightLive' exec sp_helptext 'dbo.SaturdayNightLive' select * from LocalPartitionDemo.dbo.SaturdayNightLive print 'SaturdayNightLive_old (optional)' exec sp_helptext 'dbo.SaturdayNightLive_old' select * from LocalPartitionDemo.dbo.SaturdayNightLive_old print 'SaturdayNightLive_all (optional)' exec sp_helptext 'dbo.SaturdayNightLive_all' select * from LocalPartitionDemo.dbo.SaturdayNightLive_all print '*****Set test data*****' -- clean up and then add a little SaturdayNightLive data -- notice that data can be maintained via either of these views delete dbo.SaturdayNightLive delete dbo.SaturdayNightLive_old insert dbo.SaturdayNightLive (LogPKey, Item, status, Type, CreationDate) values (1,'current table',1,1,getdate()-1) insert dbo.SaturdayNightLive (LogPKey, Item, status, Type, CreationDate) values (2,'current table',2,2,getdate()-1) insert dbo.SaturdayNightLive_old (LogPKey, Item, status, Type, CreationDate) values (3,'other table',2,3,getdate()) insert dbo.SaturdayNightLive_old (LogPKey, Item, status, Type, CreationDate) values (4,'other table',2,4,getdate()) print '*****Before Rollover*****' print 'SaturdayNightLive' exec sp_helptext 'dbo.SaturdayNightLive' select * from LocalPartitionDemo.dbo.SaturdayNightLive print 'SaturdayNightLive_old (optional)' exec sp_helptext 'dbo.SaturdayNightLive_old' select * from LocalPartitionDemo.dbo.SaturdayNightLive_old print 'SaturdayNightLive_all (optional)' exec sp_helptext 'dbo.SaturdayNightLive_all' select * from LocalPartitionDemo.dbo.SaturdayNightLive_all exec LocalPartitionDemo.dbo.SaturdayNightLiveRollover print '*****After Rollover*****' print 'SaturdayNightLive' exec sp_helptext 'dbo.SaturdayNightLive' select * from LocalPartitionDemo.dbo.SaturdayNightLive print 'SaturdayNightLive_old (optional)' exec sp_helptext 'dbo.SaturdayNightLive_old' select * from LocalPartitionDemo.dbo.SaturdayNightLive_old print 'SaturdayNightLive_all (optional)' exec sp_helptext 'dbo.SaturdayNightLive_all' select * from LocalPartitionDemo.dbo.SaturdayNightLive_all insert dbo.SaturdayNightLive (LogPKey, Item, status, Type, CreationDate) values (5,'current table',5,5,getdate()-1) insert dbo.SaturdayNightLive (LogPKey, Item, status, Type, CreationDate) values (6,'current table',6,6,getdate()-1) print '*****New rows After Rollover*****' print 'SaturdayNightLive' select * from LocalPartitionDemo.dbo.SaturdayNightLive print 'SaturdayNightLive_old (optional)' select * from LocalPartitionDemo.dbo.SaturdayNightLive_old print 'SaturdayNightLive_all (optional)' select * from LocalPartitionDemo.dbo.SaturdayNightLive_all exec LocalPartitionDemo.dbo.SaturdayNightLiveRollback print '*****After Rollback*****' print 'SaturdayNightLive' exec sp_help 'dbo.SaturdayNightLive' select * from LocalPartitionDemo.dbo.SaturdayNightLive /* output for test script execution Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'SaturdayNightLive_A'. Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'PKN_SaturdayNightLive_A__LogPKey'. Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'DF_SaturdayNightLive_A__CompletionDate'. Caution: Changing any part of an object name could break scripts and stored procedures. The INDEX was renamed to 'IXC_SaturdayNightLive_A__CompletionDate'. *****After Conversion***** SaturdayNightLive Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.SaturdayNightLive as select LogPkey , item , status , type , CreationDate , CompletionDate from LocalPartitionDemo.dbo.SaturdayNightLive_A LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ SaturdayNightLive_old (optional) Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.SaturdayNightLive_old as select LogPkey , item , status , type , CreationDate , CompletionDate from LocalPartitionDemo.dbo.SaturdayNightLive_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ SaturdayNightLive_all (optional) Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.SaturdayNightLive_all as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.SaturdayNightLive_A union all select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.SaturdayNightLive_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ *****Set test data***** *****Before Rollover***** SaturdayNightLive Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.SaturdayNightLive as select LogPkey , item , status , type , CreationDate , CompletionDate from LocalPartitionDemo.dbo.SaturdayNightLive_A LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:39:56.263 2004-01-27 09:39:56.263 2 current table 2 2 2004-01-26 09:39:56.303 2004-01-27 09:39:56.303 SaturdayNightLive_old (optional) Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.SaturdayNightLive_old as select LogPkey , item , status , type , CreationDate , CompletionDate from LocalPartitionDemo.dbo.SaturdayNightLive_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 3 other table 2 3 2004-01-27 09:39:56.303 2004-01-27 09:39:56.303 4 other table 2 4 2004-01-27 09:39:56.303 2004-01-27 09:39:56.303 SaturdayNightLive_all (optional) Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.SaturdayNightLive_all as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.SaturdayNightLive_A union all select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.SaturdayNightLive_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:39:56.263 2004-01-27 09:39:56.263 2 current table 2 2 2004-01-26 09:39:56.303 2004-01-27 09:39:56.303 3 other table 2 3 2004-01-27 09:39:56.303 2004-01-27 09:39:56.303 4 other table 2 4 2004-01-27 09:39:56.303 2004-01-27 09:39:56.303 *****After Rollover***** SaturdayNightLive Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE view dbo.SaturdayNightLive as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.SaturdayNightLive_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ SaturdayNightLive_old (optional) Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE view dbo.SaturdayNightLive_old as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.SaturdayNightLive_A LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:39:56.263 2004-01-27 09:39:56.263 2 current table 2 2 2004-01-26 09:39:56.303 2004-01-27 09:39:56.303 SaturdayNightLive_all (optional) Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.SaturdayNightLive_all as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.SaturdayNightLive_A union all select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.SaturdayNightLive_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:39:56.263 2004-01-27 09:39:56.263 2 current table 2 2 2004-01-26 09:39:56.303 2004-01-27 09:39:56.303 *****New rows After Rollover***** SaturdayNightLive LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 5 current table 5 5 2004-01-26 09:39:57.747 2004-01-27 09:39:57.747 6 current table 6 6 2004-01-26 09:39:57.757 2004-01-27 09:39:57.757 SaturdayNightLive_old (optional) LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:39:56.263 2004-01-27 09:39:56.263 2 current table 2 2 2004-01-26 09:39:56.303 2004-01-27 09:39:56.303 SaturdayNightLive_all (optional) LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:39:56.263 2004-01-27 09:39:56.263 2 current table 2 2 2004-01-26 09:39:56.303 2004-01-27 09:39:56.303 5 current table 5 5 2004-01-26 09:39:57.747 2004-01-27 09:39:57.747 6 current table 6 6 2004-01-26 09:39:57.757 2004-01-27 09:39:57.757 Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'SaturdayNightLive'. Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'PKN_SaturdayNightLive__LogPKey'. Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'DF_SaturdayNightLive__CompletionDate'. Caution: Changing any part of an object name could break scripts and stored procedures. The INDEX was renamed to 'IXC_SaturdayNightLive__CompletionDate'. *****After Rollback***** SaturdayNightLive Name Owner Type Created_datetime -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------ SaturdayNightLive dbo user table 2004-01-27 09:39:55.883 Column_name Type Computed Length Prec Scale Nullable TrimTrailingBlanks FixedLenNullInSource Collation -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------------------------------- ----------- ----- ----- ----------------------------------- ----------------------------------- ----------------------------------- -------------------------------------------------------------------------------------------------------------------------------- LogPKey int no 4 10 0 no (n/a) (n/a) NULL Item varchar no 20 no no no SQL_Latin1_General_CP1_CI_AS status char no 2 no no no SQL_Latin1_General_CP1_CI_AS type smallint no 2 5 0 yes (n/a) (n/a) NULL CreationDate datetime no 8 yes (n/a) (n/a) NULL CompletionDate datetime no 8 no (n/a) (n/a) NULL Identity Seed Increment Not For Replication -------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------- ---------------------------------------- ------------------- No identity column defined. NULL NULL NULL RowGuidCol -------------------------------------------------------------------------------------------------------------------------------- No rowguidcol column defined. Data_located_on_filegroup -------------------------------------------------------------------------------------------------------------------------------- DATA index_name index_description index_keys -------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- IXC_SaturdayNightLive__CompletionDate clustered located on DATA CompletionDate PKN_SaturdayNightLive__LogPKey nonclustered, unique, primary key located on INDEXES LogPKey constraint_type constraint_name delete_action update_action status_enabled status_for_replication constraint_keys -------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------- ------------- -------------- ---------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- DEFAULT on column CompletionDate DF_SaturdayNightLive__CompletionDate (n/a) (n/a) (n/a) (n/a) (getdate()) PRIMARY KEY (non-clustered) PKN_SaturdayNightLive__LogPKey (n/a) (n/a) (n/a) (n/a) LogPKey No foreign keys reference this table. No views with schema binding reference this table. LogPKey Item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:39:56.263 2004-01-27 09:39:56.263 2 current table 2 2 2004-01-26 09:39:56.303 2004-01-27 09:39:56.303 5 current table 5 5 2004-01-26 09:39:57.747 2004-01-27 09:39:57.747 6 current table 6 6 2004-01-26 09:39:57.757 2004-01-27 09:39:57.757 */