俄罗斯贵宾会-俄罗斯贵宾会官网
做最好的网站

SQL Server如何查找表名或列名中包含空格的表和列俄罗斯贵宾会

 

前言

最近发现一个数据库中的某个表有个字段名后面包含了一个空格,这个空格引起了一些小问题,一般出现这种情况,是因为创建对象时,使用双引号或双括号的时候,由于粗心或手误多了一个空格,如下简单案例所示:

本文主要给大家介绍的是关于SQL Server查找包含空格的表和列的相关内容,为什么会有这篇文章,是因为最近发现一个数据库中的某个表有个字段名后面包含了一个空格,这个空格引起了一些小问题,一般出现这种情况,是因为创建对象时,使用双引号或双括号的时候,由于粗心或手误多了一个空格,如下简单案例所示:

 

USE TEST;GO --表TEST_COLUMN中两个字段都包含有空格CREATE TABLE TEST_COLUMN ( "ID " INT IDENTITY , [Name ] VARCHAR, [Normal] VARCHAR;GO --表[TEST_TABLE ]中包含空格, 里面对应三个字段,一个前面包含空格,一个字段中间包含空格,一个字段后面包含空格。CREATE TABLE [TEST_TABLE ]( [ F_NAME] NVARCHAR, [M NAME] NVARCHAR, [L_NAME ] NVARCHARGO

 

实现方法:

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

SELECT name , DATALENGTH AS NAME_BYTES , LEN AS NAME_CHARACTERFROM sys.columnsWHERE object_id = OBJECT_ID; clip_image001

 

原理是这样的,保存这些元数据的字段类型为sysname ,其实这个系统数据类型,用于定义表列、变量以及存储过程的参数,是nvarchar的同义词。所以一个字母占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 #TabColumsSELECT object_id , column_idFROM sys.columnsWHERE DATALENGTH * 2 SELECT TL.name AS TableName, C.Name AS FieldName, T.Name AS DataType, DATALENGTH AS COLUMN_DATALENGTH, LEN 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 AS DefaultValue, ISNULL 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脚本:

 

SELECT * FROM sys.columns WHERE NAME LIKE ' %' --字段前面包含空格。

    DATALENGTH(name) = 2* LEN(name)

其实到了这一步,还没有完,如果一个实例,里面有十几个数据库,那么使用上面这个脚本,我要切换数据库,执行十几次,对于我这种懒人来说,我觉得无法忍受的。那么必须写

 

一个脚本,将所有数据库全部检查完。本来想用sys.sp_MSforeachdb,但是这个内部存储过程有一些限制,遂写了下面脚本。

俄罗斯贵宾会, 

DECLARE @db_name NVARCHAR;DECLARE @sql_text NVARCHAR; DECLARE @db TABLE ( database_name NVARCHAR; 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 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 * 2; SELECT ''' + @db_name + ''' AS DatabaseName, TL.name AS TableName , C.name AS FieldName , T.name AS DataType , DATALENGTH AS COLUMN_DATALENGTH , LEN 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 AS DefaultValue , ISNULL 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; EXECUTE; DELETE FROM @db WHERE database_name=@db_name; END TRUNCATE TABLE #TabColums;DROP TABLE #TabColums;

 

另外,对应表名而言,可以使用下面脚本。在此略过,不做过多介绍!

SELECT  name ,

        DATALENGTH(name) AS NAME_BYTES ,

        LEN(name)         AS NAME_CHARACTER

FROM    sys.columns

WHERE   object_id = OBJECT_ID('TEST_COLUMN');
DECLARE @db_name NVARCHAR;DECLARE @sql_text NVARCHAR; DECLARE @db TABLE ( database_name NVARCHAR; INSERT INTO @dbSELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id !=2; WHILE 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 as table_name_bytes, LEN as table_name_character, type_desc,create_date,modify_date FROM sys.tables WHERE DATALENGTH * 2; '; PRINT; EXECUTE; DELETE FROM @db WHERE database_name=@db_name; END

 

总结

俄罗斯贵宾会 1

以上就是这篇文章的全部内容了,希望本文的内容对大家的学习或者工作具有一定的参考学习价值,如果有疑问大家可以留言交流,谢谢大家对脚本之家的支持。

 

 

原理是这样的,保存这些元数据的字段类型为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

 

 

 

俄罗斯贵宾会 2

本文由俄罗斯贵宾会发布于数据库,转载请注明出处:SQL Server如何查找表名或列名中包含空格的表和列俄罗斯贵宾会

您可能还会对下面的文章感兴趣: