|
use SourceDB; |
|
go; |
|
|
|
declare @Increment bigint |
|
set @Increment = 6000 -- value from step 3 |
|
|
|
declare @TablesToImport table |
|
( |
|
[Name] nvarchar(128) |
|
) |
|
|
|
insert into @TablesToImport (Name) values |
|
('Users'), ('Orders'), ('Products'), ('Categories') |
|
|
|
declare @FkToIncrement table |
|
( |
|
[Schema] nvarchar(128), |
|
[Table] nvarchar(128), |
|
[Column] nvarchar(128) |
|
) |
|
|
|
-- SCAN SYSTEM TABLES |
|
insert into @FkToIncrement ([Schema], [Table], [Column]) |
|
select |
|
sc.TABLE_SCHEMA, |
|
t.name [table], |
|
c.name ForeignKeyColumn |
|
from sys.foreign_key_columns as fk |
|
inner join sys.tables as t |
|
on fk.parent_object_id = t.object_id |
|
inner join sys.columns as c |
|
on fk.parent_object_id = c.object_id |
|
and fk.parent_column_id = c.column_id |
|
inner join INFORMATION_SCHEMA.COLUMNS sc |
|
on sc.TABLE_NAME = t.name |
|
and sc.COLUMN_NAME = c.name |
|
where fk.referenced_object_id in |
|
( |
|
select object_id |
|
from sys.tables |
|
where name in |
|
( |
|
select [Name] |
|
from @TablesToImport |
|
) |
|
) |
|
and t.name in |
|
( |
|
select [Name] |
|
from @TablesToImport |
|
) |
|
|
|
-- REMOVE ALL FULLTEXT INDEXES |
|
declare @dropFtSql nvarchar(max) |
|
set @dropFtSql = '' |
|
|
|
select @dropFtSql = @dropFtSql |
|
+ 'drop fulltext index on ' + t.name + ';' |
|
from sys.tables t |
|
inner join sys.fulltext_indexes fi |
|
on t.[object_id] = fi.[object_id] |
|
|
|
if (@dropFtSql <> '') |
|
begin |
|
execute (@dropFtSql) |
|
end |
|
print 'all fulltext indexes are removed' |
|
|
|
-- REMOVE ALL FOREIGN KEYS |
|
declare @removeFkSql nvarchar(MAX) |
|
set @removeFkSql = '' |
|
|
|
select @removeFkSql = @removeFkSql + 'alter table ' |
|
+ QUOTENAME(CONSTRAINT_SCHEMA) + '.' |
|
+ QUOTENAME(TABLE_NAME) + ' drop constraint ' |
|
+ QUOTENAME(CONSTRAINT_NAME) + '; ' |
|
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS |
|
where CONSTRAINT_TYPE = 'FOREIGN KEY' |
|
|
|
if (@removeFkSql <> '') |
|
begin |
|
execute(@removeFkSql) |
|
end |
|
print 'all foreign keys are removed' |
|
|
|
-- REMOVE ALL PRIMARY KEYS |
|
declare @removePkSql nvarchar(MAX) |
|
set @removePkSql = '' |
|
|
|
select @removePkSql = @removePkSql + 'alter table ' |
|
+ QUOTENAME(CONSTRAINT_SCHEMA) + '.' |
|
+ QUOTENAME(TABLE_NAME) + ' drop constraint ' |
|
+ QUOTENAME(CONSTRAINT_NAME) + '; ' |
|
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS |
|
where CONSTRAINT_TYPE = 'PRIMARY KEY' |
|
|
|
if (@removePkSql <> '') |
|
begin |
|
execute(@removePkSql) |
|
end |
|
print 'all primary keys are removed' |
|
|
|
declare @Schema nvarchar(128), |
|
@Table NVARCHAR(128), |
|
@Column NVARCHAR(128), |
|
@DataType NVARCHAR(128); |
|
|
|
declare FkCursor cursor for |
|
select * from @FkToIncrement |
|
|
|
open FkCursor |
|
|
|
fetch next from FkCursor |
|
into @Schema, @Table, @Column |
|
while @@FETCH_STATUS = 0 |
|
begin |
|
execute('update ['+@Schema+'].['+@Table+'] |
|
set ['+@Column+'] = ['+@Column+'] + ' + @Increment) |
|
fetch next from FkCursor into @Schema, @Table, @Column |
|
end |
|
|
|
close FkCursor |
|
deallocate FkCursor |
|
|
|
print 'all foreign keys are incremented' |
|
|
|
declare @IdentityToIncrement table |
|
( |
|
[Schema] nvarchar(128), |
|
[Table] nvarchar(128), |
|
[Column] nvarchar(128), |
|
[DataType] nvarchar(128) |
|
) |
|
|
|
-- SCAN SYSTEM TABLES |
|
insert into @IdentityToIncrement |
|
( |
|
[Schema], |
|
[Table], |
|
[Column], |
|
[DataType] |
|
) |
|
select |
|
TABLE_SCHEMA, |
|
TABLE_NAME, |
|
COLUMN_NAME, |
|
DATA_TYPE |
|
from INFORMATION_SCHEMA.COLUMNS |
|
where COLUMNPROPERTY( |
|
OBJECT_ID(TABLE_NAME), |
|
COLUMN_NAME, |
|
'IsIdentity') = 1 |
|
and OBJECTPROPERTY( |
|
OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME), |
|
'IsTable') = 1 |
|
and TABLE_NAME IN ( |
|
select Name |
|
from @TablesToImport |
|
) |
|
|
|
-- INCREMENT IDENTITY COLUMNS |
|
declare IdentityCursor cursor for |
|
select * from @IdentityToIncrement |
|
|
|
open IdentityCursor |
|
|
|
fetch next from IdentityCursor |
|
into @Schema, @Table, @Column, @DataType |
|
|
|
while @@FETCH_STATUS = 0 |
|
begin |
|
execute('alter table ['+@Schema+'].['+@Table+'] add '+@Column+'_Copy ' + @DataType + '') |
|
execute('update ['+@Schema+'].['+@Table+'] set '+@Column+'_Copy = '+@Column+'') |
|
execute('alter table ['+@Schema+'].['+@Table+'] drop column '+@Column+'') |
|
execute('exec sp_rename ''['+@Schema+'].['+@Table+'].'+@Column+'_Copy'', '''+@Column+''', ''COLUMN''') |
|
execute('update ['+@Schema+'].['+@Table+'] set ['+@Column+'] = ['+@Column+'] + ' + @Increment) |
|
execute('alter table ['+@Schema+'].['+@Table+'] alter column ['+@Column+'] ' + @DataType + ' not null') |
|
fetch next from IdentityCursor into @Schema, @Table, @Column, @DataType |
|
end |
|
|
|
close IdentityCursor |
|
deallocate IdentityCursor |
|
|
|
print 'all identity columns are incremented' |