could not find table error message

I run a script that tells me what tables need to be defragged. This query
gets a list of the tables:
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
and then this query checks to see how fragmented the table is:
EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
The first query is finding some tables that when the second query runs I get
an error message that says "Could not find a table or object named 'tblDNS'.
Check sysobjects.
Why would the first query find a table but the second query wouldn't find
the table. They must be looking in two different places. How do I correct the
problem and get things back in sync?
Thanks,
--
Dan D.Why are you using dynamic SQL for this? Below work fine on my machine:
USE pubs
DECLARE @.n sysname
SET @.n = 'authors'
DBCC SHOWCONTIG(@.n)
Perhaps the problem is the owner (2000) or schema (2005) of the table.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dan D." <DanD@.discussions.microsoft.com> wrote in message
news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
>I run a script that tells me what tables need to be defragged. This query
> gets a list of the tables:
> SELECT TABLE_NAME
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE TABLE'
> and then this query checks to see how fragmented the table is:
> EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> The first query is finding some tables that when the second query runs I get
> an error message that says "Could not find a table or object named 'tblDNS'.
> Check sysobjects.
> Why would the first query find a table but the second query wouldn't find
> the table. They must be looking in two different places. How do I correct the
> problem and get things back in sync?
> Thanks,
>
> --
> Dan D.|||I was using it because that was the way it was written in a script someone
posted here and I didn't know any better. Thanks,
--
Dan D.
"Tibor Karaszi" wrote:
> Why are you using dynamic SQL for this? Below work fine on my machine:
> USE pubs
> DECLARE @.n sysname
> SET @.n = 'authors'
> DBCC SHOWCONTIG(@.n)
>
> Perhaps the problem is the owner (2000) or schema (2005) of the table.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Dan D." <DanD@.discussions.microsoft.com> wrote in message
> news:37AF80A9-1718-4D4C-AADC-E8E5A2F8E22F@.microsoft.com...
> >I run a script that tells me what tables need to be defragged. This query
> > gets a list of the tables:
> > SELECT TABLE_NAME
> > FROM INFORMATION_SCHEMA.TABLES
> > WHERE TABLE_TYPE = 'BASE TABLE'
> >
> > and then this query checks to see how fragmented the table is:
> > EXEC ('DBCC SHOWCONTIG (''' + @.tablename + ''')
> > WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
> >
> > The first query is finding some tables that when the second query runs I get
> > an error message that says "Could not find a table or object named 'tblDNS'.
> > Check sysobjects.
> >
> > Why would the first query find a table but the second query wouldn't find
> > the table. They must be looking in two different places. How do I correct the
> > problem and get things back in sync?
> >
> > Thanks,
> >
> >
> >
> > --
> > Dan D.
>