DBCC SHOWCONTIG

Cross-posted in: microsoft.public.sqlserver.programming &
microsoft.public.sqlserver.server
I have a procedure that loops through the databases and then their tables
and I then want to run DBCC SHOWCONTIG on each table. However, it seems that
DBCC SHOWCONTIG is insisting on only running in the current database, as
defined by the USE statement, or perhaps the database the procedure is being
run from , not sure as BOL is vague on this point.
I tried adding:
SET @.SQL = 'USE ' + @.DB
EXEC sp_executesql @.SQL
Which seems to execute fine.
I then execute:
EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
I also tried to get and use the OBJECT_ID.
but I continue to get the same error:
Server: Msg 2506, Level 16, State 1, Line 1
Could not find a table or object name 'Data.dbo.sysobjects' in database
'master'.
I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
database, hope I'm wrong.Jay,
> SET @.SQL = 'USE ' + @.DB
> EXEC sp_executesql @.SQL
But it does not mean you are now in @.DB. Current context has not changed.
use northwind
go
exec sp_executesql N'use pubs'
go
select db_name()
go
You have to add the USE statement as part of the batch.
exec sp_executesql N'use AdventureWorks; dbcc showcontig (''dbo.ErrorLog'',
1)'
go
AMB
"Jay" wrote:
> Cross-posted in: microsoft.public.sqlserver.programming &
> microsoft.public.sqlserver.server
> I have a procedure that loops through the databases and then their tables
> and I then want to run DBCC SHOWCONTIG on each table. However, it seems that
> DBCC SHOWCONTIG is insisting on only running in the current database, as
> defined by the USE statement, or perhaps the database the procedure is being
> run from , not sure as BOL is vague on this point.
> I tried adding:
> SET @.SQL = 'USE ' + @.DB
> EXEC sp_executesql @.SQL
> Which seems to execute fine.
> I then execute:
> EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
> I also tried to get and use the OBJECT_ID.
> but I continue to get the same error:
> Server: Msg 2506, Level 16, State 1, Line 1
> Could not find a table or object name 'Data.dbo.sysobjects' in database
> 'master'.
> I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
> database, hope I'm wrong.
>
>|||Works great, thanks
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:3498EDC5-3762-4881-88AF-1E387056AE95@.microsoft.com...
> Jay,
>> SET @.SQL = 'USE ' + @.DB
>> EXEC sp_executesql @.SQL
> But it does not mean you are now in @.DB. Current context has not changed.
> use northwind
> go
> exec sp_executesql N'use pubs'
> go
> select db_name()
> go
> You have to add the USE statement as part of the batch.
> exec sp_executesql N'use AdventureWorks; dbcc showcontig
> (''dbo.ErrorLog'',
> 1)'
> go
>
> AMB
> "Jay" wrote:
>> Cross-posted in: microsoft.public.sqlserver.programming &
>> microsoft.public.sqlserver.server
>> I have a procedure that loops through the databases and then their tables
>> and I then want to run DBCC SHOWCONTIG on each table. However, it seems
>> that
>> DBCC SHOWCONTIG is insisting on only running in the current database, as
>> defined by the USE statement, or perhaps the database the procedure is
>> being
>> run from , not sure as BOL is vague on this point.
>> I tried adding:
>> SET @.SQL = 'USE ' + @.DB
>> EXEC sp_executesql @.SQL
>> Which seems to execute fine.
>> I then execute:
>> EXEC ('DBCC SHOWCONTIG(''' + @.DB + '.' + @.Owner + '.' + @.Table + ''')')
>> I also tried to get and use the OBJECT_ID.
>> but I continue to get the same error:
>> Server: Msg 2506, Level 16, State 1, Line 1
>> Could not find a table or object name 'Data.dbo.sysobjects' in database
>> 'master'.
>> I have a bad feeling that DBCC SHOWCONTIG can only be run in the current
>> database, hope I'm wrong.
>>