SqlServer下一些实用的sql语句收集

• 清理数据库日志

USE [master]
ALTER DATABASE [表名] 
SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE [表名] 
SET RECOVERY SIMPLE
GO
USE [表名]
DECLARE @logname VARCHAR(150)
SELECT @logname = NAME
FROM   sys.database_files
WHERE  NAME LIKE‘%log‘
DBCC SHRINKFILE(@logname, 11, TRUNCATEONLY)
GO
USE [master]
ALTER DATABASE [表名]
SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE [表名] 
SET RECOVERY FULL
GO

• 查看数据库中各表的记录数

select a.name as 表名,max(b.rows) as 记录条数 from sysobjects a,sysindexes b 
where a.id=b.id and a.xtype=‘u‘ 
group by a.name 
order by max(b.rows) desc