/******************************************************************************* A/B table conversion - moderate complexity application may need to persist some data in the current data set at rollover application has isolated need to reference full data set *******************************************************************************/ /* 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.MiddleOfTheRoad','U') is null and object_id('LocalPartitionDemo.dbo.MiddleOfTheRoad','V') is null begin CREATE TABLE [dbo].[MiddleOfTheRoad] ( [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_MiddleOfTheRoad__CompletionDate] DEFAULT (getdate()) , CONSTRAINT [PKN_MiddleOfTheRoad__LogPKey] PRIMARY KEY NONCLUSTERED ( [LogPKey] ) ON [INDEXES] ) ON [DATA] CREATE CLUSTERED INDEX [IXC_MiddleOfTheRoad__CompletionDate] ON [dbo].[MiddleOfTheRoad]([CompletionDate]) ON [DATA] end go -- create A and B versions of the table with matching constraints use LocalPartitionDemo if object_id('dbo.MiddleOfTheRoadConversion','P') is not null drop procedure [dbo].[MiddleOfTheRoadConversion] go create procedure [dbo].[MiddleOfTheRoadConversion] as if (object_id('dbo.MiddleOfTheRoad','U') is not null) and (object_id('dbo.MiddleOfTheRoad_A','U') is null) and (object_id('dbo.MiddleOfTheRoad_B','U') is null) begin -- rename the existing table to be the "A" version exec sp_rename 'dbo.MiddleOfTheRoad' , 'MiddleOfTheRoad_A' , 'OBJECT' exec sp_rename 'PKN_MiddleOfTheRoad__LogPKey' , 'PKN_MiddleOfTheRoad_A__LogPKey' , 'OBJECT' exec sp_rename 'DF_MiddleOfTheRoad__CompletionDate' , 'DF_MiddleOfTheRoad_A__CompletionDate' , 'OBJECT' exec sp_rename 'dbo.MiddleOfTheRoad_A.IXC_MiddleOfTheRoad__CompletionDate' , 'IXC_MiddleOfTheRoad_A__CompletionDate' , 'INDEX' -- remove permissions granted on the table (will grant to view below) REVOKE SELECT ON [dbo].[MiddleOfTheRoad_A] TO [ApplicationUser] -- now create a "B" version CREATE TABLE [dbo].[MiddleOfTheRoad_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_MiddleOfTheRoad_B__CompletionDate] DEFAULT (getdate()) , CONSTRAINT [PKN_MiddleOfTheRoad_B__LogPKey] PRIMARY KEY NONCLUSTERED ( [LogPKey] ) ON [INDEXES] ) ON [DATA] CREATE CLUSTERED INDEX [IXC_MiddleOfTheRoad_B__CompletionDate] ON [dbo].[MiddleOfTheRoad_B]([CompletionDate]) ON [DATA] -- create a view named the sname as the table had been named exec ('create view dbo.MiddleOfTheRoad ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from LocalPartitionDemo.dbo.MiddleOfTheRoad_A') -- grant permissions that table had to the view GRANT SELECT ON [dbo].[MiddleOfTheRoad] TO [ApplicationUser] -- 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.MiddleOfTheRoad_old ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from LocalPartitionDemo.dbo.MiddleOfTheRoad_B') -- grant permissions to this view as necessary GRANT SELECT ON [dbo].[MiddleOfTheRoad_old] TO [ApplicationUser] -- create a view of the union of the "in use" and the "not in use" tables -- needed so application code can read the data in both tables -- union rather than union all because carry over rows can result in duplicates -- note that an index cannot be materialized on a view containing any union operation exec('create view dbo.MiddleOfTheRoad_all ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from dbo.MiddleOfTheRoad_A ' + 'union ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from dbo.MiddleOfTheRoad_B') -- grant permissions to this view as necessary GRANT SELECT ON [dbo].[MiddleOfTheRoad_all] TO [ApplicationUser] GRANT SELECT ON [dbo].[MiddleOfTheRoad_all] TO [DataAnalyst] end else raiserror('Could not complete conversion',16,1) go use LocalPartitionDemo if object_id('dbo.MiddleOfTheRoadRollover','P') is not null drop procedure [dbo].[MiddleOfTheRoadRollover] go create procedure [dbo].[MiddleOfTheRoadRollover] 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 = 'MiddleOfTheRoad' 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 = 'MiddleOfTheRoad_A' -- then @OtherTable = 'MiddleOfTheRoad_B' -- else @OtherTable = 'MiddleOfTheRoad_A' select @OtherTable = table_schema + '.' + table_name from information_schema.view_table_usage where view_name = 'MiddleOfTheRoad_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. no need to maintain the view based on the union of the two tables. Carry over the rows that are status of 1 */ set @SQLStr = 'select top 1 CompletionDate from ' + @CurrentTable + ' with(holdlock,tablock) ' + 'truncate table ' + @OtherTable + ' ' + 'insert ' + @OtherTable + ' ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from ' + @CurrentTable + ' ' + 'where status = 1 ' + 'exec(''alter view dbo.MiddleOfTheRoad ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from ' + @OtherTable + ''') ' + 'exec(''alter view dbo.MiddleOfTheRoad_old ' + 'as ' + 'select LogPkey ' + ', item ' + ', status ' + ', type ' + ', CreationDate ' + ', CompletionDate ' + 'from ' + @CurrentTable + ''') ' -- start a transaction to place a shared lock on the "in use" table -- to block data 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.MiddleOfTheRoadRollback','P') is not null drop procedure [dbo].[MiddleOfTheRoadRollback] go create procedure [dbo].[MiddleOfTheRoadRollback] 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.MiddleOfTheRoad','V') is not null) and (object_id('dbo.MiddleOfTheRoad_A','U') is not null) and (object_id('dbo.MiddleOfTheRoad_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 = 'MiddleOfTheRoad' 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_MiddleOfTheRoad_A__LogPKey','MiddleOfTheRoad_A',@PrimaryTable) set @Dft = replace('DF_MiddleOfTheRoad_A__CompletionDate','MiddleOfTheRoad_A',@PrimaryTable) set @IXN = 'dbo.MiddleOfTheRoad.' + replace('IXC_MiddleOfTheRoad_A__CompletionDate','MiddleOfTheRoad_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 = 'MiddleOfTheRoad_A' then @OtherTable = 'MiddleOfTheRoad_B' else @OtherTable = 'MiddleOfTheRoad_A' select @OtherTable = table_name from information_schema.view_table_usage where view_name = 'MiddleOfTheRoad_old' and view_schema = 'dbo' drop view [dbo].[MiddleOfTheRoad] drop view [dbo].[MiddleOfTheRoad_old] drop view [dbo].[MiddleOfTheRoad_all] -- rename the Primary table to become the only table exec sp_rename @PrimaryTable,'MiddleOfTheRoad','OBJECT' exec sp_rename @PKey,'PKN_MiddleOfTheRoad__LogPKey','OBJECT' exec sp_rename @Dft,'DF_MiddleOfTheRoad__CompletionDate','OBJECT' exec sp_rename @IXN,'IXC_MiddleOfTheRoad__CompletionDate','INDEX' -- get all remaining data back into the table -- could be some carry over that would result in duplicates exec ('insert dbo.MiddleOfTheRoad ' + 'select * from dbo.' + @OtherTable + ' o ' + 'where not exists (select 1 from dbo.MiddleOfTheRoad ' + 'where LogPkey = o.LogPkey)' ) -- now drop "B" version exec ('drop table dbo.' + @OtherTable) -- restore permissions back to the table GRANT SELECT ON [dbo].[MiddleOfTheRoad] TO [ApplicationUser] end else raiserror('Unable to Rollback',16,1) go -- MiddleOfTheRoad test script use LocalPartitionDemo set nocount on -- convert to AB table print '*****Before Conversion*****' print 'MiddleOfTheRoad' exec sp_help MiddleOfTheRoad select * from LocalPartitionDemo.dbo.MiddleOfTheRoad exec LocalPartitionDemo.dbo.MiddleOfTheRoadConversion print '*****After Conversion*****' print 'MiddleOfTheRoad' exec sp_helptext MiddleOfTheRoad select * from LocalPartitionDemo.dbo.MiddleOfTheRoad print 'MiddleOfTheRoad_old' exec sp_helptext MiddleOfTheRoad_old select * from LocalPartitionDemo.dbo.MiddleOfTheRoad_old print 'MiddleOfTheRoad_all' exec sp_helptext MiddleOfTheRoad_all select * from LocalPartitionDemo.dbo.MiddleOfTheRoad_all print '*****Set test data*****' -- clean up add a litle MiddleOfTheRoad data -- notice that data can be maintained via either of these views -- however, 'MiddleOfTheRoad_all' will not support insert, uodate, or delete. Only select. delete dbo.MiddleOfTheRoad delete dbo.MiddleOfTheRoad_old insert dbo.MiddleOfTheRoad (LogPKey, Item, status, Type, CreationDate) values (1,'current table',1,1,getdate()-1) insert dbo.MiddleOfTheRoad (LogPKey, Item, status, Type, CreationDate) values (2,'current table',1,2,getdate()-1) insert dbo.MiddleOfTheRoad_old (LogPKey, Item, status, Type, CreationDate) values (3,'other table',2,3,getdate()) insert dbo.MiddleOfTheRoad_old (LogPKey, Item, status, Type, CreationDate) values (4,'other table',2,4,getdate()) -- move a row to the status indicating no need to keep in the active set update dbo.MiddleOfTheRoad set Status = 2 where logPKey = 2 print '*****Before Rollover*****' print 'MiddleOfTheRoad' exec sp_helptext MiddleOfTheRoad select * from LocalPartitionDemo.dbo.MiddleOfTheRoad print 'MiddleOfTheRoad_old' exec sp_helptext MiddleOfTheRoad_old select * from LocalPartitionDemo.dbo.MiddleOfTheRoad_old print 'MiddleOfTheRoad_all' exec sp_helptext MiddleOfTheRoad_all select * from LocalPartitionDemo.dbo.MiddleOfTheRoad_all exec LocalPartitionDemo.dbo.MiddleOfTheRoadRollover print '*****After Rollover*****' print 'MiddleOfTheRoad' exec sp_helptext MiddleOfTheRoad select * from LocalPartitionDemo.dbo.MiddleOfTheRoad print 'MiddleOfTheRoad_old' exec sp_helptext MiddleOfTheRoad_old select * from LocalPartitionDemo.dbo.MiddleOfTheRoad_old print 'MiddleOfTheRoad_all' exec sp_helptext MiddleOfTheRoad_all select * from LocalPartitionDemo.dbo.MiddleOfTheRoad_all exec LocalPartitionDemo.dbo.MiddleOfTheRoadRollback print '*****After Rollback*****' print 'MiddleOfTheRoad' exec sp_help MiddleOfTheRoad select * from LocalPartitionDemo.dbo.MiddleOfTheRoad /* test script output *****Before Conversion***** MiddleOfTheRoad Name Owner Type Created_datetime -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------ MiddleOfTheRoad dbo user table 2004-01-27 09:43:57.770 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_MiddleOfTheRoad__CompletionDate clustered located on DATA CompletionDate PKN_MiddleOfTheRoad__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_MiddleOfTheRoad__CompletionDate (n/a) (n/a) (n/a) (n/a) (getdate()) PRIMARY KEY (non-clustered) PKN_MiddleOfTheRoad__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 ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'MiddleOfTheRoad_A'. Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'PKN_MiddleOfTheRoad_A__LogPKey'. Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'DF_MiddleOfTheRoad_A__CompletionDate'. Caution: Changing any part of an object name could break scripts and stored procedures. The INDEX was renamed to 'IXC_MiddleOfTheRoad_A__CompletionDate'. *****After Conversion***** MiddleOfTheRoad Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.MiddleOfTheRoad as select LogPkey , item , status , type , CreationDate , CompletionDate from LocalPartitionDemo.dbo.MiddleOfTheRoad_A LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ MiddleOfTheRoad_old Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.MiddleOfTheRoad_old as select LogPkey , item , status , type , CreationDate , CompletionDate from LocalPartitionDemo.dbo.MiddleOfTheRoad_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ MiddleOfTheRoad_all Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.MiddleOfTheRoad_all as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.MiddleOfTheRoad_A union select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.MiddleOfTheRoad_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ *****Set test data***** *****Before Rollover***** MiddleOfTheRoad Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.MiddleOfTheRoad as select LogPkey , item , status , type , CreationDate , CompletionDate from LocalPartitionDemo.dbo.MiddleOfTheRoad_A LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:43:58.010 2004-01-27 09:43:58.010 2 current table 2 2 2004-01-26 09:43:58.020 2004-01-27 09:43:58.020 MiddleOfTheRoad_old Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.MiddleOfTheRoad_old as select LogPkey , item , status , type , CreationDate , CompletionDate from LocalPartitionDemo.dbo.MiddleOfTheRoad_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 3 other table 2 3 2004-01-27 09:43:58.020 2004-01-27 09:43:58.020 4 other table 2 4 2004-01-27 09:43:58.030 2004-01-27 09:43:58.030 MiddleOfTheRoad_all Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.MiddleOfTheRoad_all as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.MiddleOfTheRoad_A union select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.MiddleOfTheRoad_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:43:58.010 2004-01-27 09:43:58.010 2 current table 2 2 2004-01-26 09:43:58.020 2004-01-27 09:43:58.020 3 other table 2 3 2004-01-27 09:43:58.020 2004-01-27 09:43:58.020 4 other table 2 4 2004-01-27 09:43:58.030 2004-01-27 09:43:58.030 *****After Rollover***** MiddleOfTheRoad Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE view dbo.MiddleOfTheRoad as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.MiddleOfTheRoad_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:43:58.010 2004-01-27 09:43:58.010 MiddleOfTheRoad_old Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- CREATE view dbo.MiddleOfTheRoad_old as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.MiddleOfTheRoad_A LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:43:58.010 2004-01-27 09:43:58.010 2 current table 2 2 2004-01-26 09:43:58.020 2004-01-27 09:43:58.020 MiddleOfTheRoad_all Text --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- create view dbo.MiddleOfTheRoad_all as select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.MiddleOfTheRoad_A union select LogPkey , item , status , type , CreationDate , CompletionDate from dbo.MiddleOfTheRoad_B LogPkey item status type CreationDate CompletionDate ----------- -------------------- ------ ------ ------------------------------------------------------ ------------------------------------------------------ 1 current table 1 1 2004-01-26 09:43:58.010 2004-01-27 09:43:58.010 2 current table 2 2 2004-01-26 09:43:58.020 2004-01-27 09:43:58.020 Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'MiddleOfTheRoad'. Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'PKN_MiddleOfTheRoad__LogPKey'. Caution: Changing any part of an object name could break scripts and stored procedures. The OBJECT was renamed to 'DF_MiddleOfTheRoad__CompletionDate'. Caution: Changing any part of an object name could break scripts and stored procedures. The INDEX was renamed to 'IXC_MiddleOfTheRoad__CompletionDate'. *****After Rollback***** MiddleOfTheRoad Name Owner Type Created_datetime -------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------- ------------------------------------------------------ MiddleOfTheRoad dbo user table 2004-01-27 09:43:57.910 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_MiddleOfTheRoad__CompletionDate clustered located on DATA CompletionDate PKN_MiddleOfTheRoad__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_MiddleOfTheRoad__CompletionDate (n/a) (n/a) (n/a) (n/a) (getdate()) PRIMARY KEY (non-clustered) PKN_MiddleOfTheRoad__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:43:58.010 2004-01-27 09:43:58.010 2 current table 2 2 2004-01-26 09:43:58.020 2004-01-27 09:43:58.020 */