looping thru databases - not

Question

Post reply

looping thru databases - not

sunshine

SSChasing Mays

Points: 636
More actions
November 20, 2003 at 5:21 pm

#101765

I just can't seem to get this to work. I'm trying to run DBCC SHOWCONTIG on each database to populate an administrative table (contigInfo). Although the DBCC SHOWCONTIG does load it's output into the table, it only loads it over and over for the first database it finds. What am I doing wrong?
-- Using sp_MSforeachdb
truncate table Admin.dbo.contigInfo
insert into Admin.dbo.contigInfo
exec sp_MSforeachdb @command1 =
'dbcc showcontig with tableresults, all_indexes'
-- Using a cursor
Declare @dbName sysname
Declare dbCursor Cursor For
Select name from master..sysdatabases where dbid > 6 order by name
truncate table Admin.dbo.contigInfo
Open dbCursor
fetch Next From dbCursor Into @dbName
While @@Fetch_Status = 0
Begin
exec('use ' + @dbName)
print 'dbvar' + @dbName
print 'dbcurrent' + db_name()
insert into Admin.dbo.contigInfo
exec ('dbcc showcontig with tableresults, all_indexes')
fetch Next From dbCursor Into @dbName
End
Close dbCursor
Deallocate dbCursor

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

philcart SSC-Forever Points: 47796 More actions · Answer 1

You need to include the 'use ...' as part of the EXEC

eg:



exec ('use ' + @dbName + ' dbcc showcontig with tableresults, all_indexes')

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

sunshine SSChasing Mays Points: 636 More actions · Answer 2

Thanks Phil. That worked for the cursor. Any idea why sp_MSforeachdb only reports on one database?

noeld SSC Guru Points: 96590 More actions · Answer 3

Are you using :



sp_MSforeachdb 'use ? dbcc showcontig with tableresults, all_indexes'

Remember it coms back as Multiple RS

HTH

* Noel

sunshine SSChasing Mays Points: 636 More actions · Answer 4

Thanks noeld,

The following works great:

insert into Admin.dbo.contigInfo

exec sp_MSforeachdb 'use ? dbcc showcontig with tableresults, all_indexes'

Makes for an elegant solution.

I'm aware of multi RS returned. I'm also working on how to populate a dbName column in contigInfo for each row returned.

philcart SSC-Forever Points: 47796 More actions · Answer 5

Not sure if it'll work but how about using OPENQUERY. You might have to shelve the sp_MSforeachDB routine though.

Hope this helps

Phill Carter

--------------------

Colt 45 - the original point and click interface

--------------------
Colt 45 - the original point and click interface

noeld SSC Guru Points: 96590 More actions · Answer 6

How About:

--First Add the Column

Alter Table ConfigInfo Add DBName nvarchar(128)

--Then The meat and potatos

 



exec sp_MSforeachdb 'use ?  

  insert into Admin.dbo.contigInfo

  ( ObjectName ,

   ObjectId ,

   IndexName,

   IndexId,

   Lvl,

   CountPages,

   CountRows,

   MinRecSize ,

   MaxRecSize ,

   AvgRecSize ,

   ForRecCount,

   Extents ,

   ExtentSwitches ,

   AvgFreeBytes ,

   AvgPageDensity ,

   ScanDensity ,

   BestCount ,

   ActualCount ,

   LogicalFrag ,

   ExtentFrag ) exec (''dbcc showcontig with tableresults, all_indexes, no_infomsgs'')  Update Admin.dbo.contigInfo Set DBName = ''?'' where Admin.dbo.contigInfo.DBName IS NULL'

This was FUN!

* Noel

sunshine SSChasing Mays Points: 636 More actions · Answer 7

noeld,

Ooohhhh!!! Aaahhhh!!! This is waaaay cool!

I'd just about given up on sp_MSforeachdb and was working on the cursor when I got your solution. Now I'm gonna add a datetime column and I have a growth/fragmentation table I can load periodically and create great tracking reports.

Thanks for hangin' in there and working out the solution I wanted. Have a great Thanksgiving!

noeld SSC Guru Points: 96590 More actions · Answer 8

Thanks,

I have learned from this one too 😉

Happy thanks giving to you too!!

* Noel

Andy Warren SSC Guru Points: 119902 More actions · Answer 9

Nice thread!

Andy

http://www.sqlservercentral.com/columnists/awarren/

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Paul-204783 Newbie Points: 2 More actions · Answer 10

Here it is with a temp table if anyone is interested in making this a Zero Footprint kind of thing... I threw the GetDate's in to find out how long it was taking for testing...

-Paul

SELECT GETDATE

()

CREATE TABLE

#DBCC_SHOWCONTIG(ObjectName varchar(100),ObjectID Int,IndexName varchar(100),IndexId Int,Level Int,CountPages Int,CountRows Int,MinimumRecordSize Int,MaximumRecordSize Int,AverageRecordSize Int,ForwardedRecords Int,Extents Int,ExtentSwitches Int,AverageFreeBytes Int,AveragePageDensity Int,ScanDensity Int,BestCount Int,ActualCount Int,LogicalFragmentation DECIMAL,ExtentFragmentation DECIMAL,DBName Varchar(50))

GO

EXEC

sp_MSforeachdb

@command1= 'if ''?'' <> ''tempdb''

USE ?

INSERT INTO #DBCC_SHOWCONTIG(ObjectName,ObjectID,IndexName,IndexId,Level,CountPages,CountRows,MinimumRecordSize,MaximumRecordSize,AverageRecordSize,ForwardedRecords,Extents,ExtentSwitches,AverageFreeBytes,AveragePageDensity,ScanDensity,BestCount,ActualCount,LogicalFragmentation,ExtentFragmentation)

EXEC (''DBCC SHOWCONTIG WITH TABLERESULTS, all_indexes, no_infomsgs'')

UPDATE #DBCC_SHOWCONTIG SET DBName = ''?'' WHERE DBName IS NULL'

,@command2= 'Print ''The Current DB is ?'''

SELECT

DBNAME, * FROM #DBCC_SHOWCONTIG

DROP TABLE

#DBCC_SHOWCONTIG

SELECT GETDATE

()

GO