current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite
1

I'm trying to understand if there is any cons in using table names that starts with the @ sign.

We need a well-identifiable pattern to distinguish these tables from other in the same schema.

We currently work with all the followings:
- Oracle 10g and up
- SqlServer 2008 R2 and up
- Postgres 9.1 and up

I tried with all of them and the table creation succeed, but I can't find good documentation to find out if this is actually permitted.

share|improve this question
    
The naming requirements for objects in all of those environments are well documented. Just don't break the naming requirements. –  David Aldridge Mar 31 at 15:08
    
@SeanLange No, temporary tables starts with #. –  Teejay Mar 31 at 15:08
    
@DavidAldridge As I wrote in the edit I can't find good docs especially for Oracle and Pg. –  Teejay Mar 31 at 15:09
    
It's actually table variables that start with @ in SQL Server –  JamesZ Mar 31 at 15:09
    
@ is illegal in a SQL identifier. –  a_horse_with_no_name Mar 31 at 15:10

3 Answers 3

up vote 2 down vote accepted

@ is an illegal character for an identifier in (standard) SQL.

The rules for valid (legal) identifiers that do not need double quotes are documented in the manuals:

The Postgres manual essentially quotes the SQL standard:

SQL identifiers and key words must begin with a letter (a-z, but also letters with diacritical marks and non-Latin letters) or an underscore (_). Subsequent characters in an identifier or key word can be letters, underscores, digits (0-9), or dollar signs ($)

The Oracle manual essentially says the same:

Nonquoted identifiers can contain only alphanumeric characters from your database character set and the underscore (_), dollar sign ($), and pound sign (#).

Note that Oracle has one exception when it comes to DBLinks:

Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.

You can include a @ in an identifier if you use quoted identifiers. But the Oracle manual also states: Oracle does not recommend using quoted identifiers for database object names

share|improve this answer
    
I've no particular problems with non-quoted identifiers, since our abstraction layer always adds double quotes. Anyway, it's seems to be a bit risky to use these special chars (assuming I don't want to use _). –  Teejay Mar 31 at 15:15
1  
@Teejay: but beware that quoted identifiers are case sensitive. "FooBar" is a different name than "FOOBAR" (not necessarily in SQL Server though) –  a_horse_with_no_name Mar 31 at 15:16
    
Yes, we are aware of that. All our tables are uppercase. –  Teejay Mar 31 at 15:17
    
Assuming I don't want to use _ we will probably end up using _ :-) –  Teejay Mar 31 at 15:25
up vote 2 down vote

Using @ at the beginning of table names in Oracle

I would stick to Oracle in my answer.

AFAIK, Oracle won't allow you to do so.

Even if it is allowed. Practically, one such place where it would be an issue is SQL*Plus. It would be a disaster to execute scripts if you have the "at"(@) sign in the begining of the table_name.

The only exception I know, quoting from the Oracle documentation here http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements008.htm:

Database links can also contain periods (.) and "at" signs (@). Oracle strongly discourages you from using $ and # in nonquoted identifiers.

share|improve this answer
up vote 0 down vote

You will need to start each name for a group with a common string such as acctg or payroll and search the name through the information_schema.table view which is supposed to be in 'every' database. To sell a list of all tables that start with acctg you will have to do a:

select * from information_schema.tables where left(name,5)='acctg' should work on all the products you listed.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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