Regular expression for validating SQL Server table name

Question

I am creating dynamic SQL Server table using C# code but I need to validate the table name.

What is a regular expression for validating SQL Server table names?

I am referring this article social.msdn.microsoft.com/Forums/sqlserver/en-US/… — Rakesh
– Rakesh, Commented May 10, 2015 at 13:25
Whats about this Regex regex = new Regex(@"^[ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz[]. -_0123456789]{1,128}$"); if (!regex.IsMatch(tableName)) throw new ApplicationException("Invalid table name"); — Rakesh
– Rakesh, Commented May 10, 2015 at 13:32

xanatos · Accepted Answer · 2016-06-27 07:17:58Z

13

The regex described in the link should be:

var regex = new Regex(@"^[\p{L}_][\p{L}\p{N}@$#_]{0,127}$");

Note that in general you'll have to embed the name of the table in [...], because of the rule 3 (so SELECT * FROM [SET] is a valid query, because, while SET is a reserved keyword, you can "escape" it with the [...])

Note that in the linked page the rule is incomplete:

From https://msdn.microsoft.com/en-us/library/ms175874.aspx

The identifier must not be a Transact-SQL reserved word. SQL Server reserves both the uppercase and lowercase versions of reserved words. When identifiers are used in Transact-SQL statements, the identifiers that do not comply with these rules must be delimited by double quotation marks or brackets. The words that are reserved depend on the database compatibility level. This level can be set by using the ALTER DATABASE statement.

And they forgot: https://msdn.microsoft.com/en-us/library/ms174979.aspx

Is the name of the new table. Table names must follow the rules for identifiers. table_name can be a maximum of 128 characters, except for local temporary table names (names prefixed with a single number sign (#)) that cannot exceed 116 characters.

The rule that I've written is for "full" tables, not for temporary tables, and doesn't include schema name.

edited Jun 27, 2016 at 7:17

answered May 10, 2015 at 13:32

xanatos

112k13 gold badges209 silver badges297 bronze badges

Sign up to request clarification or add additional context in comments.

10 Comments

Casimir et Hippolyte Over a year ago

Even if it is not recommended the first character can be @ or #.

xanatos Over a year ago

@CasimiretHippolyte But at least on the SQLFiddle and on the SQL Express LocalDB that I have here (sorry, no full SQL Server on my machine), it seems that [@Foo] and [Foo Bar] are both valid table names... so it seems that the rules given on MSDN are "de minimis" rules, and other names are valid.

xanatos Over a year ago

@CasimiretHippolyte My problem is that the name of the tables seems to be more a "anything goes"... sqlfiddle.com/#!6/af4ca/2 I was able to name a table ° § ^ ? * - %

Casimir et Hippolyte Over a year ago

Incredible, and it works too with a beaver table: [(8B °)<##)]

xanatos Over a year ago

@ShimmyWeitzhandler The _ is included ([\p{L}_]), while the @ and the # would make the table a table variable/a temporary table, but as I've said in the response The rule that I've written is for "full" tables, not for temporary tables, and doesn't include schema name.

|

Collectives™ on Stack Overflow

Regular expression for validating SQL Server table name

1 Answer 1

10 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related