SQL Server查找表名或列名中包含空格的表和列实例代码
作者:潇湘隐者 发布时间:2024-01-17 03:15:33
前言
本文主要给大家介绍的是关于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
来源:http://www.cnblogs.com/kerrycode/p/9549001.html
猜你喜欢
- 我正在开发一个档案管理系统,需要从数据库中同时调出图像及相关的文字说明,可我只做到了单纯地显示图片,像有一个数据库CHUNFENG,在数据库
- 用Flask处理图片非常容易,这一篇学习一下图片的上传、下载及展示。还是以实例代码演示为主。首先,实现一个简单的上传(过程中未做任何处理,只
- aspjpeg组件实现加水印函数的调用方法: <%printwater "/images/水印图片.gif",&q
- 目的两年前曾为了租房做过一个找房机器人 「爬取豆瓣租房并定时推送到微信」,维护一段时间后就荒废了。当时因为代码比较简单一直没开源,现在想想说
- 有时你提交过代码之后,发现一个地方改错了,你下次提交时不想保留上一次的记录;或者你上一次的commit message的描述有误,这时候你可
- 如下所示:python 设置值import pandas as pdimport numpy as npdates = pd.date_ra
- 我们经常需要在数据库上建立有权限的用户,该用户只能去操作某个特定的数据库(比如该用户只能去读,去写等等),那么我们应该怎么在sqlserve
- 以前的服务器,由于内存的价格过高,一般配置的内存不是很多,超过4GB的当然就不多了.现在的服务器,配置超过4GB就很多,在配作SQL 数据库
- 条件语句主要有三种形式:分别为if语句、if...else语句和if...elif...else 语句1.if语句条件语句中常用的比较运算符
- 高层的期望“3个月内,我希望网站能增加X注册用户,每日的独立IP到Y,网站盈利达到Z……”作为一个团队的领袖或者产品负责人,这样的期望是根据
- 你是否发现,在浩如烟海的应用程序堆里,具有漂亮图标和清爽名字的 App 更容易被用户喜爱。作为开发者,面对这自己的作品,能否自问一句:“从图
- 自从接触python以后就想着爬pixiv,之前因为梯子有点问题就一直搁置,最近换了个梯子就迫不及待试了下。爬虫无非request获取htm
- 类和对象类和函数一样都是Python中的对象。当一个类定义完成之后,Python将创建一个“类对象”并将其赋值给一个同名变量。类是type类
- 新标准的熟悉和入门内容: 还在用 HTML 编写文档?如果是的话,就不符合当前标准了。2000 年&
- 以下所描述无理论依据,纯属经验谈。MySQL使用4.1以上版本,管他是什么字符集,一律使用默认。不用去设置MySQL。然后举个使用GB231
- 一、MySQL中如何表示当前时间?其实,表达方式还是蛮多的,汇总如下:CURRENT_TIMESTAMPCURRENT_TIMESTAMP(
- 什么是Three.js? 如果你正在读这篇文章,你可能对Three.js有一定的了解,那我们来简单地介绍下Three.js是什么.Three
- 很多人可能认为门户网站首页设计只是把一些导航、资讯内容和广告堆积起来摆放得好看就可以了,虽然这个观点也并不是完全错误的,确实门户网站首页是由
- 红包分配算法代码实现发给大家,祝红包大丰收!#coding=gbkimport randomimport sys#print random.
- zip即将多个可迭代对象组合为一个可迭代的对象,每次组合时都取出对应顺序的对象元素组合为元组,直到最少的对象中元素全部被组合,剩余的其他对象