修改表主键字段数据类型(V2.0)
这存储过程up_ModifyPrimaryColumn会生成转换的执行代码，可以使用Copy方式拿执行代码到查询分析器中检查和执行。
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 2009-3-6
說明：在原版腳本的基礎上，不刪除原表，以確保原來的數據可日后還原。
Step:
1. Drop Constraint and Index
2. Insert Into tempTables
3. Update tmpTables
4. Create Table Tables_new
5. Insert Into Tables_new
6. Drop Table Tables_old and Tables_temp
7. rename Tables_new
8. Create Constraint/Index
*/
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 Chinese_Taiwan_Stroke_CI_AS 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
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 2009-3-6
說明：在原版腳本的基礎上，不刪除原表，以確保原來的數據可日后還原。
Step:
1. Drop Constraint and Index
2. Insert Into tempTables
3. Update tmpTables
4. Create Table Tables_new
5. Insert Into Tables_new
6. Drop Table Tables_old and Tables_temp
7. rename Tables_new
8. Create Constraint/Index
*/
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 Chinese_Taiwan_Stroke_CI_AS 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'
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
