CODEWITHSUNDEEP
regexsql-server-2008c#-4.0
Rakesh
Rakesh

Reputation: 313

Regular expression for validating SQL Server table name

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?

Upvotes: 10

Views: 13455

Answers (1)

xanatos
xanatos

Reputation: 111870

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

  1. 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.

Upvotes: 13

Related Questions