sql server的数据库个数、表个数及表的数据量统计
sql server的数据库个数、表个数及表的数据量统计
--由于今天要监控数据，急需统计实例中1有多少库2库里有多少表3每个表有多少数据
--将写好的代码贴出来，用到如下的：
--sysobjects：在数据库每个对象（约束、默认值、日志、规则、存储过程）占一行。
--sysindexes：数据库中的每个索引和表在表中各占一行。
--syscolumns：每个表和视图中的每列在表中占一行，存储过程中每个参数在表中占一行。
select * from sysobjects
select * from sysindexes
select * from syscolumns
--1-----------统计有多少数据库，查出库里面多少表---------------
declare @str varchar(8000)
set @str='' www.2cto.com
select @str=@str+
'union all select '+quotename(name,'''')+' ,COUNT(*)
from '+name+'.dbo.sysobjects where xtype=''U'''
from (select name from master.dbo.sysdatabases) a
set @str =' select ''0数据库总数'' as 库名,(select count(*) from master.dbo.sysdatabases)
as 表的个数 '+ @str+' order by 库名 '
print @str
exec(@str)
---2----------统计当前数据库的表的个数和表的数据记录数----------
set nocount on --不记录放回多少行受影响，这样速度快很多，是一种优化
if object_id(N'tempdb.db.#temp') is not null
drop table #temp www.2cto.com
create table #temp (name sysname,count numeric(18))
insert into #temp
select o.name,i.rows
from sysobjects o,sysindexes i
where o.id=i.id and o.Xtype='U' and i.indid<2
select count(count) 当前库总表数,sum(count) 总记录数 from #temp
select * from #temp
set nocount off --打开返回计数
----3--------------比较两个数据库的表个数和数据量，两个库的数据结构相同-------
set nocount on
if object_id(N'tempdb.db.#temp1') is not null
drop table #temp1
create table #temp1 (name sysname,count numeric(18))
insert into #temp1
select o.name,i.rows
from Ljfcdata30.dbo.sysobjects o,Ljfcdata30.dbo.sysindexes i
where o.id=i.id and o.Xtype='U' and i.indid<2
--查询2
if object_id(N'tempdb.db.#temp2') is not null
drop table #temp2
create table #temp2 (name sysname,count numeric(18))
insert into #temp2
select o.name,i.rows
from Ljfcdata31.dbo.sysobjects o,Ljfcdata31.dbo.sysindexes i
where o.id=i.id and o.Xtype='U' and i.indid<2
select '30号' as 日期 ,count(count) 总表数,sum(count) 总记录数 from #temp1
union all select '31号' ,count(count) 总表数,sum(count) 总记录数 from #temp2
select i.name as 表名, i.count as '30号',j.count as '31号'
from #temp1 i Left join #temp2 j on i.name = j.name
order by i.name
set nocount off
go
---3 查询数据库的所有表的所有列------------------------------------------------------------------
---3 查询数据库的所有表的所有列------------------------------------------------------------------
declare @s varchar(8000)
set @s='' www.2cto.com
select @s=@s+
( select 'select '''+name+''' as dbname,
a.name collate Chinese_PRC_CI_AS as tablename,
b.name collate Chinese_PRC_CI_AS as colname,
c.name collate Chinese_PRC_CI_AS as coltype,
c.length as coltype from ['+
name+']..sysobjects a
inner join ['+
name+']..syscolumns b on a.id=b.id
inner join ['+
name+']..systypes c on c.xtype=b.xtype where a.type=''U'''
as sql from master..sysdatabases as s where s.name=d.name)+' union '
from master..sysdatabases as d
set @s=left(@s,len(@s)-7) + ' order by dbname'
print @s
execute(@s)
--以上的脚本在sql2008R2中通过
