重建数据库索引等SQL常用语句

压缩数据库：

--清空日志

DUMP  TRANSACTION  库名  WITH  NO_LOG   

 

--截断事务日志：

BACKUP LOG 库名 WITH NO_LOG

 

--收缩数据库

DBCC SHRINKDATABASE(库名)

 

--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles

DBCC SHRINKFILE(1)

 

 

重建数据库索引：

USE DatabaseName --Enter the name of the database you want to reindex

DECLARE @TableName varchar(255)

DECLARE TableCursor CURSOR FOR

SELECT Table_Schema+'.'+Table_Name FROM information_schema.tables WHERE table_type = 'base table'

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TableName

WHILE @@FETCH_STATUS = 0

BEGIN

DBCC DBREINDEX(@TableName,' ',90)

FETCH NEXT FROM TableCursor INTO @TableName

END

CLOSE TableCursor

DEALLOCATE TableCursor

转载于:https://www.cnblogs.com/martian6125/p/9631007.html