Results 1 to 2 of 2

Thread: Dbcc Showcontig

  1. #1
    Join Date
    Apr 2003
    Location
    Bethlehem, PA
    Posts
    2

    Red face Unanswered: Dbcc Showcontig

    I am looking for coding that runs dbcc showcontig on all user databases in one command on a SQL Server. The coding I have listed below will grab the names of user databases, but still runs the showcontig on the master db. I have tried using sp_MSforeachdb, but that performs the showcontig on both system and user databases. Thx!
    P.S. If possible this command can be used on SQL 7.0 and 2K.
    USE master
    DECLARE dnames_cursor CURSOR
    FOR
    SELECT NAME FROM sysdatabases
    where name not in ('master', 'model', 'tempdb', 'pubs', 'Northwind', 'msdb')
    OPEN dnames_cursor
    DECLARE @dbname sysname
    FETCH NEXT FROM dnames_cursor INTO @dbname
    WHILE (@@FETCH_STATUS <> -1)
    BEGIN
    IF (@@FETCH_STATUS <> -2)
    BEGIN
    SELECT @dbname = RTRIM(@dbname)
    set nocount on
    select 'ZZ_Date; ', getdate()
    set nocount on
    Declare @My_dbname char(30)
    exec ('use ' + @dbname)
    select @My_dbname = @dbname
    print 'ZZ_Name; ' + @My_dbname
    dbcc showcontig
    END
    FETCH NEXT FROM dnames_cursor INTO @dbname
    END
    CLOSE dnames_cursor
    DEALLOCATE dnames_cursor
    Last edited by bowie54; 04-21-03 at 17:55.

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    May check this DB Journal link for more information.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •