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
总结
以上就是这篇文章的全部内容了,但愿本文的内容对大家的学习或者工作拥有一定的参考学习价值,要是有疑难大家可以留言交换,感谢大家对我们的支撑。