一段统计各数据库中最（大，小）记录的存储过程

作者：[51ak ]

注意这个存储过程是在“已经统计好想要的数据到指定表以后”进行的二次统计

Ｓelect COUNT(*) from info_tbcount

Ｓelect COUNT(*) from info_tbtypecount

Ｓelect COUNT(*) from info_tbadd

Ｓelect COUNT(*) from info_tbMaxUsed

Ｓelect COUNT(*) from info_tbMaxRows

Ｓelect COUNT(*) from info_tbMaxColumns

Ｓelect COUNT(*) from info_tbMaxRowsAdd

Ｓelect COUNT(*) from info_tbMaxColumnssAdd

Ｓelect MAX(tablecount),MAX(rowscount),MAX(columncount) from info_tbcount

Ｓelect f_ip,dbname,MAX(tablecount) as tablecount from info_tbcount group by f_ip,dbname,f_date

Ｓelect MAX(tablecount),MAX(rowscount),MAX(columncount) from info_tbcount

alter proc usp_get_top1(

@f_date date)

--declare @f_date date=getdate()

--Ｓelect top 10 * from info_tbcount

--合计,表计数，行计数，列计数

Ｓelect count(1) as dbcount,SUM(tablecount) as tablecount,SUM(rowscount) as rowscount,SUM(columncount) as columncount from dbo.info_tbcount where f_date=@f_date

--总文件个数

Ｓelect COUNT(1) as filecount from dbo.Host_sqlfiles

--数据文件总大小

Ｓelect SUM(size)/128/1024 as filesize from dbo.Host_sqlfiles

--共有用户

Ｓelect COUNT(1) as usercount from dbo.Host_sqllogins

--共有用户名(排异)

Ｓelect count(distinct username) from dbo.Host_sqllogins

--其中有DBO权限

Ｓelect count(distinct username) from dbo.Host_SqlRoles where dbrole='db_owner'

Ｓelect sum(t1) as t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='C'

Ｓelect sum(t1) as t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='D'

Ｓelect sum(t1) as t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='F'

Ｓelect sum(t1) as t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='P'

Ｓelect sum(t1) as t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='TR'

Ｓelect sum(t1) as t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='V'

--数据文件最多的数据库

Ｓelect top 1 f_ip,dbname,COUNT(1) as filecount from dbo.Host_sqlfiles where groupid>0 group by f_ip,dbname order by 3 desc

--数据文件最大的数据库

Ｓelect top 1 f_ip,dbname,SUM(size)/128/1024 as filesize from dbo.Host_sqlfiles where groupid>0 group by f_ip,dbname order by 3 desc

--用户名最多的数据库

Ｓelect top 1 f_ip,dbname,COUNT(1) as usercount from Host_SqlRoles group by f_ip,dbname order by 3 desc

--用户名最多的主机

--Ｓelect top 1 f_ip,COUNT(1) as usercount from dbo.Host_sqllogins group by f_ip order by 2 desc

--表数据最多的数据库

Ｓelect top 1 f_ip,dbname,tablecount from dbo.info_tbcount where f_date=@f_date order by tablecount desc

--CHECK约束最多的数据库

Ｓelect top 1 f_ip,dbname,t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='C' order by t1 desc

--默认值最多的数据库

Ｓelect top 1 f_ip,dbname,t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='D' order by t1 desc

--外键约束最多的数据库

Ｓelect top 1 f_ip,dbname,t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='F' order by t1 desc

--存储过程最多的数据库

Ｓelect top 1 f_ip,dbname,t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='P' order by t1 desc

--触发器最多的数据库

Ｓelect top 1 f_ip,dbname,t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='TR' order by t1 desc

--视图最多的数据库

Ｓelect top 1 f_ip,dbname,t1 from dbo.info_tbtypecount where f_date=@f_date and [TYPE]='V' order by t1 desc

--记录数(行)最多的数据库

Ｓelect top 1 f_ip,dbname,rowscount from dbo.info_tbcount where f_date=@f_date order by rowscount desc

--字段数(列)最多的数据库

Ｓelect top 1 f_ip,dbname,columncount from dbo.info_tbcount where f_date=@f_date order by columncount desc

--行记录

Ｓelect top 1 f_ip,dbname,tablename,[rows] from dbo.info_tbMaxrows where f_date=@f_date order by [rows] desc

--列记录

Ｓelect top 1 f_ip,dbname,tablename,[Columns] from dbo.info_tbMaxColumns where f_date=@f_date order by [Columns] desc

--占用大小

Ｓelect top 1 f_ip,dbname,tablename,used from dbo.info_tbMaxUsed where f_date=@f_date order by used desc