浮雲雅築: [研究] SQL Server 資料庫 NULL 欄位調正(一)

[研究] SQL Server 資料庫 NULL 欄位調正(一)

2016-09-10

由於不斷的測試、修改，改到有點亂了，有問題請參考第二篇

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#
http://shaurong.blogspot.com/2016/09/sql-server-null-aspnet-c.html

準備把 Database 中所有允許 NULL 欄位做調整，預計

備份資料庫
所有允許 NULL 欄位都設定預設值 (DEFAULT)
欄位中 NULL 換成非 NULL 值 (不然無法把欄位格式轉成 NOT NULL)
把欄位格式轉成 NOT NULL

********************************************************************************

1.備份資料庫 (作法略)

********************************************************************************

前置瞭解狀況

A.先了解有哪幾種 Data Type

在 SQL Server Manage Studio 中執行，列出資料庫中所有用到的欄位種類

select DATA_TYPE from information_schema.columns group by DATA_TYPE

結果

bit
datetime
datetime2
int
ntext
nvarchar
uniqueidentifier
varbinary

B.評估要處理的資料表(table)有哪些

在 SQL Server Manage Studio 中執行，列出所有資料表 (table) 名稱，評估要排除的，增加 WHERE 條件去排除

select distinct TABLE_NAME from information_schema.columns order by TABLE_NAME

結果 (這是小弟的情況，排除的資料表基本上是系統資料表或某些套件建立的，留下自己建立的)

SELECT * FROM information_schema.columns
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'

********************************************************************************

2.所有欄位都設定預設值

A.所有欄位都設定預設值 (失敗，留下參考)

會出現 "不允許對系統目錄的特定更新。" 錯誤

而且基本上不建議修改系統資料表，很危險，下面僅供參考，最後放棄。

-- 解決 "不允許對系統目錄的特定更新。"
sp_configure 'allow updates',1
RECONFIGURE WITH override
go
-- 修正欄位格式 ntext, nvarchar 預設值為 ''
UPDATE information_schema.columns
SET column_default = ''
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'ntext'
OR data_type = 'nvarchar' );

-- 修正欄位格式 datetime, datetime2 預設值為 datetime()
UPDATE information_schema.columns
SET column_default = 'datetime()'
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'datetime'
OR data_type = 'datetime2' );

-- 修正欄位格式 int 預設值為 0
UPDATE information_schema.columns
SET column_default = 0
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'int' );

-- 修正欄位格式 bit, 欄位名稱 IsValid 預設值為 1
UPDATE information_schema.columns
SET column_default = 1
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'ntext'
OR data_type = 'nvarchar' )
AND ( column_name = 'IsValid' );

-- 修正欄位格式 bit, 欄位名稱不是 IsValid 預設值為 0
UPDATE information_schema.columns
SET column_default = 0
WHERE table_catalog = '資料庫名稱'
AND table_name NOT LIKE '%zDel%'
AND table_name NOT LIKE '%aspnet%'
AND table_name NOT LIKE '%__MigrationHistory%'
AND table_name NOT LIKE 'View%'
AND table_name <> '%ELMAH_Error%'
AND table_name <> 'Sessions'
AND table_name <> 'sysdiagrams'
AND is_nullable = 'YES'
AND ( data_type = 'ntext'
OR data_type = 'nvarchar' )
AND ( column_name <> 'IsValid' );
go
-- 復原 "不允許對系統目錄的特定更新。"
sp_configure 'allow updates',0
RECONFIGURE WITH override
go

B.所有欄位都設定預設值 (成功)

利用語法
ALTER TABLE [資料表名稱] ADD CONSTRAINT [DF_資料表名稱_欄位名稱] DEFAULT (欄位預設值 ) FOR [欄位名]

要記得依據自己資料庫名稱、欄位名稱做調整。

因為資料表名稱 or 欄位名稱可能是有保留字，要用 [ 和 ] 括弧。
use 資料庫名稱
TABLE_CATALOG='資料庫名稱'

use pms
go

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT ('''') FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' )

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (''getdate()'') FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' )

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='int')

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (1) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME='IsValid')

union

SELECT 'ALTER TABLE ['+ [TABLE_NAME] + '] ADD CONSTRAINT [DF_' + [TABLE_NAME] + '_' + [COLUMN_NAME] +'] DEFAULT (0) FOR [' + [COLUMN_NAME] + '];'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME<>'IsValid')

結果

未免其中一行執行失敗，後面不會再執行，每一行之後要插入 GO指令
GO 和 ALTER TABLE 可先寫到 Notepad 上，用 UltraEdit 進行取代動作 ( 很多 Editor 的取代無法包含看不到的換行字元，UltraEdit 可以做到)，最後一行要手動新增，再執行 GO

貼到 SQL Server Management Studio 中執行，注意錯誤訊息，修正之後再度執行 ( 設定過再設一次會出錯)

********************************************************************************

3.欄位中 NULL 換成非 NULL 值 (不然無法把欄位格式轉成 NOT NULL)

因為資料表名稱 or 欄位名稱可能是有保留字，要用 [ 和 ] 括弧。
use 資料庫名稱
TABLE_CATALOG='資料庫名稱'
datetime 和 datetime2 本來塞 DEFAULT (''getdate()'')，但不行，改用 DEFAULT (''0-1-1'')

use pms ;

-- 若 ntext, nvarchar 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']='''' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='ntext' or DATA_TYPE='nvarchar' )
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
union
-- 若 datetime, datetime2 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0-1-1'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='datetime' or DATA_TYPE='datetime2' )
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
union
-- 若 int 欄位允許 null ，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='int')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

union
-- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''1'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME='IsValid')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

union
-- 若 bit 欄位允許 null ，且欄位為 IsValid，先更新欄位內容
SELECT 'UPDATE ['+ [TABLE_NAME] + '] SET [' + [COLUMN_NAME] +']=''0'' WHERE [' + [COLUMN_NAME] + '] is NULL;'
FROM information_schema.columns
WHERE TABLE_CATALOG='pms'
and [TABLE_NAME] not like '%zDel%'
and [TABLE_NAME] not like '%aspnet%'
and [TABLE_NAME] not like '%__MigrationHistory%'
and [TABLE_NAME] not like 'View%'
and [TABLE_NAME] <> '%ELMAH_Error%'
and [TABLE_NAME] <> 'Sessions'
and [TABLE_NAME] <> 'sysdiagrams'
and IS_NULLABLE ='YES'
and ( DATA_TYPE='bit')
and ( COLUMN_NAME<>'IsValid')
group by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE
--order by TABLE_NAME, COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE

貼到 SQL Server Management Studio 中執行

未免其中一行執行失敗，後面不會再執行，每一行之後要插入 GO指令
GO 和 ALTER 可先寫到 Notepad 上，用 UltraEdit 進行取代動作 ( 很多 Editor 的取代無法包含看不到的換行字元，UltraEdit 可以做到)

(注意下面 UPDATE 後面要多一個空格，免得換到別的字)

最後一行要手動新增 GO

再貼回 SQL Server Management Studio 中執行

某些錯誤去看一下發生甚麼事情，可能是已經被設定過了，或是外部 KEY

********************************************************************************

4.把欄位格式轉成 NOT NULL

列出允許 NULL 欄位

select c.name, t.name + case when t.name in ('nvarchar','nchar')
then '('+ cast(c.max_length/2 as nvarchar) +')'
when t.name in ('varchar','binary', 'char', 'varbinary')
then '('+ cast(c.max_length as nvarchar) +')'
else '' end, case when c.is_nullable = 1 then 'Yes' ELSE 'No' end
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where object_id = object_id ('dbo.account') and t.name <> 'sysname'
order by column_id

產生把 NULL 改為 NOT NULL 的 SQL 語法

網路找的，有問題

select 'ALTER TABLE dbo.資料表名稱 ALTER COLUMN ' +
QUOTENAME(c.name) + ' ' + t.name +
case when t.name in ('nvarchar','nchar')
then '('+ cast(c.max_length/2 as nvarchar) +')'
when t.name in ('varchar','binary', 'char', 'varbinary')
then '('+ cast(c.max_length as nvarchar) +')'
else '' end + ' NOT NULL'
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where object_id = object_id ('dbo.資料表名稱') and t.name <> 'sysname'
order by column_id

分析

資料表名稱和欄位名稱可能用到保留字，要加 [ 和 ]，裡面不能有 dbo

nvarchar(50) NULL 改成 nvarchar(MAX) NOT NULL 會失敗，長度必須相同

另外 nvarchar(MAX) 在 sys.columns 的 max_length 是紀錄為 -1，除 2 會變成 0，會出錯

-------------------------------------------
c.max_length, t.max_length, c.name, t.name
-------------------------------------------
4 4 Id int
4 4 Priority int
8 8 CreatTime datetime
1 1 IsValid bit
1 1 Role_Admin bit
1 1 Role_Plan bit
1 1 Role_Todo bit
1 1 Role_Audit bit
-1 8000 OID nvarchar
-1 8000 Name nvarchar
-1 8000 Username nvarchar
-1 8000 Password nvarchar
-1 8000 Email nvarchar
-1 8000 Provider nvarchar
100 8000 Impersonate nvarchar
-------------------------------------------

修正為

select 'ALTER TABLE [' + 資料表名稱 + '] ALTER COLUMN ' +
QUOTENAME(c.name) + ' ' + t.name +
case when t.name in ('nvarchar','nchar') and c.max_length <>-1
then '('+ cast(c.max_length/2 as nvarchar) +')'
when t.name in ('nvarchar','nchar') and c.max_length =-1
then '(MAX)'
when t.name in ('varchar','binary', 'char', 'varbinary')
then '('+ cast(c.max_length as nvarchar) +')'
else '' end + ' NOT NULL'
from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where
object_id = object_id ('dbo.資料表名稱')
and t.name <> 'sysname'

C# ASP.NET 程式

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace WebApplication1
{
public partial class WebForm1 : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
var connectionString = "Data Source=10.3.99.192;Initial Catalog=pms;User ID=sa;Password=P@ssw0rd";

using (SqlConnection connection = new SqlConnection(
connectionString))
{
SqlCommand command = new SqlCommand(
@"select DISTINCT [TABLE_NAME] FROM information_schema.columns
WHERE TABLE_CATALOG = 'pms'

and[TABLE_NAME] not like '%zDel%'

and[TABLE_NAME] not like '%aspnet%'

and[TABLE_NAME] not like '%__MigrationHistory%'

and[TABLE_NAME] not like 'View%'

and[TABLE_NAME] <> '%ELMAH_Error%'

and[TABLE_NAME] <> 'Sessions'

and[TABLE_NAME] <> 'sysdiagrams'
; ",
connection);
connection.Open();

SqlDataReader reader = command.ExecuteReader();
string str = "";
if (reader.HasRows)
{
while (reader.Read())
{

str = @" select 'ALTER TABLE dbo." + reader[0].ToString() + @" ALTER COLUMN ' +
QUOTENAME(c.name) + ' ' + t.name +
case when t.name in ('nvarchar','nchar') and c.max_length <>-1
then '('+ cast(c.max_length/2 as nvarchar) +')'
when t.name in ('nvarchar','nchar') and c.max_length =-1
then '(MAX)'
when t.name in ('varchar','binary', 'char', 'varbinary')
then '('+ cast(c.max_length as nvarchar) +')'
else '' end + ' NOT NULL'from sys.columns c join sys.types t on c.system_type_id = t.system_type_id
where object_id = object_id('dbo." + reader[0].ToString() + @"') and t.name <> 'sysname'";
//order by column_id";
if (Literal1.Text == "")
Literal1.Text = "(" + str + ")";
else
Literal1.Text = Literal1.Text + "<br /><br />union<br /><br />" + "(" + str + ")";
}
}
else
{
//Console.WriteLine("No rows found.");
}
reader.Close();
}
}
}
}

(下圖) 產出修整改用的 SQL 語法

(下圖) 貼到 SQL Server Management 中執行，產出了可以把 NULL 欄位轉成 NOT NULL 的SQL 語法，再放到 SQL Server Management 中執行

********************************************************************************

(完)

相關

[研究] 用SQL指令找出資料庫的資料表、欄位名、PK欄位、資料筆數
http://shaurong.blogspot.tw/2016/02/sqlpk.html

[研究] SQL Server 資料庫 NULL 欄位調正(一)
http://shaurong.blogspot.com/2016/09/sql-server-null.html

[研究] SQL Server 資料庫 NULL 欄位調正(二) ASP.NET + C#
http://shaurong.blogspot.com/2016/09/sql-server-null-aspnet-c.html

浮雲雅築

2016年9月13日星期二

[研究] SQL Server 資料庫 NULL 欄位調正(一)

沒有留言:

張貼留言

2016年9月13日 星期二

[研究] SQL Server 資料庫 NULL 欄位調正(一)

沒有留言:

張貼留言

2016年9月13日星期二