Shulou(Shulou.com)05/31 Report--

This article is about how SQL Server looks for tables and columns with spaces in table or column names. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

As shown in the following example:

USE TEST;GO-- two fields in the table TEST_COLUMN contain spaces CREATE TABLE TEST_COLUMN ("ID" INT IDENTITY (1 TEST_TABLE 1), [Name] VARCHAR (32), [Normal] VARCHAR (32)); GO-the table [TEST_TABLE] contains spaces, corresponding to three fields, one containing spaces in front (described in more detail later), one containing spaces in the middle, and one containing spaces after the other. CREATE TABLE [TEST_TABLE] ([F_NAME] NVARCHAR (32), [M NAME] NVARCHAR (32), [L_NAME] NVARCHAR (32)) GO

Implementation method:

So how do you find out that a table or field name contains spaces? Whether it's a conventional method or a regular expression, this is inefficient. We can use a tricky method to judge by the law of the number of characters and bytes of the field. If there are no spaces, then the number of bytes and characters of the column name satisfies the following rule (the same is true of the table name):

DATALENGTH (name) = 2 * LEN (name) SELECT name, DATALENGTH (name) AS NAME_BYTES, LEN (name) AS NAME_CHARACTERFROM sys.columnsWHERE object_id = OBJECT_ID ('TEST_COLUMN'); clip_image001

The principle is that the field type that holds this metadata is sysname. In fact, this system data type, which is used to define table columns, variables, and parameters of stored procedures, is synonymous with nvarchar. So one letter takes up two bytes. So we install this rule and write a script to check which table or field names in the data contain spaces. It is convenient for inspection. As shown in the following test

IF OBJECT_ID ('tempdb.dbo.#TabColums') IS NOT NULL DROP TABLE dbo.#TabColums CREATE TABLE # TabColums (object_id INT, column_id INT) INSERT INTO # TabColumsSELECT object_id, column_idFROM sys.columnsWHERE 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 CINNER JOIN sys.types T ON C.system_type_id = T.user_type_idLEFT JOIN dbo.syscomments M ON M.id = C.default_object_idLEFT 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_idINNER JOIN # TabColums TC ON C.object_id = TC.object_id AND c.column_id = TC.column_idORDER BY C.Column_Id ASC

So why can't you recognize spaces in front of and spaces in the middle of the three fields of the table name TEST_TABLE? This is related to the LEN function of the database, which returns the number of characters of the specified string expression, where

Does not contain trailing spaces. So this script cannot troubleshoot table or field names with spaces in front of them. If you want to troubleshoot this situation, you need to use the following SQL script (the space in the middle is skipped here, which does not conform to the naming convention):

SELECT * FROM sys.columns WHERE NAME LIKE'%'- the field is preceded by a space.

In fact, at this stage, it is not over yet. If there are more than a dozen databases in an instance, then using the above script, I will switch the database and execute it more than a dozen times. For lazy people like me, I find it unbearable. Then it must be written.

A script that checks all databases. I wanted to use sys.sp_MSforeachdb, but this internal stored procedure had some limitations, so I wrote the following script.

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 @ dbSELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id! = 2; WHILE (1x 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

In addition, you can use the following script for table names. Skip it here and don't introduce too much!

DECLARE @ db_name NVARCHAR (32); DECLARE @ sql_text NVARCHAR (MAX); DECLARE @ db TABLE (database_name NVARCHAR (64)); INSERT INTO @ dbSELECT name FROM sys.databases WHERE state_desc='ONLINE' AND database_id! = 2; WHILE (1x 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, thank you for your reading! This is the end of the article on "how SQL Server looks up tables and columns with spaces in table or column names". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, you can share it out for more people to see!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.