最近发现一个数据库中的某个表有个字段名后面包含了一个空格，这个空格引起了一些小问题，一般出现这种情况，是因为创建对象时，使用双引号或双括号的时候，由于粗心或手误多了一个空格，如下简单案例所示：
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
)
原理是这样的，保存这些元数据的字段类型为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
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;
另外，对应表名而言，可以使用下面脚本。在此略过，不做过多介绍！
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
