10

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?

Improve this question
3
  • Whats wrong with \w? i.e.. [a-zA-Z0-9_] ??
    – karthik manchala
    Commented May 10, 2015 at 13:18
  • I am referring this article social.msdn.microsoft.com/Forums/sqlserver/en-US/…
    – 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
    Commented May 10, 2015 at 13:32

1 Answer 1

Reset to default
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

  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.

Improve this answer
10
  • 1
    Even if it is not recommended the first character can be @ or #.
    – Casimir et Hippolyte
    Commented May 10, 2015 at 13:37
  • 1
    @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
    Commented May 10, 2015 at 13:52
  • 1
    @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 ° § ^ ? * - %
    – xanatos
    Commented May 10, 2015 at 14:06
  • 1
    Incredible, and it works too with a beaver table: [(8B °)<##)]
    – Casimir et Hippolyte
    Commented May 10, 2015 at 14:26
  • 1
    @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.
    – xanatos
    Commented May 5, 2020 at 15:50

Not the answer you're looking for? Browse other questions tagged or ask your own question.