-- xtype = '167' 字段類型為 varchar 可用 select * from systypes 查看
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

------解决方案--------------------
SQL code
有什么哪里不明白的指出来~~

------解决方案--------------------
探讨

SQL code-- xtype = '167' 字段類型為 varchar 可用 select * from systypes 查看Declare  curAlterInfoCursorForSelect a.Name AlterFieldName, a.Length, a.IsNullable, b.Name AlterTableNameFrom SysColumns a, SysObject?-


------解决方案--------------------