SQL - 產生每個資料表欄位的 Alter Column 語法 | 張小呆的碎碎唸 - 點部落

2011-06-12

SQL - 產生每個資料表欄位的 Alter Column 語法

6123
0
MS SQL

摘要:SQL - 產生每個資料表欄位的 Alter Column 語法

這篇文章的誕生，主要歸功於我的好同事，怎麼說呢!? 話說今天在努力撰寫程式與測試系統，結果在一大片的程式海中出現了「字串或二進位資料會被截斷」這種經典問題，當時有幾種作法：
1.在茫茫程式海中，單步執行到錯，就知到哪一個資料表有誤，之後再與正式機做比對，找出錯
   誤的欄位長度
2.將正式機的資料表匯出成 Script，並將開發機的資料表全部 Drop 掉，再將正式機的資料表匯
   入到測試機
3.將正式機資料庫備份後，再還原到開發機中

呼呼!! 還真多方式耶，但感覺有點麻煩兼複雜。可惜 MS SQL 2005 並未提供將全部資料表匯出成 Alter Column 的方式，那...就自己寫嚕。畢竟，日後會遇到的機會還是很大，到時就可以用這樣的方式快速的將正式機「特定」或「全部」的資料表欄位組成 Alter Column 語法，以下就來看看唄...

環境說明：
1.MS SQL Server 2005(而後的版本，沒試過是否正常，有興趣的看倌可以自行測試)
2.欄位的型別均採用原生型別，若有自己定義、開發或計算欄位者，請自行修改

使用方式：
1.Exce P_GenerateAlterColumn '', '' --表示全部都要
2.Exce P_GenerateAlterColumn 'dbo', '' --表示 Schema Name 為 dbo 的資料表都要
3.Exce P_GenerateAlterColumn 'dbo,dbo1', '' --表示 Schema Name 為 dbo 與 dbo1 的資料表都要
4.Exce P_GenerateAlterColumn 'dbo', 'TableA' --表示 Schema Name 為 dbo，並且只要產生 TableA 的語法
5.Exce P_GenerateAlterColumn 'dbo', 'TableA,TableB' --表示 Schema Name 為 dbo，並且產生 TableA 與 TableB 的語法

T-SQL：

CREATE PROCEDURE P_GenerateAlterColumn(
@p_SchemaList nvarchar(max) = NULL,
@p_TableList nvarchar(max) = NULL
)
AS
BEGIN
--判斷字串第一碼是否為逗號
IF CHARINDEX(',', @p_SchemaList) = 1
begin
  print 'Schema參數不允許第一碼就是逗號';
  return;
end

IF CHARINDEX(',', @p_TableList) = 1
begin
print 'Table參數不允許第一碼就是逗號';
return;
end

--判斷字串最後一碼是否為逗號
IF CHARINDEX(',', reverse(@p_SchemaList)) = 1
begin
print 'Schema參數不允許最後一碼為逗號';
return;
end

IF CHARINDEX(',', reverse(@p_TableList)) = 1
begin
print 'Table參數不允許最後一碼為逗號';
return;
end

DECLARE @p_SQL nvarchar(max)

Set @p_SchemaList = Replace(@p_SchemaList, ' ', '')
Set @p_SchemaList = Replace(@p_SchemaList, ' ', '')

Set @p_TableList = Replace(@p_TableList, ' ', '')
Set @p_TableList = Replace(@p_TableList, ' ', '')

Set @p_SQL = 'SELECT ''ALTER TABLE ['' + t.DB_Schema + ''].['' + t.Table_Name + ''] ALTER COLUMN ['' + t.Column_Name + ''] '' + t.Column_Type + '' '' + t.Column_Nullable '
Set @p_SQL = @p_SQL + 'FROM ('
Set @p_SQL = @p_SQL + 'SELECT s.name as ''DB_Schema'', t1.name as ''Table_Name'', c.name as ''Column_Name'', '
Set @p_SQL = @p_SQL + 'CASE '
Set @p_SQL = @p_SQL + 'WHEN t2.name = ''binary'' THEN ''binary('' + Convert(nvarchar(20), c.max_length) + '')'' '
Set @p_SQL = @p_SQL + 'WHEN t2.name = ''char'' THEN ''char('' + Convert(nvarchar(20), c.max_length) + '')'' '
Set @p_SQL = @p_SQL + 'WHEN t2.name = ''decimal'' THEN ''decimal('' + Convert(nvarchar(20), c.precision) + '','' + Convert(nvarchar(20), c.scale) + '')'' '
Set @p_SQL = @p_SQL + 'WHEN t2.name = ''nchar'' THEN ''nchar('' + Convert(nvarchar(20), c.max_length / 2) + '')'' '
Set @p_SQL = @p_SQL + 'WHEN t2.name = ''numeric'' THEN ''numeric('' + Convert(nvarchar(20), c.precision) + '','' + Convert(nvarchar(20), c.scale) + '')'' '
Set @p_SQL = @p_SQL + 'WHEN t2.name = ''nvarchar'' THEN '
Set @p_SQL = @p_SQL + 'CASE '
Set @p_SQL = @p_SQL + 'WHEN c.max_length <> -1 THEN ''nvarchar('' + Convert(nvarchar(20), c.max_length / 2) + '')'' ELSE ''nvarchar(max)'' END '
Set @p_SQL = @p_SQL + 'WHEN t2.name = ''varbinary'' THEN CASE WHEN c.max_length <> -1 THEN ''varbinary('' + Convert(nvarchar(20), c.max_length) + '')'' ELSE ''varbinary(max)'' END '
Set @p_SQL = @p_SQL + 'WHEN t2.name = ''varchar'' THEN CASE WHEN c.max_length <> -1 THEN ''varchar('' + Convert(nvarchar(20), c.max_length) + '')'' ELSE ''varchar(max)'' END '
Set @p_SQL = @p_SQL + 'ELSE t2.name '
Set @p_SQL = @p_SQL + 'END as ''Column_Type'', '
Set @p_SQL = @p_SQL + 'CASE WHEN c.is_nullable = 0 THEN ''NOT NULL'' ELSE ''NULL'' END as ''Column_Nullable'' '
Set @p_SQL = @p_SQL + 'FROM sys.schemas s INNER JOIN sys.tables t1 ON s.schema_id = t1.schema_id '
Set @p_SQL = @p_SQL + 'INNER JOIN sys.columns c ON t1.object_id = c.object_id '
Set @p_SQL = @p_SQL + 'INNER JOIN sys.types t2 ON c.system_type_id = t2.system_type_id '
Set @p_SQL = @p_SQL + 'WHERE t2.name <> ''sysname'' '

If @p_SchemaList <> '' and CHARINDEX(',', @p_SchemaList) = 0
Begin
Set @p_SQL = @p_SQL + 'AND s.name = ''' + @p_SchemaList + ''' '
End
Else If @p_SchemaList <> '' and CHARINDEX(',', @p_SchemaList) > 0
Begin
Set @p_SQL = @p_SQL + 'AND s.name in (''' + Replace(@p_SchemaList, ',', ''',''') + ''') '
End

If @p_TableList <> '' and CHARINDEX(',', @p_TableList) = 0
Begin
Set @p_SQL = @p_SQL + 'AND t1.name = ''' + @p_TableList + ''' '
End
Else If @p_TableList <> '' and CHARINDEX(',', @p_TableList) > 0
Begin
Set @p_SQL = @p_SQL + 'AND t1.name in (''' + Replace(@p_TableList, ',', ''',''') + ''') '
End

Set @p_SQL = @p_SQL + ') t ORDER BY t.DB_Schema, t.Table_Name ASC'

EXEC(@p_SQL)

END
GO

結果：
ALTER TABLE [dbo].[銷售業績By季] ALTER COLUMN [ID] uniqueidentifier NOT NULL
ALTER TABLE [dbo].[銷售業績By季] ALTER COLUMN [年份] char(4) NOT NULL
ALTER TABLE [dbo].[銷售業績By季] ALTER COLUMN [季] char(4) NOT NULL
ALTER TABLE [dbo].[銷售業績By季] ALTER COLUMN [月份] char(2) NOT NULL
ALTER TABLE [dbo].[銷售業績By季] ALTER COLUMN [銷售量] int NOT NULL
ALTER TABLE [dbo].[銷售業績By季] ALTER COLUMN [出貨數量] int NULL

回首頁