Results 1 to 2 of 2
  1. #1
    Join Date
    May 2004

    Unanswered: Defraging database / help

    Hi all

    I want to defrag the SQL database
    yes this is code

    DBCC INDEXDEFRAG (Northwind, Orders, CustomersOrders)

    but this will defrag a specific Table

    but i want defrag entire a specific database

    how can i do it


  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    Create a view in your database:

    create view dbo.vw_DBCC_INDEXDEFRAG as
    select cmd = 'dbcc indexdefrag (' + db_name() + ', ' + + ', ' + + ')'
    from sysindexes i
    inner join sysobjects o on
    where objectproperty(, 'IsMSShipped') = 0
    and objectproperty(, 'IsTable') = 1
    and indid > 0 and indid < 255 and (i.status & 64)=0

    Then, at command prompt type the following:

    C:\>BCP <db_name>.dbo.vw_DBCC_INDEXDEFRAG out DBCC_INDEXDEFRAG.SQL -S <your_server> -T -c
    C:\>OSQL -i DBCC_INDEXDEFRAG.SQL -o DBCC_INDEXDEFRAG.OUT -S <server_name> -d <your_database> -E -h-1 -n -w 256

    ...And you're done.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

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