Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

query sql server table names with prefix

Posted on 2009-04-23
7
Medium Priority
?
521 Views
Last Modified: 2012-05-06
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
0
Comment
Question by:glenn_r
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24216200
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
 

Author Comment

by:glenn_r
ID: 24252517
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
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24252682
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 

Author Comment

by:glenn_r
ID: 24253121
Why would they user the forward slashes in the table name?
What is the purpose of the schema?
0
 
LVL 3

Expert Comment

by:GregTSmith
ID: 24253907
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
 
LVL 3

Accepted Solution

by:
GregTSmith earned 200 total points
ID: 24253969
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
 

Author Closing Comment

by:glenn_r
ID: 31573795
Thanks a million Greg your explanation was a real help. Makes sense now. Glenn
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question