前言
本文主要給大家介紹的是關于SQL Server查找包含空格的表和列的相關內容，為什么會有這篇文章，是因為最近發現一個數據庫中的某個表有個字段名后面包含了一個空格，這個空格引起了一些小問題，一般出現這種情況，是因為創建對象時，使用雙引號或雙括號的時候，由于粗心或手誤多了一個空格，如下簡單案例所示：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
|
USE TEST;GO --表TEST_COLUMN中兩個字段都包含有空格CREATE TABLE TEST_COLUMN ( "ID " INT IDENTITY (1,1), [Name ] VARCHAR(32), [Normal] VARCHAR(32));GO --表[TEST_TABLE ]中包含空格， 里面對應三個字段，一個前面包含空格（后面詳細闡述），一個字段中間包含空格，一個字段后面包含空格。CREATE TABLE [TEST_TABLE ]( [ F_NAME] NVARCHAR(32), [M NAME] NVARCHAR(32), [L_NAME ] NVARCHAR(32))GO |
實現方法：
那么要如何找出表名或字段名包含空格的相關信息呢？ 不管是常規方法還是正則表達式，這個都會效率不高。我們可以用一個取巧的方法，就是通過字段的字符數和字節數的規律來判斷，如果沒有包含空格，那么列名的字節數和字符數滿足下面規律（表名也是如此）：
|
1
|
DATALENGTH(name) = 2* LEN(name) |
|
1
2
3
4
5
6
7
8
|
SELECT name , DATALENGTH(name) AS NAME_BYTES , LEN(name) AS NAME_CHARACTERFROM sys.columnsWHERE object_id = OBJECT_ID('TEST_COLUMN'); clip_image001 |
原理是這樣的，保存這些元數據的字段類型為sysname ，其實這個系統數據類型，用于定義表列、變量以及存儲過程的參數，是nvarchar(128)的同義詞。所以一個字母占2個字節。那么我們安裝這個規律寫了一個腳本來檢查數據中那些表名或字段名包含空格。方便巡檢。如下測試所示
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
IF OBJECT_ID('tempdb.dbo.#TabColums') IS NOT NULL DROP TABLE dbo.#TabColums; CREATE TABLE #TabColums( object_id INT , column_id INT) INSERT INTO #TabColumsSELECT object_id , column_idFROM sys.columnsWHERE 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 CINNER JOIN sys.types T ON C.system_type_id = T.user_type_idLEFT JOIN dbo.syscomments M ON M.id = C.default_object_idLEFT 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_idINNER JOIN #TabColums TC ON C.object_id = TC.object_id AND c.column_id = TC.column_idORDER BY C.Column_Id ASC |
那么為什么表名TEST_TABLE的三個字段里面，前面包含空格與與中間包含空格都識別不出來呢？這個與數據庫的LEN函數有關系，LEN函數返回指定字符串表達式的字符數，其中
不包含尾隨空格。所以這個腳本是無法排查表名或字段名前面包含空格的。如果要排查這種情況，就需要使用下面SQL腳本（中間包含空格在此略過，這個不符合命名規則）：
|
1
|
SELECT * FROM sys.columns WHERE NAME LIKE ' %' --字段前面包含空格。 |
其實到了這一步，還沒有完，如果一個實例，里面有十幾個數據庫，那么使用上面這個腳本，我要切換數據庫，執行十幾次，對于我這種懶人來說，我覺得無法忍受的。那么必須寫
一個腳本，將所有數據庫全部檢查完。本來想用sys.sp_MSforeachdb，但是這個內部存儲過程有一些限制，遂寫了下面腳本。
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
|
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 @dbSELECT 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; |
另外，對應表名而言，可以使用下面腳本。在此略過，不做過多介紹！
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
|
DECLARE @db_name NVARCHAR(32);DECLARE @sql_text NVARCHAR(MAX); DECLARE @db TABLE( database_name NVARCHAR(64)); INSERT INTO @dbSELECT 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 |
總結
以上就是這篇文章的全部內容了，希望本文的內容對大家的學習或者工作具有一定的參考學習價值，如果有疑問大家可以留言交流，謝謝大家對服務器之家的支持。
原文鏈接：http://www.cnblogs.com/kerrycode/p/9549001.html
