SQL Server如何查找表名或列名中包含空格的表和列
栏目: 数据库 · SQL Server · 发布时间: 6年前
内容简介:最近发现一个数据库中的某个表有个字段名后面包含了一个空格,这个空格引起了一些小问题,一般出现这种情况,是因为创建对象时,使用双引号或双括号的时候,由于粗心或手误多了一个空格,如下简单案例所示:那么要如何找出表名或字段名包含空格的相关信息呢? 不管是常规方法还是正则表达式,这个都会效率不高。我们可以用一个取巧的方法,就是通过字段的字符数和字节数的规律来判断,如果没有包含空格,那么列名的字节数和字符数满足下面规律(表名也是如此):
最近发现一个数据库中的某个表有个字段名后面包含了一个空格,这个空格引起了一些小问题,一般出现这种情况,是因为创建对象时,使用双引号或双括号的时候,由于粗心或手误多了一个空格,如下简单案例所示:
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
以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网
猜你喜欢:- MySQL列名中包含斜杠或者空格的处理方法
- SQLServer获取临时表所有列名或是否存在指定列名的方法
- pandas 修改 DataFrame 列名
- SQL SERVER动态列名
- 不知道列名的情况下注入
- postgresql – 在postgres中将表列名更改为大写
本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们。