[SQL Server] 利用T-SQL把資料庫中每個表的nvarchar(max)資料行都換成nvarchar(4000)
前言
最近資料庫開Table開太快，字串資料行都一律開nvarchar(max)想說儲存佔用空間應該跟nvarchar(n)差不多，但直覺事情應該沒那麼簡單
所以上論壇向大家請教的結果後，請問字串資料行可以都偷懶開nvarchar(max)嗎？
決定還是把目前資料庫的所有nvarchar(max)資料行改為nvarchar(4000)，這也是為了能夠讓該資料行能加入索引的索引鍵資料行，幫助效能調校
實作
--先把Default constraints Drop掉
Declare @DropcolumnDF nvarchar(max)=N''
SELECT
@DropcolumnDF =@DropcolumnDF +
N' ALTER TABLE '+sys.tables.name+' DROP ' +sys.default_constraints.name
FROM
sys.columns
inner join
sys.types
on sys.columns.user_type_id=sys.types.user_type_id
INNER JOIN
sys.tables
ON columns.object_id = tables.object_id
INNER JOIN
sys.default_constraints
ON columns.default_object_id = default_constraints.object_id
WHERE
sys.types.name='nvarchar' and sys.columns .max_length=-1
exec( @DropcolumnDF)
--第二段SQL開始↓
Declare @Tables Table
(
TableRowNum int,--資料表的列編號
TableName nvarchar(4000)--資料表的名稱
)
insert into @Tables
--撈出資料庫中全部的資料表名稱
select ROW_NUMBER() over(Order by [name] asc) As TableRowNum,[name] As TableName
from sys.tables Where [type]='U'
Declare @i int =1--資料表的列編號
Declare @tableTotal int =0--資料表總筆數
--計算出共有多少筆資料表要走訪
select @tableTotal = count(*) from @Tables
while(@i<=@tableTotal)--走訪每筆Table
Begin
--撈出一筆 @TableName
Declare @TableName nvarchar(4000)
--找每一筆Table是否含有nvarchar(MAX)資料行
select @TableName = TableName from @Tables
Where TableRowNum=@i
--把@TableName有nvarchar(MAX)的資料行寫入此Table變數
Declare @ContainsWrongColumnTables Table
(
ColumnName nvarchar(4000),
DataType varchar(4000),
MaxLength int,
is_nullable bit
)
insert into @ContainsWrongColumnTables
SELECT
c.name As ColumnName,
t.Name As DataType ,
c.max_length As MaxLength,
c.is_nullable
FROM
sys.columns c
INNER JOIN
sys.types t ON c.user_type_id = t.user_type_id
WHERE
c.object_id = OBJECT_ID(@TableName) And t.name='nvarchar' and c.max_length=-1
Declare @sql nvarchar(4000)=N'' --待會要放入Exec()中執行的SQL語法
Select @sql = @sql +
' Alter Table ' +@TableName +
' Alter Column ['+ColumnName+'] nvarchar(4000) NOT NULL ' +
' Alter Table '+ @TableName+
' ADD DEFAULT (N'''') FOR ['+ColumnName+'] '
from
@ContainsWrongColumnTables
--刪除變數Table，避免下一次跑迴圈時，影響到資料
delete from @ContainsWrongColumnTables
/* 有可能都沒有含nvarchar(max)資料行的表 */
--防呆
if(Len(@sql)>0)--有alter table 的SQL語句
begin
--一個Table同時多個資料行修改成nvarchar(4000)
Exec(@sql)
end
set @i= @i+1--累加1才能離開迴圈
end--end while從晚上12點寫到早上6點(※熬夜通宵是慢性自殺，請勿模仿)
以上原本使用Cursor寫法，第一次挑戰改成迴圈的替代方案
主要邏輯就是找目前的資料庫中的資料表，如果有包含nvarchar(max)資料行的話，會先把它的Default Constraints Drop掉(不然接下來第二段SQL會報錯)
然後再跑迴圈走訪資料庫中的每一個資料表名稱，再依各資料表名稱去找該Table有沒有nvarchar(max)資料行，有的話，就產生出Alter Table TableName Alter Column ColumnName nvarchar(4000) Not NULL的語法
一段一段餵給Exec()函數執行
結語
利用本文章相同邏輯，也可以把原本使用bigint的資料行全部轉換成int資料行型別，就請自行發揮料理吧
參考文章：