前言
本文主要給大家介紹的是關(guān)于SQL Server查找包含空格的表和列的相關(guān)內(nèi)容，為什么會(huì)有這篇文章，是因?yàn)樽罱l(fā)現(xiàn)一個(gè)數(shù)據(jù)庫(kù)中的某個(gè)表有個(gè)字段名后面包含了一個(gè)空格，這個(gè)空格引起了一些小問(wèn)題，一般出現(xiàn)這種情況，是因?yàn)閯?chuàng)建對(duì)象時(shí)，使用雙引號(hào)或雙括號(hào)的時(shí)候，由于粗心或手誤多了一個(gè)空格，如下簡(jiǎn)單案例所示：
USE TEST;
GO
--表TEST_COLUMN中兩個(gè)字段都包含有空格
CREATE TABLE TEST_COLUMN
(
"ID " INT IDENTITY (1,1),
[Name ] VARCHAR(32),
[Normal] VARCHAR(32)
);
GO
--表[TEST_TABLE ]中包含空格， 里面對(duì)應(yīng)三個(gè)字段，一個(gè)前面包含空格（后面詳細(xì)闡述），一個(gè)字段中間包含空格，一個(gè)字段后面包含空格。
CREATE TABLE [TEST_TABLE ]
(
[ F_NAME] NVARCHAR(32),
[M NAME] NVARCHAR(32),
[L_NAME ] NVARCHAR(32)
)
GO
實(shí)現(xiàn)方法：
那么要如何找出表名或字段名包含空格的相關(guān)信息呢？ 不管是常規(guī)方法還是正則表達(dá)式，這個(gè)都會(huì)效率不高。我們可以用一個(gè)取巧的方法，就是通過(guò)字段的字符數(shù)和字節(jié)數(shù)的規(guī)律來(lái)判斷，如果沒有包含空格，那么列名的字節(jié)數(shù)和字符數(shù)滿足下面規(guī)律（表名也是如此）：
DATALENGTH(name) = 2* LEN(name)
SELECT name ,
DATALENGTH(name) AS NAME_BYTES ,
LEN(name) AS NAME_CHARACTER
FROM sys.columns
WHERE object_id = OBJECT_ID('TEST_COLUMN');
clip_image001
原理是這樣的，保存這些元數(shù)據(jù)的字段類型為sysname ，其實(shí)這個(gè)系統(tǒng)數(shù)據(jù)類型，用于定義表列、變量以及存儲(chǔ)過(guò)程的參數(shù)，是nvarchar(128)的同義詞。所以一個(gè)字母占2個(gè)字節(jié)。那么我們安裝這個(gè)規(guī)律寫了一個(gè)腳本來(lái)檢查數(shù)據(jù)中那些表名或字段名包含空格。方便巡檢。如下測(cè)試所示
IF OBJECT_ID('tempdb.dbo.#TabColums') IS NOT NULL
DROP TABLE dbo.#TabColums;
CREATE TABLE #TabColums
(
object_id INT ,
column_id INT
)
INSERT INTO #TabColums
SELECT object_id ,
column_id
FROM sys.columns
WHERE DATALENGTH(name) != LEN(name) * 2
SELECT
TL.name AS TableName,
C.Name AS FieldName,
T.Name AS DataType,
DATALENGTH(C.name) AS COLUMN_DATALENGTH,
LEN(C.name) AS COLUMN_LENGTH,
CASE WHEN C.Max_Length = -1 THEN 'Max' ELSE CAST(C.Max_Length AS VARCHAR) END AS Max_Length,
CASE WHEN C.is_nullable = 0 THEN '×' ELSE N'√' END AS Is_Nullable,
C.is_identity,
ISNULL(M.text, '') AS DefaultValue,
ISNULL(P.value, '') AS FieldComment
FROM sys.columns C
INNER JOIN sys.types T ON C.system_type_id = T.user_type_id
LEFT JOIN dbo.syscomments M ON M.id = C.default_object_id
LEFT JOIN sys.extended_properties P ON P.major_id = C.object_id AND C.column_id = P.minor_id
INNER JOIN sys.tables TL ON TL.object_id = C.object_id
INNER JOIN #TabColums TC ON C.object_id = TC.object_id AND c.column_id = TC.column_id
ORDER BY C.Column_Id ASC
那么為什么表名TEST_TABLE的三個(gè)字段里面，前面包含空格與與中間包含空格都識(shí)別不出來(lái)呢？這個(gè)與數(shù)據(jù)庫(kù)的LEN函數(shù)有關(guān)系，LEN函數(shù)返回指定字符串表達(dá)式的字符數(shù)，其中
不包含尾隨空格。所以這個(gè)腳本是無(wú)法排查表名或字段名前面包含空格的。如果要排查這種情況，就需要使用下面SQL腳本（中間包含空格在此略過(guò)，這個(gè)不符合命名規(guī)則）：
SELECT * FROM sys.columns WHERE NAME LIKE ' %' --字段前面包含空格。
其實(shí)到了這一步，還沒有完，如果一個(gè)實(shí)例，里面有十幾個(gè)數(shù)據(jù)庫(kù)，那么使用上面這個(gè)腳本，我要切換數(shù)據(jù)庫(kù)，執(zhí)行十幾次，對(duì)于我這種懶人來(lái)說(shuō)，我覺得無(wú)法忍受的。那么必須寫
一個(gè)腳本，將所有數(shù)據(jù)庫(kù)全部檢查完。本來(lái)想用sys.sp_MSforeachdb，但是這個(gè)內(nèi)部存儲(chǔ)過(guò)程有一些限制，遂寫了下面腳本。
DECLARE @db_name NVARCHAR(32);
DECLARE @sql_text NVARCHAR(MAX);
DECLARE @db TABLE
(
database_name NVARCHAR(64)
);
IF OBJECT_ID('tempdb.dbo.#TabColums') IS NOT NULL
DROP TABLE dbo.#TabColums;
CREATE TABLE #TabColums
(
object_id INT ,
column_id INT
);
INSERT INTO @db
SELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id !=2;
WHILE (1=1)
BEGIN
SELECT TOP 1 @db_name = database_name FROM @db ORDER BY 1;
IF @@ROWCOUNT = 0 RETURN;
SET @sql_text =N'USE ' + @db_name +';
TRUNCATE TABLE #TabColums;
INSERT INTO #TabColums
SELECT object_id ,
column_id
FROM sys.columns
WHERE DATALENGTH(name) != LEN(name) * 2;
SELECT ''' + @db_name + ''' AS DatabaseName,
TL.name AS TableName ,
C.name AS FieldName ,
T.name AS DataType ,
DATALENGTH(C.name) AS COLUMN_DATALENGTH ,
LEN(C.name) AS COLUMN_LENGTH ,
CASE WHEN C.max_length = -1 THEN ''Max''
ELSE CAST(C.max_length AS VARCHAR)
END AS Max_Length ,
CASE WHEN C.is_nullable = 0 THEN ''×''
ELSE ''√''
END AS Is_Nullable ,
C.is_identity ,
ISNULL(M.text, '''') AS DefaultValue ,
ISNULL(P.value, '''') AS FieldComment
FROM sys.columns C
INNER JOIN sys.types T ON C.system_type_id = T.user_type_id
LEFT JOIN dbo.syscomments M ON M.id = C.default_object_id
LEFT JOIN sys.extended_properties P ON P.major_id = C.object_id
AND C.column_id = P.minor_id
INNER JOIN sys.tables TL ON TL.object_id = C.object_id
INNER JOIN #TabColums TC ON C.object_id = TC.object_id
AND C.column_id = TC.column_id
ORDER BY C.column_id ASC;';
PRINT(@sql_text);
EXECUTE(@sql_text);
DELETE FROM @db WHERE database_name=@db_name;
END
TRUNCATE TABLE #TabColums;
DROP TABLE #TabColums;
另外，對(duì)應(yīng)表名而言，可以使用下面腳本。在此略過(guò)，不做過(guò)多介紹！
DECLARE @db_name NVARCHAR(32);
DECLARE @sql_text NVARCHAR(MAX);
DECLARE @db TABLE
(
database_name NVARCHAR(64)
);
INSERT INTO @db
SELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id !=2;
WHILE (1=1)
BEGIN
SELECT TOP 1 @db_name = database_name FROM @db ORDER BY 1;
IF @@ROWCOUNT = 0 RETURN;
SET @sql_text =N'USE ' + @db_name +';
SELECT ''' + @db_name + ''' as database_name, name,
DATALENGTH(name) as table_name_bytes,
LEN(name) as table_name_character,
type_desc,create_date,modify_date
FROM sys.tables
WHERE DATALENGTH(name) != LEN(name) * 2;
';
PRINT(@sql_text);
EXECUTE(@sql_text);
DELETE FROM @db WHERE database_name=@db_name;
END
總結(jié)
以上就是這篇文章的全部?jī)?nèi)容了，希望本文的內(nèi)容對(duì)大家的學(xué)習(xí)或者工作具有一定的參考學(xué)習(xí)價(jià)值，如果有疑問(wèn)大家可以留言交流，謝謝大家對(duì)腳本之家的支持。
您可能感興趣的文章:- SQLServer行轉(zhuǎn)列實(shí)現(xiàn)思路記錄
- SQL Server將一列的多行內(nèi)容拼接成一行的實(shí)現(xiàn)方法
- Sql Server 2000 行轉(zhuǎn)列的實(shí)現(xiàn)（橫排）
- sqlserver2005 行列轉(zhuǎn)換實(shí)現(xiàn)方法
- sqlserver下將數(shù)據(jù)庫(kù)記錄的列記錄轉(zhuǎn)換成行記錄的方法
- sqlserver 行列互轉(zhuǎn)實(shí)現(xiàn)小結(jié)
- SQLServer行列互轉(zhuǎn)實(shí)現(xiàn)思路（聚合函數(shù)）
- SQL Server行轉(zhuǎn)列的方法解析
