sqlserver语句显示表字段(SQL Server查找表名或列名中包含空格的表和列实例代码)
sqlserver语句显示表字段
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 Server2012数据库备份和还原的教程)
- sqlserverlog原理(sql server中错误日志errorlog的深入讲解)
- sqlserver数据库同步复制(SqlServer将数据库中的表复制到另一个数据库)
- sqlserver数字格式化五位小数(详细分析sqlserver中的小数类型float和decimal)
- sqlserver去除空格函数(sqlServer实现去除字符串空格)
- sqlserver索引介绍(浅析SQL Server的聚焦使用索引和查询执行计划)
- sqlserver新建连接服务器卡住(SQL Server成功与服务器建立连接但是在登录过程中发生错误的快速解决方案)
- sqlserver游标使用场景(解析SQL Server聚焦移除Bookmark Lookup、RID Lookup、Key Lookup)
- SQLServer日期函数总结案例详解(SQLServer日期函数总结案例详解)
- 如何使用sqlserver建立数据表(SqlServer编写数据库表的操作方式建库、建表、修改语句)
- sqlserver函数条件判断(Sql Server 开窗函数Over的使用实例详解)
- sqlserver 使用SSMS运行sql脚本的六种方法(sqlserver 使用SSMS运行sql脚本的六种方法)
- sqlserverselect选择两个参数(SQL SERVER中SELECT和SET赋值相同点与不同点推荐)
- sql怎么取随机数(SQLServer 使用rand获取随机数的操作)
- sqlserver分组查询(sql server如何利用开窗函数over进行分组统计)
- sqlservercount函数怎么用(Sql Server中Substring函数的用法实例解析)
- 靳东新剧《精英律师》定档,众星云集,这剧可追(靳东新剧精英律师定档)
- 精英律师 廖佳敏封印恋情曝光,顾婕马失前蹄 你个老不死的(廖佳敏封印恋情曝光)
- 以家人之名广受好评,剧情生动引起观众共鸣,演员张新成圈粉无数(以家人之名广受好评)
- 三兄妹感情再遇波折,人设接连崩塌 《以家人之名》剧情猜不透(三兄妹感情再遇波折)
- 《小敏家》金波想要复婚 这只是他圈套的第1步,更可恶的在后面(小敏家金波想要复婚)
- 小敏家 剧情离谱一锅乱炖,但他们俩绝对是这部剧的一大 亮点(剧情离谱一锅乱炖)
热门推荐
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9