Post reply

looping thru databases - not

  • 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

  • November 20, 2003 at 7:35 pm

    #482993

    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

  • November 24, 2003 at 7:53 am

    #483349

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

  • November 24, 2003 at 8:09 am

    #483363

    Are you using :

    

    sp_MSforeachdb 'use ? dbcc showcontig with tableresults, all_indexes'

    Remember it coms back as Multiple RS

    HTH


    * Noel

  • November 24, 2003 at 8:40 am

    #483372

    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.

  • November 24, 2003 at 3:18 pm

    #483459

    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

  • November 25, 2003 at 10:50 am

    #483588

    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

  • November 25, 2003 at 11:09 am

    #483594

    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!

  • November 25, 2003 at 11:24 am

    #483598

    Thanks,

    I have learned from this one too 😉

    Happy thanks giving to you too!!


    * Noel

  • November 26, 2003 at 7:59 am

    #483747

    Nice thread!

    Andy

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

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

  • October 7, 2005 at 2:43 pm

    #596080

    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

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

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