SQLServer查找表名或列名中包含空格的表和列实例代码
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了SQLServer查找表名或列名中包含空格的表和列实例代码,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含5652字,纯文字阅读大概需要9分钟。
内容图文
![SQLServer查找表名或列名中包含空格的表和列实例代码](/upload/InfoBanner/zyjiaocheng/586/fc5fc80ac1af4b6caa4634c9380118f5.jpg)
前言
本文主要给大家介绍的是关于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行转列的方法解析
内容总结
以上是互联网集市为您收集整理的SQLServer查找表名或列名中包含空格的表和列实例代码全部内容,希望文章能够帮你解决SQLServer查找表名或列名中包含空格的表和列实例代码所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。