• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    SQL Server查找表名或列名中包含空格的表和列实例代码

    前言

    本文主要给大家介绍的是关于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');
     
    
    clip_image001

     

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

    总结

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

    您可能感兴趣的文章:
    • SQLServer行转列实现思路记录
    • SQL Server将一列的多行内容拼接成一行的实现方法
    • Sql Server 2000 行转列的实现(横排)
    • sqlserver2005 行列转换实现方法
    • sqlserver下将数据库记录的列记录转换成行记录的方法
    • sqlserver 行列互转实现小结
    • SQLServer行列互转实现思路(聚合函数)
    • SQL Server行转列的方法解析
    上一篇:sql中的常用的字符串处理函数大全
    下一篇:SQL Server统计信息更新时采样百分比对数据预估准确性的影响详解
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    SQL Server查找表名或列名中包含空格的表和列实例代码 SQL,Server,查找,表名,或,列名,