query sql server table names with prefix

I have a clients sql server 2005 database. I've attached to the mdf and ldf files. The database tables are named:

wd1./1CN/CPASAP0001, wd1./1CN/CPNSAP0001, wd1./1CN/CPTSAP0001, etc.

I'm trying to run the query SELECT * FROM CPASAP0001 and I get the error message "invalid object name. If use the fully qualified name of

What does the wd1./1CN/ represent and why can't I query the tables?

Thanks,
Glenn
glenn_rAsked:
Who is Participating?
 
GregTSmithCommented:
Note: Schemas can be used for security and to help group concerns together.  If you are working with a database that has hundreds (or thousands) of tables, you could create a schema called "HR" and attache the "Employees" table to that schema, so it would be grouped with other "HR" tables, and be queried using HR.Employees.
0
 
GregTSmithCommented:
Try enclosing the schema / object with brackets.  

Like this:

SELECT * FROM [wd1].[/1CN/CPASAP0001]

(If this isn't a production table yet, but will end up being one, I would rename this to something that didn't require brackets to be queried.)
0
 
glenn_rAuthor Commented:
Greg,

I'm new to the schema / object with brackets syntax. Can you breakdown and explain the [wd1].[/1CN/CPASAP0001] ; what the [wd1], the dot, the 1CN/, and what the CPASAP0001 means? Why are people doing this? Whats its purpose?

Thanks,
Glenn
0
Network Scalability - Handle Complex Environments

Monitor your entire network from a single platform. Free 30 Day Trial Now!

 
RiteshShahCommented:
well, in above scenario, wd1 is your schema that own your table (/1cn/cpasap0001)

if Greg's query is working than your table name is /1cn/cpasap0001 not only cpasap0001
0
 
glenn_rAuthor Commented:
Why would they user the forward slashes in the table name?
What is the purpose of the schema?
0
 
GregTSmithCommented:
I may have interpreted your original post incorrectly.  You list your tables as: wd1./1CN/CPASAP0001, wd1./1CN/CPNSAP0001, wd1./1CN/CPTSAP0001, etc.

When writing T-SQL you can specify the *path* to the object you're trying to query, like this:  (I'm using <> to show variables here)

SELECT * FROM [<database>].[<schema>].[<table>] <alias>

See how they are separated by periods?  I assumed from your table list that wd1 was a schema, but I'm not clear if it is or not.  Because periods are interpreted by SQL Server as separators for database / schema / object, having a period in the name of your database / schema / object would require you to put the object name in [brackets].  (Other things require brackets too... dashes, spaces... probably a lot of others as well.)

Every table has a schema, and the default is "dbo".  The schema value does not need to be supplied unless the schema is something other than dbo.  (dbo could be supplied as well if you want to be explicit)  

The database value does not need to be supplied unless you are doing cross-database queries.  SQL Server will automatically look for the object in the database you are currently connected to.

So... maybe try this:
SELECT * FROM [wd1./1CN/CPASAP0001]

Open in new window

0
 
glenn_rAuthor Commented:
Thanks a million Greg your explanation was a real help. Makes sense now. Glenn
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.