http://www.cnblogs.com/qinche/archive/2012/08/09/app.html
由于今天要監控資料，急需統計執行個體中1有多少庫2庫裡有多少表3每個表有多少資料
--将寫好的代碼貼出來，用到如下的：
--sysobjects：在資料庫每個對象（限制、預設值、日志、規則、存儲過程）占一行。
--sysindexes：資料庫中的每個索引和表在表中各占一行。
--syscolumns：每個表和視圖中的每列在表中占一行，存儲過程中每個參數在表中占一行。
select * from sysobjects
select * from sysindexes
select * from syscolumns
--1-----------統計有多少資料庫，查出庫裡面多少表---------------
declare @str varchar(8000)
set @str=''
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
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=''
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中通過