SQL Server查找表名或列名中包含空格的表和列实例代码

这篇文章主要给大家介绍了关于SQL Server如何查找表名或列名中包含空格的表和列的相关资料,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面来一起看看吧

前言

本文主要给大家介绍的是关于SQL Server查找包含空格的表和列的相关内容,为什么会有这篇文章,是因为最近发现一个数据库中的某个表有个字段名后面包含了一个空格,这个空格引起了一些小问题,一般出现这种情况,是因为创建对象时,使用双引号或双括号的时候,由于粗心或手误多了一个空格,如下简单案例所示:

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

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

实现方法:

那么要如何找出表名或字段名包含空格的相关信息呢? 不管是常规方法还是正则表达式,这个都会效率不高。我们可以用一个取巧的方法,就是通过字段的字符数和字节数的规律来判断,如果没有包含空格,那么列名的字节数和字符数满足下面规律(表名也是如此):

?

1

DATALENGTH(name) = 2* LEN(name)

?

1

2

3

4

5

6

7

8

SELECT name ,

DATALENGTH(name) AS NAME_BYTES ,

LEN(name) AS NAME_CHARACTER

FROM sys.columns

WHERE object_id = OBJECT_ID('TEST_COLUMN');

clip_image001

SQL Server查找表名或列名中包含空格的表和列实例代码

原理是这样的,保存这些元数据的字段类型为sysname ,其实这个系统数据类型,用于定义表列、变量以及存储过程的参数,是nvarchar(128)的同义词。所以一个字母占2个字节。那么我们安装这个规律写了一个脚本来检查数据中那些表名或字段名包含空格。方便巡检。如下测试所示

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

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

SQL Server查找表名或列名中包含空格的表和列实例代码

那么为什么表名TEST_TABLE的三个字段里面,前面包含空格与与中间包含空格都识别不出来呢?这个与数据库的LEN函数有关系,LEN函数返回指定字符串表达式的字符数,其中

不包含尾随空格。所以这个脚本是无法排查表名或字段名前面包含空格的。如果要排查这种情况,就需要使用下面SQL脚本(中间包含空格在此略过,这个不符合命名规则):

?

1

SELECT * FROM sys.columns WHERE NAME LIKE ' %' –字段前面包含空格。

SQL Server查找表名或列名中包含空格的表和列实例代码

其实到了这一步,还没有完,如果一个实例,里面有十几个数据库,那么使用上面这个脚本,我要切换数据库,执行十几次,对于我这种懒人来说,我觉得无法忍受的。那么必须写

一个脚本,将所有数据库全部检查完。本来想用sys.sp_MSforeachdb,但是这个内部存储过程有一些限制,遂写了下面脚本。

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

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;

另外,对应表名而言,可以使用下面脚本。在此略过,不做过多介绍!

?

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

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

版权声明:本文(即:原文链接:https://www.qin1qin.com/catagory/24614/)内容由互联网用户自发投稿贡献,该文观点仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌抄袭侵权/违法违规的内容, 请发送邮件至 630367839@qq.com 举报,一经查实,本站将立刻删除。

(0)
上一篇 2022年9月22日 上午11:18
下一篇 2022年9月22日 上午11:18
软件定制开发公司

相关阅读

发表回复

登录后才能评论
通知:禁止投稿所有关于虚拟货币,币圈类相关文章,发现立即永久封锁账户ID!