利用syscolumns系统表:
name sysname 列名或过程参数的名称。
id int 该列所属的表对象 ID，或与该参数关联的存储过程 ID。
xusertype smallint 扩展的用户定义数据类型 ID。
cdefault int 该列的默认值 ID。
prec smallint 该列的精度级别。
scale int 该列的小数位数。
name sysname 列名或过程参数的名称。
id int 该列所属的表对象 ID，或与该参数关联的存储过程 ID。
xusertype smallint 扩展的用户定义数据类型 ID。
cdefault int 该列的默认值 ID。
prec smallint 该列的精度级别。
scale int 该列的小数位数。
@dbase varchar(50),
@updateusage varchar(5) = false
as
set nocount on
declare @id int -- The object id of @objname.
declare @type character(2) -- The object type.
declare @pages int -- Working variable for size calc.
declare @dbname sysname
declare @dbsize dec(15,0)
declare @logsize dec(15)
declare @bytesperpage dec(15,0)
declare @pagesperMB dec(15,0)
declare @objname nvarchar(776)
declare @tbcount int
select identity(int,1,1)as mid ,name into #sysob from sysobjects where xtype='U'
select @tbcount=@@rowcount
while(@tbcount>0)
begin
select @objname=name from #sysob where mid=@tbcount
create table #spt_space -------------過度表
(
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)/*
** Check to see if user wants usages updated.
*/if @updateusage is not null
begin
select @updateusage=lower(@updateusage) if @updateusage not in ('true','false')
begin
raiserror(15143,-1,-1,@updateusage)
return(1)
end
end
/*
** Check to see that the objname is local.
*/
if @objname IS NOT NULL
begin
select @id = null
select @id = id, @type = xtype
from sysobjects
where id = object_id(@objname) /*
** Does the object exist?
*/
if @id is null
begin
raiserror(15009,-1,-1,@objname,@dbname)
return (1)
end
if not exists (select * from sysindexes
where @id = id and indid < 2) if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysprocedures
begin
raiserror(15234,-1,-1)
return (1)
end
else if @type = 'V ' -- View => no physical data storage.
begin
raiserror(15235,-1,-1)
return (1)
end
else if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messages
begin
raiserror(15064,-1,-1)
return (1)
end
else if @type = 'F ' -- FK => no physical data storage.
begin
raiserror(15275,-1,-1)
return (1)
end
end/*
** Update usages if user specified to do so.
*/if @updateusage = 'true'
begin
if @objname is null
dbcc updateusage(0) with no_infomsgs
else
dbcc updateusage(0,@objname) with no_infomsgs
print ' '
end
if @id is not null
begin
/*
** Now calculate the summary data.
** reserved: sum(reserved) where indid in (0, 1, 255)
*/
insert into #spt_space (reserved)
select sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @id /*
** data: sum(dpages) where indid < 2
** + sum(used) where indid = 255 (text)
*/
select @pages = sum(dpages)
from sysindexes
where indid < 2
and id = @id
select @pages = @pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @id
update #spt_space
set data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
- data /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @id)
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @id
-----------------------------------------------------------------------------------------------------------------------
insert into SysNote..tablemaster(name,rows,reserved,data,indexp,unused,dbase)
select @objname,
convert(char(11), rows),
ltrim(str(reserved * d.low / 1024.,15,0) +
' ' + 'KB'),
ltrim(str(data * d.low / 1024.,15,0) +
' ' + 'KB'),
ltrim(str(indexp * d.low / 1024.,15,0) +
' ' + 'KB'),
ltrim(str(unused * d.low / 1024.,15,0) +
' ' + 'KB'),
@dbase
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
--------------------------------------------------------------------------------------------------------------------------
end
set @tbcount=@tbcount-1
drop table #spt_space
end
drop table #sysobGO
功能說明﹕用於查找某一服務器所有得表得基本信息(記錄總數，佔用空間等)
編 寫 人﹕hh_li
完成日期﹕修改記錄**********************************************************************
序號 修改人 修改日期 备注
1
调用示例**********************************************************************
*********************************************************************************/
CREATE proc hh_li_alltbms
as
------------------處理 <表>的詳細信息-----------------------------------
if exists (select 1 from SysNote..sysobjects where id=object_id('SysNote..tablemaster'))
begin
if exists (select 1 from SysNote..sysobjects where id=object_id('SysNote..tablemaster_bk'))
drop table SysNote..tablemaster_bk
select * into SysNote..tablemaster_bk from SysNote..tablemaster
delete SysNote..tablemaster
endelse
begin
create table SysNote..tablemaster
(
name nvarchar(100) null,
rows int null,
reserved nvarchar(100) null,
data nvarchar(100) null,
indexp nvarchar(100) null,
unused nvarchar(100) null,
dbase nvarchar(100) null
)
end----------------------------------------------------------------------------------------------------declare @vdbname nvarchar(50),@end int
select identity(int,1,1) as mid ,name into #sysdb from master.dbo.sysdatabases where name not in('msdb','tempdb','master','model','pubs')
set @end=@@rowcount
while @end>0
begin
select @vdbname=name from #sysdb where mid=@end
exec ('use ['+@vdbname +'] exec sp_hh_li_tbms '''+@vdbname+'''')
set @end=@end-1
endGO
數據庫SysNote下
tablemaster表中(樓主可以自己修改)
樓主隻需要修改第2個存儲過程就Ok了
http://community.csdn.net/Expert/topic/4060/4060414.xml?temp=.8791162