SQL批次更新資料庫中所有使用者資料表中欄位類型為tinyint為int

--SQL批次更新資料庫中所有使用者資料表中欄位類型為tinyint為int

--關鍵說明：
--1、從系統資料表syscolumns中的查詢所有xtype='48'的記錄得到類型為[tinyint]的欄位
--2、更新欄位類型前如果該欄位有預設值索引則應先刪除掉對應的索引
--3、資料表欄位資料類型為tinyint在CodeSmith中讀出來的是DbType.Byte類型，需要修正

declare @TableName nvarchar(250)

--聲明讀取資料庫所有資料表名稱遊標mycursor1
declare mycursor1 cursor for select name from dbo.SysObjects WHERE OBJECTPROPERTY(ID, 'IsUserTable') = 1
 --開啟遊標
open mycursor1
--從遊標裡取出資料賦值到我們剛才聲明的資料表名變數中
fetch next from mycursor1 into @TableName
--如果遊標執行成功 
while (@@fetch_status=0)
begin 

--定義遊標中要修正的欄位名變數
Declare @ColumnName nvarchar(255)
Declare @ColumnID int
--通過遊標讀取指定資料表的所有類型為tinyint的欄位

--聲明遊標mycursor2
declare mycursor2 cursor for select name,colid from syscolumns Where ID=OBJECT_ID(@TableName) and xtype='48'  order by colid
 
--開啟遊標
open mycursor2

--從遊標裡取出資料賦值到我們剛才聲明的欄位名變數中
fetch next from mycursor2 into @ColumnName,@ColumnID
 
--如果遊標執行成功 
while (@@fetch_status=0)
begin
 
--1、如果當前欄位存在預設值索引則應先刪除
IF  EXISTS (select * from sys.default_constraints where parent_object_id=OBJECT_ID(@TableName) and
parent_column_id=@ColumnID)
BEGIN
Declare @ConstraintName nvarchar(255)
select @ConstraintName=name from sys.default_constraints where parent_object_id=OBJECT_ID(@TableName) and
parent_column_id=@ColumnID
exec ('ALTER TABLE ['+@TableName+'] DROP CONSTRAINT ['+@ConstraintName+']')

END

--2、更新當前欄位[tinyint]類型為[int]類型
exec ('ALTER TABLE ['+@TableName+'] ALTER COLUMN ['+@ColumnName+'] int')

--用遊標去取下一條記錄
    fetch next from mycursor2 into @ColumnName,@ColumnID
end

--關閉遊標
close mycursor2
--撤銷遊標
deallocate mycursor2

 --用遊標去取下一條記錄
    fetch next from mycursor1 into @TableName
end
--關閉遊標
close mycursor1
--撤銷遊標
deallocate mycursor1