SQL Server如何查找表名或列名中包含空格的表和列

最近發現一個資料庫中的某個表有個欄位名後麵包含了一個空格，這個空格引起了一些小問題，一般出現這種情況，是因為創建對象時，使用雙引號或雙括弧的時候，由於粗心或手誤多了一個空格，如下簡單案例所示：

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

那麼要如何找出表名或欄位名包含空格的相關信息呢？ 不管是常規方法還是正則表達式，這個都會效率不高。我們可以用一個取巧的方法，就是通過欄位的字元數和位元組數的規律來判斷，如果沒有包含空格，那麼列名的位元組數和字元數滿足下麵規律（表名也是如此）：

    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');

原理是這樣的，保存這些元數據的欄位類型為sysname ，其實這個系統數據類型，用於定義表列、變數以及存儲過程的參數，是nvarchar(128)的同義詞。所以一個字母占2個位元組。那麼我們安裝這個規律寫了一個腳本來檢查數據中那些表名或欄位名包含空格。方便巡檢。如下測試所示

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的三個欄位裡面，前麵包含空格與與中間包含空格都識別不出來呢？這個與資料庫的LEN函數有關係，LEN函數返回指定字元串表達式的字元數，其中

不包含尾隨空格。所以這個腳本是無法排查表名或欄位名前麵包含空格的。如果要排查這種情況，就需要使用下麵SQL腳本（中間包含空格在此略過，這個不符合命名規則）：

SELECT * FROM sys.columns WHERE NAME LIKE ' %'  --欄位前麵包含空格。

其實到了這一步，還沒有完，如果一個實例，裡面有十幾個資料庫，那麼使用上面這個腳本，我要切換資料庫，執行十幾次，對於我這種懶人來說，我覺得無法忍受的。那麼必須寫

一個腳本，將所有資料庫全部檢查完。本來想用sys.sp_MSforeachdb，但是這個內部存儲過程有一些限制，遂寫了下麵腳本。

DECLARE @db_name  NVARCHAR(32);

DECLARE @sql_text NVARCHAR(MAX);

DECLARE @db TABLE 

(

    database_name  NVARCHAR(64)