SQL Server查找表名或列名中包含空格的表和列实例代码_SqlServer_数据库_码蚁之家_www.codes51.com
返回首页
专题
网络编程
ASP教程 .NET教程 PHP教程 JSP教程 C#教程 Java教程 Delphi教程 VB教程 C/C++教程 Android开发 IOS开发 Windows Phone开发 Python教程 Ruby教程 移动开发 其他编程教程
网页制作
HTML教程 CSS教程 Dreamweaver教程 FrontPages教程 Javascript教程 web前端
数据库
SqlServer MySql Oracle Access DB2 SQLite 其他数据库
图形设计
photoshop教程 Fireworks教程 CorelDraw教程 Illustrator教程 AutoCad教程 FLASH教程
操作系统
Windows xp教程 Windows 7教程 Windows 8教程 Windows 2003教程 Windows Server 2008教程 Linux教程 Windows 10
网站运营
建站经验 SEO优化 站长心得 网赚技巧 网站推广 站长故事
手机学院
手机速递 安卓教程 iphone教程 手机评测 手机技巧 手机知识 手机应用 手机游戏 手机导购
网店宝典
开店指导 开店经验 网店装修 网店推广 网店seo 网购技巧
软件教程
办公软件 系统工具 媒体工具 压缩工具 图文处理 文件管理
范文之家
自我介绍 自我鉴定 写作模板 合同范本 工作总结 贺词祝福语 演讲致辞 思想汇报 入党申请书 实习报告 心得体会 工作计划 简历模板 工作报告 导游词 评语寄语 口号大全 策划书范文
信息工程
软件工程 企业开发 系统运维 软件测试
移民之家
移民动态 移民政策 移民百科 移民生活 技术移民 投资移民
知识大全
母婴 数码 摄影 装修 美文 常识 时尚 婚嫁 美食 养生 旅游 兴趣 职场 教育 文学 健康
问答大全
电脑网络 手机数码 QQ专区 生活 游戏 体育运动 娱乐明星 休闲爱好 文化艺术 社会民生 教育科学 健康医疗 商业理财 情感家庭 地区问题 其他
编程问答
IOS Android .NET Java C/C++ Delphi VC/MFC 其他语言 PHP MSSQL MYSQL Oracle 其他数据库 Web开发 Windows Linux 硬件/嵌入开发 网络通信 移动开发 云计算 企业IT 游戏开发
笑话大全
幽默笑话 爱情笑话 成人笑话 校园笑话 爆笑笑话 综合笑话 古代笑话 现代笑话 国外笑话

SQL Server查找表名或列名中包含空格的表和列实例代码(1/2)

来源:互联网  时间:2018/9/24 0:49:54

前言

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

总结

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


上一篇sql server中的任务调度与CPU深入讲解
下一篇sql中的常用的字符串处理函数大全
明星图片
相关文章
《 SQL Server查找表名或列名中包含空格的表和列实例代码》由码蚁之家搜集整理于网络,
联系邮箱:mxgf168#qq.com(#改为@)