开始：
有一需求，就是把表中作为主键字段的数据类型从int 转换为 uniqueidentifier。我们可能会想到直接通过Microsoft SQL Server Management Studio (MSSMS)修改表结构，删除原来的主键，增加一个数据类型为uniqueidentifier的字段作为主键，并对它设置not null default(newid()) 。
对于单独的表，主键没有被其他表作为外键，可以这样修改，但一旦存在外键时，我们就不能这样修改了。至少我们需要通过编写T-SQL语句来实现，基本方法是：
1.删除外键约束
2.修改表字段类型（删除主键约束-->新增uniqueidentifier类型的字段(default(newid()))，并设置为主键）
3.在其他表（原主键字段，在其他表作为外键的表）新增uniqueidentifier类型的字段(default(newid()))。
4.更新数据
5.字段重新命名
6.重新创建外键
当然你还要考虑索引是否要重新创建，如果我们要修改一大堆的表，那么需要写一大堆的T-SQl代码。
为了解决这一问题，这里我为此特别写了一个存储过程up_ModifyPrimaryColumn来实现表主键数据类型转换。
up_ModifyPrimaryColumn应用范围：
1.作为主键的字段必须是单一字段
2.不考虑分区
存储过程up_ModifyPrimaryColumn：
View Code
Use Test Go if object_ID(\'[up_ModifyPrimaryColumn]\') is not null Drop Procedure [up_ModifyPrimaryColumn] Go Create Proc up_ModifyPrimaryColumn ( @objectname sysname=null, @columnname sysname=null, @type_new sysname=null ) As /* 修改表主键字段数据类型(V2.0) Andy 2012-12-8 说明：在原版脚本的基础上，不删除原表，以确保原来的数据可日后还原。 */ Set Nocount On Declare @objectid int, @columnid int, @type_old nvarchar(512), @typeid_old int, @typeid_new int, @Enter nvarchar(10), @Tab nvarchar(10), @Spacebar nvarchar(10), @Date nvarchar(8) Select @Enter=Char(13)+Char(10), @Tab=Char(9), @Spacebar=Char(32), @Date=Convert(char(8),Getdate(),112) Select @objectid=object_id,@objectname=name From sys.objects As a Where name=@objectname And type=\'U\' And is_ms_shipped=0 And Not Exists(Select 1 From sys.extended_properties Where major_id=a.Object_id And minor_id=0 And class=1 And Name = N\'microsoft_database_tools_support\' ) If @objectid Is null Begin Raiserror 50001 N\'无效的表名!\' Return End If not Exists(Select 1 From sys.columns Where object_id=@objectid And name=@columnname) Begin Raiserror 50001 N\'无效的字段名!\' Return End If Not Exists(Select 1 From sys.indexes As a Inner Join sys.index_columns As b On b.object_id=a.object_id And b.index_id=a.index_id And b.index_id=1 And a.is_primary_key=1 Inner Join sys.columns As c On c.object_id=b.object_id And c.column_id=b.column_id Where a.object_id=@objectid And c.name=@columnname ) Begin Raiserror 50001 N\'非主键字段，不需要调用当前存储过程来修改数据类型.\' Return End Exec(\'Declare @x \'+@type_new) If @@Error>0 Begin Print N\'数据类型定义错误!\' Return End Set @typeid_new=type_id(Left(@type_new+\'(\',Charindex(\'(\',@type_new+\'(\')-1)) If Object_id(\'tempdb..#ScriptTable\') Is Not null Drop Table #ScriptTable Create Table #ScriptTable (id int Identity(1,1) Primary Key,[content] nvarchar(4000)) /* 1. Drop Constraint and Index */ If Object_id(\'tempdb..#Tables\') Is Not null Drop Table #Tables Create Table #Tables ( objectid int Primary Key, objectname sysname, columnid int , columnname sysname, typeid_new int, type sysname, TypeDefinition_new nvarchar(512), Flag bit, IsIdentity_new bit, IsRowGuidCol_new bit ) Insert Into #Tables Select a.object_id,object_name(a.object_id),a.column_id,a.name,a.user_type_id,b.name, @type_new + Case When @typeid_new In(48,52,56,59,62,106,108,127) And a.object_id=@objectid Then \' Not null Identity(1,1)\' When @typeid_new =36 And a.object_id=@objectid Then \' Rowguidcol Not null \' Else \'\' End, Case a.object_id When @objectid Then 1 Else 0 End, Case When @typeid_new In(48,52,56,59,62,106,108,127) Then 1 Else 0 End, Case When @typeid_new =36 Then 1 Else 0 End From sys.columns As a Inner Join sys.types As b On b.user_type_id=a.user_type_id Where (a.object_id=@objectid And a.name=@columnname) Or Exists (Select 1 From sys.foreign_key_columns Where parent_object_id=a.object_id And Referenced_object_id=@objectid And parent_column_id=a.column_id ) Insert Into #ScriptTable Select \'Use \'+Quotename(db_name())+@Enter+\'Go\' Insert Into #ScriptTable Select \'If object_id(\'\'\'+Quotename(a.name)+\'\'\') Is Not Null Alter Table \'+ Quotename(object_name(a.parent_object_id))+\' Drop Constraint \'+Quotename(a.name)+@Enter+\'Go\' From sys.objects As a Left Outer Join #Tables As b On b.objectid=a.Parent_object_id Where a.type In(\'PK\',\'F \',\'D \',\'C \',\'UQ\') And ( Exists(Select 1 From #Tables Where objectid=a.Parent_object_id) Or Exists(Select 1 From sys.foreign_keys as x Inner Join #Tables As y On y.objectid=x.referenced_object_id And x.object_id=a.object_id ) ) Order By b.Flag , Case a.type When \'D \' Then 1 When \'C \' Then 2 When \'UQ\' Then 3 When \'F \' Then 4 When \'PK\' Then 5 Else 6 End Insert Into #ScriptTable Select \'If Exists(Select 1 From sys.indexes Where object_id=object_id(\'\'\'+b.objectname+\'\'\') And name=\'\'\'+ a.name+\'\'\') Drop Index \'+Quotename(b.objectname)+\'.\'+Quotename(a.name) From sys.indexes As a Inner Join #Tables As b On b.objectid=a.object_id Where a.name Is Not null Order By b.Flag Insert Into #ScriptTable Select \'Go\' /* 2. Insert Into tempTables */ Insert Into #ScriptTable Select \'If Object_id(\'\'Tempdb..[#\'+objectname+\']\'\') Is Not Null Drop Table [#\'+objectname+\']\'+@Enter+@Tab+ Case Flag When 1 Then Case When IsRowGuidCol_new =1 Then \'Select *,Newid() As [\'+columnname+\'_new] Into [#\'+objectname+\'] From \'+Quotename(objectname) When IsIdentity_new =1 Then \'Select *,Row_number() Over(Order By \'+Quotename(columnname)+\') As [\'+columnname+\'_new] Into [#\'+objectname+\'] From \'+ Quotename(objectname) Else \'\' End Else \'Select *,Convert(\'+TypeDefinition_new+\',null) As [\'+columnname+\'_new] Into [#\'+objectname+\'] From \'+ Quotename(objectname) End+@Enter From #Tables Insert Into #ScriptTable Select \'Go\' /* 3. Update tmpTables */ Insert Into #ScriptTable Select \'Update a Set a.[\'+columnname+\'_new]=b.[\'+@columnname+\'_new] From [#\'+objectname+\'] As a Inner Join [#\'+ @objectname+\'] As b On b.\'+Quotename(@columnname)+\'=a.\'+Quotename(columnname) From #Tables Where Flag=0 Insert Into #ScriptTable Select \'Go\' /* 4. Create Table Tables_new */ If Object_id(\'tempdb..#ObjectDefinition\') Is Not null Drop Table #ObjectDefinition Select a.object_id As objectid,a.column_id As columnid,a.name As columnname, --Column_name Case When a.column_id=e.columnid Then Quotename(a.name)+@Spacebar+e.TypeDefinition_new Else Quotename(a.name)+@Spacebar+b.name+ --data_length Case When a.user_type_id In (127,104,61,34,56,60,99,59,58,52,122,98,35,189,48,36,241) Then \'\' When a.user_type_id In (106,108) Then \'(\'+Rtrim(a.precision)+\',\'+Rtrim(a.scale)+\')\' When a.user_type_id In (239,231) Then \'(\'+Rtrim(a.max_length/2)+\')\' When a.max_length<>b.max_length Then \'(\'+Rtrim(a.max_length)+\')\' Else \'\' End End +@Spacebar+ --Rowguidcol Case a.is_rowguidcol When 1 Then \'Rowguidcol\'+@Spacebar Else \'\' End + --Identity Case a.is_identity When 1 Then \'Identity(\'+Convert(nvarchar(10),c.seed_value)+\',\'+ Convert(nvarchar(10),c.increment_value)+\')\'+@Spacebar Else \'\' End + --Collate --Isnull(\'Collate \'+a.collation_name+@Spacebar,\'\')+ --Null Case a.is_nullable When 1 Then \'Null\' Else \'Not Null\' End +@Spacebar --Default As column_definition Into #ObjectDefinition From sys.columns As a Inner Join sys.types As b On b.user_type_id=a.user_type_id Left Outer Join sys.Identity_columns As c On c.object_id=a.object_id And c.column_id=a.column_id Inner Join #Tables As e On e.objectid=a.object_id Order By a.object_id,a.column_id Create Clustered Index IX_#ObjectDefinition_ObjectidColumnid On #ObjectDefinition(objectid Asc,columnid Asc) Insert Into #ScriptTable Select \'If Object_id(\'\'[\'+objectname+\'_new]\'\') Is Not Null Drop Table [\'+objectname +\'_new]\'+@Enter+ \'Create Table [\'+objectname +\'_new]\'+@Enter+\'(\'+@Enter+@Tab+b.column_definition+@Enter+\')\' From #Tables As a Outer Apply(Select column_definition= Stuff((Select \',\'+Case When a.Flag=1 And columnid=a.columnid Then a.columnname+@Spacebar+TypeDefinition_new Else column_definition End+Char(10)+@Tab From #ObjectDefinition Where objectid=a.objectid For Xml Path(\'\')) ,1,1,\'\') ) As b Insert Into #ScriptTable Select \'Go\' /* 5. Insert Into Tables_new */ Insert Into #ScriptTable Select Case When Flag=1 And IsIdentity_new = 1 Then \'Set Identity_insert [\'+objectname+\'_new] On \'+@Enter When Flag=0 And Exists(Select 1 From sys.columns Where object_id=a.objectid And Is_Identity=1 ) Then \'Set Identity_insert [\'+objectname+\'_new] On \'+@Enter Else \'\' End+ \'Insert Into [\'+objectname+\'_new] (\'+columnListA+\') Select \'+columnListB+\' From [#\'+objectname+\']\'++@Enter+ Case When Flag=1 And IsIdentity_new = 1 Then \'Set Identity_insert [\'+objectname+\'_new] Off \' When Flag=0 And Exists(Select 1 From sys.columns Where object_id=a.objectid And Is_Identity=1 ) Then \'Set Identity_insert [\'+objectname+\'_new] Off \' Else \'\' End From #Tables As a Outer Apply(Select columnListA=Stuff((Select \',\'+Quotename(columnname) From #ObjectDefinition Where objectid=a.objectid For Xml Path(\'\')),1,1,\'\') ) As b Outer Apply(Select columnListB=Stuff((Select \',\'+Quotename(columnname+Case columnid When a.columnid Then \'_new\' Else \'\' End) From #ObjectDefinition Where objectid=a.objectid For Xml Path(\'\')),1,1,\'\') ) As c Insert Into #ScriptTable Select \'Go\' /* 6. Drop Table Tables_old and Tables_temp */ Insert Into #ScriptTable Select \'If object_id(\'\'\'+Quotename(objectname)+\'\'\') Is Not Null \'+@Enter+@Tab+\'Drop Table \'+ -- Quotename(objectname)+@Enter+\'Drop Table [#\'+objectname+\']\' \'[#\'+objectname+\']\' From #Tables Order By Flag Insert Into #ScriptTable Select \'Go\' /* 7. rename Tables_new */ Insert Into #ScriptTable Select N\'Begin Try\' Union All Select Char(9) +N\'Begin Transaction\' Insert Into #ScriptTable Select \'If object_id(\'\'[\'+objectname+\']\'\') Is Not Null And object_id(\'\'[\'+objectname+@Date+\']\'\') Is Null Exec sp_rename \'\'\'+objectname+\'\'\',\'\'\'+objectname+@Date+\'\'\',\'\'OBJECT\'\'\' From #Tables Order By Flag Insert Into #ScriptTable Select \'If object_id(\'\'[\'+objectname+\'_new]\'\') Is Not Null And object_id(\'\'[\'+objectname+\']\'\') Is Null Exec sp_rename \'\'\'+objectname+\'_new\'\',\'\'\'+objectname+\'\'\',\'\'OBJECT\'\'\' From #Tables Order By Flag Insert Into #ScriptTable Select Char(9)+N\'Commit Transaction\' Union All Select N\'End Try\' Union All Select N\'Begin Catch\' Union All Select N\'Print N\'\'重命名表的时候发生错误!\'\'\' Union All Select N\'Rollback Transaction\' Union All Select N\'End Catch\' Union All Select \'Go\' /* 8. Create Constraint/Index */ If Object_id(\'tempdb..#indexes\') Is Not null Drop Table #indexes Select id=Identity(int,1,1), a.object_id As objectid, a.name As Index_name,a.Index_id, a.type_desc Collate database_default As type_desc, Case a.is_unique When 1 Then \'Unique\' Else \'\' End As is_unique, null As data_space,--e.name As data_space, Case a.is_primary_key When 1 Then N\'Primary Key\' Else null End As is_primary_key, Case a.is_unique_constraint When 1 Then N\'Unique\' Else null End As is_unique_constraint, a.fill_factor,a.is_padded,a.is_disabled,a.is_hypothetical,a.allow_row_locks,a.allow_page_locks, b.index_column_id,Case b.is_descending_key When 0 Then N\'Asc\' Else N\'Desc\' End As descending,b.is_included_column, Quotename(c.name) As column_name Into #indexes From sys.indexes As a Inner Join sys.index_columns As b On b.object_id=a.object_id And b.index_id=a.index_id Inner Join sys.columns As c On c.object_id=b.object_id And c.column_id=b.column_id Inner Join sys.data_spaces As d On d.data_space_id=a.data_space_id Inner Join sys.filegroups As e On e.data_space_id=d.data_space_id Where Exists(Select 1 From #Tables Where objectid=a.object_id) Insert Into #ScriptTable Select --Default Case When c1.name Is null Then \'\' Else \'If object_id(\'\'\'+Quotename(a.name)+\'\'\') Is Null Alter Table \'+ Quotename(object_name(a.parent_object_id))+\' Add Constraint \'+Quotename(c1.name)+ \' Default \'+c1.definition+\' For \'+Quotename(c2.name)+@Enter End+ --Check Case When d.name Is null Then \'\' Else \'If object_id(\'\'\'+Quotename(a.name)+\'\'\') Is Null Alter Table \'+ Quotename(object_name(a.parent_object_id))+\' Add Constraint \'+Quotename(d.name)+ \' Check \'+d.definition+@Enter End+ --Unique Key/Primary Key Case When e1.name Is null Then \'\' Else \'If object_id(\'\'\'+Quotename(a.name)+\'\'\') Is Null Alter Table \'+ Quotename(object_name(a.parent_object_id))+\' Add Constraint \'+ Quotename(a.Name)+@Spacebar+Isnull(e2.is_primary_key,e2.is_unique_constraint)+@Spacebar+ e2.type_desc+@Spacebar+\'(\'+e3.column_constraint+\')\'+@Spacebar+Isnull(\'On \'+Quotename(e2.data_space)+@Enter,\'\') End From sys.objects As a Inner Join #Tables As b On b.objectid=a.parent_object_id Left Outer Join sys.Default_constraints As c1 On c1.object_id=a.object_id And a.Type=\'D\' And c1.Parent_column_id<>b.columnid Left Outer Join sys.columns As c2 On c2.object_id=c1.parent_object_id And c2.column_id=c1.parent_column_id Left Outer Join sys.check_constraints As d On d.object_id=a.object_id And a.Type=\'C\' And c1.Parent_column_id<>b.columnid Left Outer Join sys.key_constraints As e1 On e1.object_id=a.object_id And a.Type In(\'UQ\',\'PK\') Left Outer Join #indexes As e2 On e2.index_id=e1.unique_index_id And e2.objectid=e1.parent_object_id And e2.id=(Select Max(id) From #indexes Where objectid=e2.objectid And index_id=e2.index_id) Outer Apply(Select column_constraint=Stuff((Select \',\'+column_name+@Spacebar+descending From #indexes Where objectid=e2.objectid And index_id=e2.index_id For xml Path(\'\')),1,1,\'\') ) As e3 Where a.Type In(\'D \',\'C \',\'UQ\',\'PK\') Order By b.Flag Desc, Case a.type When \'D \' Then 1 When \'C \' Then 2 When \'UQ\' Then 3 When \'PK\' Then 4 Else 5 End --Foreign Key Insert Into #ScriptTable Select Case When f1.name Is null Then \'\' Else \'If object_id(\'\'\'+Quotename(a.name)+\'\'\') Is Null Alter Table \'+ Quotename(object_name(a.parent_object_id))++\' With \'+ Case f1.is_disabled When 0 Then \'Check \' Else \'Nocheck \' End+ \'Add Constraint \'+Quotename(a.name)+\' Foreign Key \'+\'(\'+f2.Foreign_column+\') References \'+ Quotename(object_name(f1.referenced_object_id))+\'(\'+f3.referenced_column+\')\'+\' On Delete \'+ Replace(f1.delete_referential_action_desc,\'_\',\' \')+\' On Update \'+ Replace(f1.update_referential_action_desc,\'_\',\' \')+@Enter End From sys.objects As a Left Outer Join #Tables As b On b.objectid=a.parent_object_id Inner Join sys.foreign_keys As f1 On f1.object_id=a.object_id Outer Apply(Select Foreign_column=Stuff((Select \',\'+Quotename(y.name) From sys.foreign_key_columns x Inner Join sys.columns As y On y.object_id=x.parent_object_id And y.column_id=x.Parent_column_id Where x.constraint_object_id=a.object_id For xml Path(\'\')),1,1,\'\') ) As f2 Outer Apply(Select referenced_column=Stuff((Select \',\'+Quotename(y.name) From sys.foreign_key_columns x Inner Join sys.columns As y On y.object_id=x.referenced_object_id And y.column_id=x.referenced_column_id Where x.constraint_object_id=a.object_id For xml Path(\'\')),1,1,\'\') ) As f3 Where a.Type=\'F \' And ( Exists(Select 1 From #Tables Where objectid=a.Parent_object_id) Or Exists(Select 1 From sys.foreign_keys as x Inner Join #Tables As y On y.objectid=x.referenced_object_id And x.object_id=a.object_id ) ) Order By b.Flag Desc Insert Into #ScriptTable Select \'Go\' --Index Insert Into #ScriptTable Select distinct \'Create \'+is_unique+@Spacebar+a.type_desc + @Spacebar+ \'Index \'+Quotename(a.index_name)+\' On \'+Quotename(object_name(a.objectid))+@Spacebar+ \'(\'+b.[column]+\')\'+@Spacebar+ Isnull(\'Include (\'+c.Clustered_column+\')\'+@Spacebar,\'\')+ Isnull(\'On \'+Quotename(a.data_space),\'\') From #indexes As a Outer Apply(Select [column]=Stuff((Select \',\'+column_name From #indexes Where objectid=a.objectid And index_id=a.index_id And is_included_column=0 For Xml Path(\'\')),1,1,\'\') ) As b Outer Apply(Select Clustered_column=Stuff((Select \',\'+column_name From #indexes Where objectid=a.objectid And index_id=a.index_id And is_included_column=1 For Xml Path(\'\')),1,1,\'\') ) As c Where Not Exists(Select 1 From sys.key_constraints As x Inner Join #Tables As y On y.objectid=x.parent_object_id And x.parent_object_id=a.objectid And x.unique_index_id=a.index_id ) Insert Into #ScriptTable Select \'Go\' --Print Declare @Print nvarchar(4000), @i int Select @i=1,@Print=[content] From #ScriptTable Where id=1 While @Print>\'\' Begin Print @Print Set @i=@i+1 Set @Print=(Select [content] From #ScriptTable Where id=@i) End Drop Table #Tables, #ObjectDefinition, #indexes, #ScriptTable /* e.g: Use Test Go Exec up_ModifyPrimaryColumn @objectname=\'Test1\', @columnname=\'id\', @type_new=\'uniqueidentifier\' */ Go
调用例子：
创建2个表：TableA & TableB
View Code
use test go if object_id(\'TableB\') Is not null Drop Table TableB; if object_id(\'TableA\') Is not null Drop Table TableA; go create table TableA (ID int identity,Code nvarchar(50),Data nvarchar(50),constraint PK_TableA primary key clustered(ID)); create table TableB (ID int identity,TableA_ID int not null,Data nvarchar(50),constraint PK_TableB primary key clustered(ID),constraint FK_TableB_TableA_ID foreign key(TableA_ID) references TableA(ID)); create nonclustered index IX_TableA_Code on TableA(Code) create nonclustered index IX_TableB_TableA_ID on TableB(TableA_ID) go set nocount on insert into TableA(Code,Data)values (\'Nr001\',\'Data1\'),(\'Nr002\',\'Data2\'),(\'Nr003\',\'Data3\'),(\'Nr004\',\'Data4\') insert into TableB(TableA_ID,Data)values (1,\'TableB_Data1\'),(2,\'TableB_Data2\'),(3,\'TableB_Data3\'),(4,\'TableB_Data4\') go
现在转换TableA的主键字段ID数据类型：int --> uniqueidentifier
use test go exec up_ModifyPrimaryColumn \'TableA\',\'ID\',\'uniqueidentifier\' go
生成脚本：
View Code
Use [test] Go If object_id(\'[FK_TableB_TableA_ID]\') Is Not Null Alter Table [TableB] Drop Constraint [FK_TableB_TableA_ID] Go If object_id(\'[PK_TableB]\') Is Not Null Alter Table [TableB] Drop Constraint [PK_TableB] Go If object_id(\'[PK_TableA]\') Is Not Null Alter Table [TableA] Drop Constraint [PK_TableA] Go If Exists(Select 1 From sys.indexes Where object_id=object_id(\'TableB\') And name=\'PK_TableB\') Drop Index [TableB].[PK_TableB] If Exists(Select 1 From sys.indexes Where object_id=object_id(\'TableB\') And name=\'IX_TableB_TableA_ID\') Drop Index [TableB].[IX_TableB_TableA_ID] If Exists(Select 1 From sys.indexes Where object_id=object_id(\'TableA\') And name=\'PK_TableA\') Drop Index [TableA].[PK_TableA] If Exists(Select 1 From sys.indexes Where object_id=object_id(\'TableA\') And name=\'IX_TableA_Code\') Drop Index [TableA].[IX_TableA_Code] Go If Object_id(\'Tempdb..[#TableA]\') Is Not Null Drop Table [#TableA] Select *,Newid() As [ID_new] Into [#TableA] From [TableA] If Object_id(\'Tempdb..[#TableB]\') Is Not Null Drop Table [#TableB] Select *,Convert(uniqueidentifier,null) As [TableA_ID_new] Into [#TableB] From [TableB] Go Update a Set a.[TableA_ID_new]=b.[ID_new] From [#TableB] As a Inner Join [#TableA] As b On b.[ID]=a.[TableA_ID] Go If Object_id(\'[TableA_new]\') Is Not Null Drop Table [TableA_new] Create Table [TableA_new] ( ID uniqueidentifier Rowguidcol Not null ,[Code] nvarchar(50) Null ,[Data] nvarchar(50) Null ) If Object_id(\'[TableB_new]\') Is Not Null Drop Table [TableB_new] Create Table [TableB_new] ( [ID] int Identity(1,1) Not Null ,[TableA_ID] uniqueidentifier Not Null ,[Data] nvarchar(50) Null ) Go Insert Into [TableA_new] ([ID],[Code],[Data]) Select [ID_new],[Code],[Data] From [#TableA] Set Identity_insert [TableB_new] On Insert Into [TableB_new] ([ID],[TableA_ID],[Data]) Select [ID],[TableA_ID_new],[Data] From [#TableB] Set Identity_insert [TableB_new] Off Go If object_id(\'[TableB]\') Is Not Null Drop Table [#TableB] If object_id(\'[TableA]\') Is Not Null Drop Table [#TableA] Go Begin Try Begin Transaction If object_id(\'[TableB]\') Is Not Null And object_id(\'[TableB20121210]\') Is Null Exec sp_rename \'TableB\',\'TableB20121210\',\'OBJECT\' If object_id(\'[TableA]\') Is Not Null And object_id(\'[TableA20121210]\') Is Null Exec sp_rename \'TableA\',\'TableA20121210\',\'OBJECT\' If object_id(\'[TableB_new]\') Is Not Null And object_id(\'[TableB]\') Is Null Exec sp_rename \'TableB_new\',\'TableB\',\'OBJECT\' If object_id(\'[TableA_new]\') Is Not Null And object_id(\'[TableA]\') Is Null Exec sp_rename \'TableA_new\',\'TableA\',\'OBJECT\' Commit Transaction End Try Begin Catch Print N\'重命名表的時候發生錯誤!\' Rollback Transaction End Catch Go If object_id(\'[PK_TableA]\') Is Null Alter Table [TableA] Add Constraint [PK_TableA] Primary Key CLUSTERED ([ID] Asc) If object_id(\'[PK_TableB]\') Is Null Alter Table [TableB] Add Constraint [PK_TableB] Primary Key CLUSTERED ([ID] Asc) If object_id(\'[FK_TableB_TableA_ID]\') Is Null Alter Table [TableB] With Check Add Constraint [FK_TableB_TableA_ID] Foreign Key ([TableA_ID]) References [TableA]([ID]) On Delete NO ACTION On Update NO ACTION Go Create NONCLUSTERED Index [IX_TableA_Code] On [TableA] ([Code]) Create NONCLUSTERED Index [IX_TableB_TableA_ID] On [TableB] ([TableA_ID]) Go
执行生成的脚本，
并检查转换后的结果：
use test Go select * From TableA; select * From TableB; go
小结：
up_ModifyPrimaryColumn已在SQL Server 2005/2008/2008R2作测试通过。在这篇随笔中，我只是描述功能的实现，没有对存储过程up_ModifyPrimaryColumn进行详细的说明，也许里面有些地方写的，与你实际应用的时候有些出入，你可以修改其中的代码来满足实际的需求。如果你对其中的内容感兴趣，哪里有疑问或建议，可以发email或在文章后面的回复中与我联系，非常感谢！