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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rakesh
  • 313
  • 1
  • 3
  • 14
  • Whats wrong with `\w`? i.e.. `[a-zA-Z0-9_]` ?? – karthik manchala May 10 '15 at 13:18
  • I am referring this article https://social.msdn.microsoft.com/Forums/sqlserver/en-US/154c19c4-95ba-4b6f-b6ca-479288feabfb/characters-that-are-not-allowed-in-table-name-column-name-in-sql-server-?forum=databasedesign – Rakesh May 10 '15 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 May 10 '15 at 13:32

1 Answers1

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.

xanatos
  • 109,618
  • 12
  • 197
  • 280
  • 1
    Even if it is not recommended the first character can be `@` or `#`. – Casimir et Hippolyte May 10 '15 at 13:37
  • @CasimiretHippolyte I trusted https://msdn.microsoft.com/en-us/library/ms175874.aspx: *A regular identifier that starts with the at sign always denotes a local variable or parameter and cannot be used as the name of any other type of object. An identifier that starts with a number sign denotes a temporary table or procedure.* I'm excluding temporary tables, so no `#`, and for the `@` sign it seems to be forbidden. – xanatos May 10 '15 at 13:43
  • 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 May 10 '15 at 13:52
  • I think it is only warnings about these particular cases, from the same page: *Although the number sign or double number sign characters can be used to begin the names of other types of objects, we do not recommend this practice. [...] Some Transact-SQL functions have names that start with double at signs (@@). To avoid confusion with these functions, you should not use names that start with @@.* . The number sign is indeed reserved for temp tables. – Casimir et Hippolyte May 10 '15 at 13:56
  • 1
    @CasimiretHippolyte My problem is that the name of the tables seems to be more a "anything goes"... http://sqlfiddle.com/#!6/af4ca/2 I was able to name a table `° § ^ ? * - %` – xanatos May 10 '15 at 14:06
  • 1
    Incredible, and it works too with a beaver table: `[(8B °)<##)]` – Casimir et Hippolyte May 10 '15 at 14:26
  • SqlFiddle was playing games on me, so I tried with another sandbox site, [rextester](http://rextester.com/EOWLV77113) and adding more special chars: `° § ^ ? * - % + / \ "`. That still seems to work... **Edit** and it works on a recent LocalDb as well – superjos Sep 05 '17 at 16:38
  • You got it wrong. The first char may include `_@#`. – Shimmy Weitzhandler May 04 '20 at 18:02
  • 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 May 05 '20 at 15:50
  • Just a note, this RegEx is made by a rule, which is more like a recommendation, and does not allow table name to start with a number, which is allowed and is being used in some cases. – Viktor Jevdokimov Sep 30 '21 at 10:32