На основе обходного пути, предложенного Avkash. И, получив некоторые идеи из https://stackoverflow.com/a/317864/53970, я придумал хранимую процедуру это изменит первичный ключ в таблице. Вероятно, есть проблемы с некоторыми структурами таблиц (пока все работает нормально), и он не обрабатывает триггеры. Но он копирует структуру, данные, ограничения (первичные, внешние, проверочные) и индексы. Разместите это здесь на случай, если кто-то еще наткнется на ту же кирпичную стену, что и я.
create procedure dbo.ChangePK
@src sysname,
@pklist nvarchar(4000), --comma list of primary key fields
@skipfinalrename bit = 1 --set to not perform anything destructive on src table or related tables (for testing)
as
set nocount on
declare @tmpPrefix nvarchar(10)
set @tmpPrefix = 'tmp_'
declare @dest sysname
set @dest = 'tmpCopy'
declare @sql nvarchar(max)
set @sql = ''
--create table script
select @sql = @sql + ' IF EXISTS (SELECT * FROM sysobjects WHERE id = OBJECT_ID(N''' + @dest + ''') AND OBJECTPROPERTY(id, N''IsTable'') = 1) drop table [' + @dest + ']; create table [' + @dest + '] (' + o.list + ')' + CASE WHEN tc.Constraint_Name IS NULL THEN '' ELSE 'ALTER TABLE ' + @dest + ' ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' PRIMARY KEY ' + ' (' + @pklist + ')' END + ';'
from sysobjects so
cross apply
(SELECT
' ['+column_name+'] ' +
data_type + case data_type
when 'sql_variant' then ''
when 'text' then ''
when 'decimal' then '(' + cast(numeric_precision_radix as varchar) + ', ' + cast(numeric_scale as varchar) + ')'
else coalesce('('+case when character_maximum_length = -1 then 'MAX' else cast(character_maximum_length as varchar) end +')','') end + ' ' +
case when exists (
select id from syscolumns
where object_name(id)=so.name
and name=column_name
and columnproperty(id,name,'IsIdentity') = 1
) then
'IDENTITY(' +
cast(ident_seed(so.name) as varchar) + ',' +
cast(ident_incr(so.name) as varchar) + ')'
else ''
end + ' ' +
(case when IS_NULLABLE = 'No' then 'NOT ' else '' end ) + 'NULL ' +
case when information_schema.columns.COLUMN_DEFAULT IS NOT NULL THEN 'DEFAULT '+ information_schema.columns.COLUMN_DEFAULT ELSE '' END + ', '
from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH('')) o (list)
cross apply
(SELECT
', ['+column_name+'] '
from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH('')) c (columnlist)
left join
information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) j (list)
where xtype = 'U'
AND name NOT IN ('dtproperties')
and name = @src
print 'create'
print @sql
exec sp_executesql @sql
--now the inserts
set @sql = ''
select @sql = @sql + ' set identity_insert [' + @dest + '] on; insert into [' + @dest + '] (' + STUFF(c.columnlist,1,2,'') + ') select ' + STUFF(c.columnlist,1,2,'') + ' from [' + @src + '] ; set identity_insert [' + @dest + '] off;'
from sysobjects so
cross apply
(SELECT
', ['+column_name+'] '
from information_schema.columns where table_name = so.name
order by ordinal_position
FOR XML PATH('')) c (columnlist)
left join
information_schema.table_constraints tc
on tc.Table_name = so.Name
AND tc.Constraint_Type = 'PRIMARY KEY'
cross apply
(select '[' + Column_Name + '], '
FROM information_schema.key_column_usage kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) j (list)
where xtype = 'U'
AND name NOT IN ('dtproperties')
and name = @src
print 'data'
print @sql
exec sp_executesql @sql
--now the foreign keys
set @sql = ''
select @sql = @sql + case when tc.Constraint_Name is null then '--no foreign keys' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' FOREIGN KEY ' + ' (' + STUFF(fk1.list,1,2,'') + ') REFERENCES [' + rctc.table_name + '] (' + STUFF(fk2.list,1,2,'') + ');' end
from sysobjects so
left join
information_schema.table_constraints tc on tc.Table_name = so.Name
AND tc.Constraint_Type = 'FOREIGN KEY'
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rcc on tc.constraint_name = rcc.constraint_name
left join INFORMATION_SCHEMA.table_constraints rctc on rcc.unique_constraint_name = rctc.constraint_name
cross apply
(select ', [' + Column_Name + ']'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) fk1 (list)
cross apply
(select ', [' + kcu.Column_Name + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on rc.unique_constraint_name = kcu.constraint_name
WHERE rc.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) fk2 (list)
where xtype = 'U'
and name = @src
print 'foreign keys'
print @sql
exec sp_executesql @sql
--now the unique keys
set @sql = ''
select @sql = @sql + case when tc.Constraint_Name is null then '--no unique keys' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' UNIQUE NONCLUSTERED ' + ' (' + STUFF(fk1.list,1,2,'') + ');' end
from sysobjects so
left join
information_schema.table_constraints tc on tc.Table_name = so.Name
AND tc.Constraint_Type = 'UNIQUE'
cross apply
(select ', [' + Column_Name + ']'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE kcu.Constraint_Name = tc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) fk1 (list)
where xtype = 'U'
and name = @src
print 'unique keys'
print @sql
exec sp_executesql @sql
--now check constraints
set @sql = ''
select @sql = @sql + case when tc.Constraint_Name is null then '--no check constraints' else 'ALTER TABLE ' + @dest + ' WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + tc.Constraint_Name + ' CHECK ' + ' (' + cc.check_clause + ');' end
from sysobjects so
left join
information_schema.table_constraints tc on tc.Table_name = so.Name
AND tc.Constraint_Type = 'CHECK'
left join INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc on cc.Constraint_Name = tc.Constraint_Name
where xtype = 'U'
and name = @src
print 'check constraints'
print @sql
exec sp_executesql @sql
if (@skipfinalrename = 1)
return
set xact_abort on
--now we start affecting the src table
begin tran
--drop fk constraints on src referencing current primary key
set @sql = ''
select @sql = @sql + case when tc.Constraint_Name is null then '--no fk constraints to drop' else 'ALTER TABLE ' + rctc.table_name + ' DROP CONSTRAINT ' + rc.Constraint_Name + ';' end
from sysobjects so
left join information_schema.table_constraints tc on tc.Table_name = so.Name
AND tc.constraint_type = 'PRIMARY KEY'
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on tc.constraint_name = rc.unique_constraint_name
left join information_schema.table_constraints rctc on rc.constraint_name = rctc.constraint_name
where xtype = 'U'
and name = @src
--create fk constraints on dest referencing new primary key
declare @sql2 nvarchar(max)
set @sql2 = ''
select @sql2 = @sql2 + case when tc.Constraint_Name is null then '--no fk constraints to drop' else 'ALTER TABLE [' + rctc.table_name + '] WITH CHECK ADD CONSTRAINT ' + @tmpPrefix + rc.Constraint_Name + ' FOREIGN KEY ' + ' (' + STUFF(fk1.list,1,2,'') + ') REFERENCES [' + @dest + '] (' + STUFF(fk2.list,1,2,'') + ');' end
from sysobjects so
left join information_schema.table_constraints tc on tc.Table_name = so.Name
AND tc.constraint_type = 'PRIMARY KEY'
left join INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc on tc.constraint_name = rc.unique_constraint_name
left join information_schema.table_constraints rctc on rc.constraint_name = rctc.constraint_name
cross apply
(select ', [' + Column_Name + ']'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu
WHERE kcu.Constraint_Name = rc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) fk1 (list)
cross apply
(select ', [' + kcu.Column_Name + ']'
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu on rc1.unique_constraint_name = kcu.constraint_name
WHERE rc1.Constraint_Name = rc.Constraint_Name
ORDER BY
ORDINAL_POSITION
FOR XML PATH('')) fk2 (list)
where xtype = 'U'
and name = @src
----
print 'create new ref fk'
print @sql2
exec sp_executesql @sql2
print 'drop original ref fk'
print @sql
exec sp_executesql @sql
--now we can create the index sql
set @sql2 = ''
select @sql2 = @sql2 + ' IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N''' + @src + ''') AND name = N''' + i.name + ''')
CREATE ' + i.type_desc COLLATE Latin1_General_CS_AS + ' INDEX ' + i.name + ' ON [' + @src + '] (
' + STUFF(ix.list,1,2,'') + '
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];
'
from sys.tables as t
inner join sys.indexes as i on t.[object_id] = i.[object_id]
cross apply
(select ', [' + ac.name + ']'
FROM sys.index_columns ic
inner join sys.all_columns as ac on ic.[object_id] = ac.[object_id] and ic.[column_id] = ac.[column_id]
WHERE ic.[object_id] = i.[object_id] and ic.[index_id] = I.[index_id]
ORDER BY
key_ordinal
FOR XML PATH('')) ix (list)
where
t.name = @src
--now drop the original table
set @sql = 'drop table [' + @src + '];'
print 'drop original original'
print @sql
exec sp_executesql @sql
--now rename the constraints (remove leading tmp_ in effect)
set @sql=''
select @sql = @sql + ' exec sp_rename ''' + tc.constraint_name + ''', ''' + substring(tc.constraint_name, 5, len(tc.constraint_name)-4) + ''';'
from sysobjects so
left join
information_schema.table_constraints tc on tc.Table_name = so.Name
where xtype = 'U'
and name = @dest
print 'rename constraints to original names'
print @sql
exec sp_executesql @sql
--now rename the table back to the original
exec sp_rename @dest, @src
print 'finally apply the indexes'
print @sql2
exec sp_executesql @sql2
print 'finished'
commit tran
person
Ian1971
schedule
20.06.2012
