Query a dynamic table...HELP!

Hello,

I am trying to write a query to pull data from a table that is system generated daily at midnight. I tried:

declare @.tablename varchar(50) set @.tablename = (select name from sysobjects where crdate = '05/01/2003') select * from @.tablename

But I get an error on 'select * from @.tablename' (must declare @.tablename)

Any assistance is GREATLY APPRECIATED!!!!Please check the answer in

http://forums.databasejournal.com/showthread.php?threadid=31446

Qualifing table names with dbo

I see different examples of query statements some with the table names
preceeded by dbo.tablename and others with only the table name. Does the db
o
do anything at all, especially if the user that request the query to execute
is not the dbo simply a user with read only permissions?Jim Abel (JimAbel@.discussions.microsoft.com) writes:
> I see different examples of query statements some with the table names
> preceeded by dbo.tablename and others with only the table name. Does
> the dbo do anything at all, especially if the user that request the
> query to execute is not the dbo simply a user with read only
> permissions?
Objects in a database can be referred to with three-part names on the
form catalog.schema.object.
In SQL Server "catalog" is the same as "database". Up to SQL 2000 "schema"
was the same as owner, but in SQL 2005 owner and schema have been separated.
A schema can be thought of as a namespace, so that different groups that
work in the same database can use different namespaces, so they don't
get name clashes.
In SQL 2000, each user has a default schema which is equal to the user
name - obvious since schema and owner is the same. If the user "fred" says
"SELECT * FROM tbl", SQL Server first looks up his default schema, so
if there is a table fred.tbl, this is what he will get. If there is not,
SQL Server moves to the default schema for the database, which is dbo.
By prefixing with dbo, "SELECT * FROM dbl.tbl", fred makes an unmabiguous
reference.
In practice, all objects in a database are owned by dbo in 99% of the
database world-wide, so dbo becomes a bit redudant. But as I discussed in
another post today, there can be a performance benefit, if you
specify the schema.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I think he means that dbo as the default database owner does not need to be
used, but should be for performance reasons.
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Jim Abel" wrote:
> I'm still unclear
> Please elaborate on what you mean by "if dbo exists" do yo mean if there i
s
> a dbo assigned to the database specifically as on of the users? and if one
> exists then how does it help if the query is requested by say the asp.net
> user that only has read access to the tables in the query?
> "Rajesh" wrote:
>|||Would adding dbo to db on a single server really make a difference in
performance?
(2000 or 2005)
thanks
kes
--
thanks (as always)
some day i''m gona pay this forum back for all the help i''m getting
kes
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||I am very new to SQL Server but I thought dbo (DataBase Owner) always exists
,
it cannot be deleted.
Quote from MSDN:
========================================
======
The dbo user cannot be deleted and is always present in every database.
========================================
======
However, when to use it...I find very confusing.
T
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||I'm still unclear
Please elaborate on what you mean by "if dbo exists" do yo mean if there is
a dbo assigned to the database specifically as on of the users? and if one
exists then how does it help if the query is requested by say the asp.net
user that only has read access to the tables in the query?
"Rajesh" wrote:
> if dbo exists then the sql server resolves the object immeidately
> otherwise it needs to search the databases one by one in the server.
> Basically its a performance issue related.
> HTH
> Rajesh Peddireddy.
>
> "Jim Abel" wrote:
>|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> I have had some discussions with the SQL Server team on that one...
> In SQL 2000, if a plain user issues a SELECT statement or calls a
> stored procedure with qualifying the name with dbo, there is a cost.
But the order of magnitute of this cost for it to be considered a
"performance issue", or to make dbo prefixing a performance "best practice".
David|||If the sp's are called by multiple users concurrently or many times a second
the lack of owner qualification can have a huge performance impact. There is
no question this is more than just a best practice. It has definite
implications if not done.
Andrew J. Kelly SQL MVP
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:%23RIY3lcKGHA.1676@.TK2MSFTNGP09.phx.gbl...
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
> David
>|||David Browne (davidbaxterbrowne no potted meat@.hotmail.com) writes:
> "Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
> news:Xns9760AC1EB409EYazorman@.127.0.0.1...
> But the order of magnitute of this cost for it to be considered a
> "performance issue", or to make dbo prefixing a performance "best
> practice".
I should have included a reference to this KB article:
http://support.microsoft.com/kb/q263889/.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23NQ3YDdKGHA.500@.TK2MSFTNGP15.phx.gbl...
> If the sp's are called by multiple users concurrently or many times a
> second the lack of owner qualification can have a huge performance impact.
> There is no question this is more than just a best practice. It has
> definite implications if not done.
>
What scenario are you claiming has a "huge performance impact"? Referencing
objects inside stored procedures? The objects are only resolved at query
parse/compile time, not on every call. IMO, basic best practices wrt shared
SQL eliminate any significant impact with object name resolution.
David