修改表主键字段数据类型(up_ModifyPrimaryColumn)

开始：

有一需求，就是把表中作为主键字段的数据类型从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)+'.'+