DECLARE B CURSOR FOR
SELECT A.NAME FROM (SELECT * FROM SYSOBJECTS)A,(SELECT * FROM SYSINDEXES) B WHERE A.ID=B.ID AND XTYPE='U' GROUP BY A.NAME
OPEN B
DECLARE @BM VARCHAR(50)
DECLARE @SQL VARCHAR(8000)
FETCH NEXT FROM B INTO @BM
WHILE(@@FETCH_STATUS<>-1)
BEGIN
SET @SQL = 'DBCC DBREINDEX ( '+@BM+','''', 0)'
EXEC(@SQL)
PRINT(@SQL)
FETCH NEXT FROM B INTO @BM
END
CLOSE B
DEALLOCATE B
go
go
SET NOCOUNT ON
DECLARE @tablename VARCHAR (128)
DECLARE @execstr VARCHAR (255)
DECLARE @objectid INT
DECLARE @indexid INT
DECLARE @frag DECIMAL
DECLARE @maxfrag DECIMAL
SET @maxfrag = 30.0
CREATE TABLE #fraglist (
ObjectName CHAR (255),
ObjectId INT,
IndexName CHAR (255),
IndexId INT,
Lvl INT,
CountPages INT,
CountRows INT,
MinRecSize INT,
MaxRecSize INT,
AvgRecSize INT,
ForRecCount INT,
Extents INT,
ExtentSwitches INT,
AvgFreeBytes INT,
AvgPageDensity INT,
ScanDensity DECIMAL,
BestCount INT,
ActualCount INT,
LogicalFrag DECIMAL,
ExtentFrag DECIMAL)
DECLARE tables CURSOR FOR SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE'
OPEN tables
FETCH NEXT FROM tables INTO @tablename
WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @tablename + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS')
FETCH NEXT FROM tables INTO @tablename
END
CLOSE tables
DEALLOCATE tables
DECLARE indexes CURSOR FOR SELECT ObjectName, ObjectId, IndexId, LogicalFrag FROM #fraglist
WHERE LogicalFrag >= @maxfrag
AND INDEXPROPERTY (ObjectId, IndexName, 'IndexDepth') > 0
OPEN indexes
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
WHILE @@FETCH_STATUS = 0 BEGIN
PRINT '正在执行 DBCC INDEXDEFRAG (0, ' + RTRIM(@tablename) + ',
' + RTRIM(@indexid) + ') - fragmentation currently '
+ RTRIM(CONVERT(varchar(15),@frag)) + '%'
SELECT @execstr = 'DBCC INDEXDEFRAG (0, ' + RTRIM(@objectid) + ', ' + RTRIM(@indexid) + ')'
EXEC (@execstr)
FETCH NEXT FROM indexes INTO @tablename, @objectid, @indexid, @frag
END
CLOSE indexes
DEALLOCATE indexes
DROP TABLE #fraglist
GO
DBCC SHRINKFILE(1)
go
DBCC SHRINKFILE(2)
go
DBCC SHRINKDATABASE(数据库名称,0)
go
EXEC sp_dboption '数据库名称','autoshrink', 'TRUE'
go
DUMP TRANSACTION 数据库名称 WITH NO_LOG
go
BACKUP LOG 数据库名称 WITH NO_LOG
GO
declare @databasefile varchar(50)
declare @SQL nvarchar(4000)
set @databasefile=(select name from sysfiles where fileid=1)
set @SQL='
ALTER DATABASE 数据库名称
MODIFY FILE
(
NAME = '+@databasefile+', --mdf 数据文件逻辑文件名
FILEGROWTH = 100MB
)
'
print (@sql)
exec (@sql)
set @databasefile=(select name from sysfiles where fileid=2)
set @SQL='
ALTER DATABASE 数据库名称
MODIFY FILE
(
NAME = '+@databasefile+',-- ldf 日志文件逻辑文件名
FILEGROWTH = 100MB
) '
print (@sql)
exec (@sql)
