SQL Server如何查找表名或列名中包含空格的表和列
程序员文章站
2022-08-30 23:00:36
最近发现一个数据库中的某个表有个字段名后面包含了一个空格，这个空格引起了一些小问题，一般出现这种情况，是因为创建对象时，使用双引号或双括号的时候，由于粗心或手误多了一个空格，如下简单案例所示： USE TEST;GO --表TEST_COLUMN中两个字段都包含有空格CREATE TABLE TES... ......
最近发现一个数据库中的某个表有个字段名后面包含了一个空格，这个空格引起了一些小问题，一般出现这种情况，是因为创建对象时，使用双引号或双括号的时候，由于粗心或手误多了一个空格，如下简单案例所示：
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)
);
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