Cari Blog Ini

01 Oktober 2012

Shrinking Database Using SQL Script

Query:

------------------------------Code:Start------------------------------------------------

SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT TABLE_NAME
   FROM INFORMATION_SCHEMA.TABLES
   WHERE TABLE_TYPE = 'BASE TABLE'


-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
-- Do Building Index

   DBCC DBREINDEX(@TABLENAME,'',0)

   FETCH NEXT
      FROM tables
      INTO @tablename
END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables

DECLARE @fileid int

-- Declare cursor
DECLARE tables CURSOR FOR
   SELECT fileid
   FROM sysfiles


-- Open the cursor
OPEN tables

-- Loop through all the tables in the database
FETCH NEXT
   FROM tables
   INTO @fileid

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Do shrinking files

    dbcc shrinkfile(@fileid,0)

    FETCH NEXT
       FROM tables
       INTO @fileid
    END

-- Close and deallocate the cursor
CLOSE tables
DEALLOCATE tables


------------------------------Code:End------------------------------------------------