Declare curAlterInfo Cursor For Select a.Name AlterFieldName, a.Length, a.IsNullable, b.Name AlterTableName
From SysColumns a, SysObjects b Where a.id = b.id and a.xType = '167'
and b.xType = 'u' and a.cdefault = 0
declare @AlterFieldName nvarchar(50), -- 修改的字段
@AlterTableName nvarchar(50), -- 修改的表名
@Length int, -- 字段以長度
@IsNullable bit, -- 字段是否允許為空
@PkName nvarchar(50), -- 主鍵名
@PkFieldName nvarchar(500), --主鍵字段名
@TmpFieldName nvarchar(50),
@TmpTableName nvarchar(50),
@Sql nvarchar(500)
Set NoCount On
Begin Tran
open curAlterInfo
Fetch curAlterInfo Into @AlterFieldName, @Length, @IsNullable, @AlterTableName
While @@Fetch_Status=0
Begin
print @AlterTableName
--檢查修改的表是否有主鍵
If Exists(Select Name From SysObjects Where xType = 'PK'
and Parent_Obj = (Select id From SysObjects Where Name = @AlterTableName))
Begin
Set @TmpTableName = @AlterTableName
-- 取得主鍵名
Select @PkName = Name From SysObjects Where xType = 'PK'
and Parent_Obj = (Select id From SysObjects Where Name = @AlterTableName)
Set @PkFieldName = ''
-- 主鍵字段
Declare curPkFieldName Cursor For Select b.Name From SysIndexKeys a, SysColumns b
Where a.id = (Select id From SysIndexes Where Name = @PkName)
and a.indid = 1 and a.colid = b.colid and a.id = b.id
-- 取得所有的主鍵字段
Open curPkFieldName
Fetch curPkFieldName Into @TmpFieldName
While @@fetch_status = 0
Begin
Set @PkFieldName = @PkFieldName + @TmpFieldName + ','
Fetch curPkFieldName Into @TmpFieldName
End
Close curPkFieldName
Deallocate curPkFieldName
-- 刪除舊主鍵
Set @Sql = 'ALTER TABLE '+ @AlterTableName + ' DROP CONSTRAINT ' + @PkName
Print @Sql
Exec(@Sql)
end
-- 修改字段
Set @Sql = 'ALTER TABLE ' + @AlterTableName + ' ALTER COLUMN ' + @AlterFieldName
+ ' NVARCHAR( ' + CAST(@Length AS NVARCHAR) + ')'
-- 是否允許為空
if @IsNullable = 0
Set @Sql = @Sql + ' NOT NULL'
Print @sql
Exec(@sql)
Fetch curAlterInfo Into @AlterFieldName, @Length, @IsNullable, @AlterTableName
-- 創建主鍵
If (@AlterTableName <> @TmpTableName or @@fetch_status <> 0) and @PkFieldName <> ''
Begin
Set @PkFieldName = Left(@PkFieldName, Len(@PkFieldName) - 1)
Set @Sql = ' ALTER TABLE ' + @TmpTableName + ' ADD CONSTRAINT ' + @PkName
+ ' PRIMARY KEY CLUSTERED(' + @PkFieldName + ') ON [PRIMARY]'
Print @Sql
Exec(@Sql)
print '-----------------------------'
Set @PkFieldName = ''
End
End
Close curAlterInfo
Deallocate curAlterInfo
If @@Error > 0
Rollback Tran
Else
Commit Tran
Set NoCount Off