select * from sysobjects where name='tablename' sp_help 'tablename'--查看数据日志文件情况： exec sp_helpfile dbcc sqlperf('logspace')
某个表的大小需要加参数：sp_spaceused 'tablename'
/* 作者:不祥 这个存储过程能查到当前连接的所有表的空间情况。 */ if exists(select name from sysobjects where name='spaceused' and type='p')Drop procedure spaceusedGOcreate procedure spaceused asbegin 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 sysnamedeclare @dbsize dec(15,0)declare @logsize dec(15)declare @bytesperpage dec(15,0)declare @pagesperMB dec(15,0)declare @objname nvarchar(776) -- The object we want size on.declare @updateusage varchar(5) -- Param. for specifying that create table #temp1(tablename varchar(200) null,--表名rownum char(11) null,--行数baoliukj varchar(15) null,--保留空间datausekj varchar(15) null,--数据使用空间indexkj varchar(15) null,--索引使用空间nousekj varchar(15) null--未用空间)--select @objname=''N_dep'' -- usage info. should be updated.select @updateusage='false'/*Create temp tables before any DML to ensure dynamic** We need to create a temp table to do the calculation.** reserved: sum(reserved) where indid in (0, 1, 255)** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)** indexp: sum(used) where indid in (0, 1, 255) - data** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)*/declare cur_table cursor forselect name from sysobjects where type='u' Open cur_tablefetch next from cur_table into @objname While @@FETCH_STATUS=0begincreate 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 nullbeginselect @updateusage=lower(@updateusage) if @updateusage not in ('true','false')beginraiserror(15143,-1,-1,@updateusage)return(1)endend/*** Check to see that the objname is local.*/if @objname IS NOT NULLbegin select @dbname = parsename(@objname, 3) if @dbname is not null and @dbname <> db_name()beginraiserror(15250,-1,-1)return (1)end if @dbname is nullselect @dbname = db_name() /*** Try to find the object.*/select @id = nullselect @id = id, @type = xtypefrom sysobjectswhere id = object_id(@objname) /*** Does the object exist?*/if @id is nullbeginraiserror(15009,-1,-1,@objname,@dbname)return (1)end if not exists (select * from sysindexeswhere @id = id and indid < 2) if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysproceduresbeginraiserror(15234,-1,-1)return (1)endelse if @type = 'V ' -- View => no physical data storage.beginraiserror(15235,-1,-1)return (1)endelse if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messagesbeginraiserror(15064,-1,-1)return (1)endelse if @type = 'F ' -- FK => no physical data storage.beginraiserror(15275,-1,-1)return (1)endend /*** Update usages if user specified to do so.*/ if @updateusage = 'true'beginif @objname is nulldbcc updateusage(0) with no_infomsgselsedbcc updateusage(0,@objname) with no_infomsgsprint '' end set nocount on /*** If @id is null, then we want summary data.*//* Space used calculated in the following way** @dbsize = Pages used** @bytesperpage = d.low (where d = master.dbo.spt_values) is** the # of bytes per page when d.type = ''E'' and** d.number = 1.** Size = @dbsize * d.low / (1048576 (OR 1 MB))*/if @id is nullbeginselect @dbsize = sum(convert(dec(15),size))from dbo.sysfileswhere (status & 64 = 0) select @logsize = sum(convert(dec(15),size))from dbo.sysfileswhere (status & 64 <> 0) select @bytesperpage = lowfrom master.dbo.spt_valueswhere number = 1and type = 'E'select @pagesperMB = 1048576 / @bytesperpage select database_name = db_name(),database_size =ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + 'MB'),'unallocated space' =ltrim(str((@dbsize -(select sum(convert(dec(15),reserved))from sysindexeswhere indid in (0, 1, 255))) / @pagesperMB,15,2)+ ' MB') print ''/*** Now calculate the summary data.** reserved: sum(reserved) where indid in (0, 1, 255)*/insert into #spt_space (reserved)select sum(convert(dec(15),reserved))from sysindexeswhere indid in (0, 1, 255) /*** data: sum(dpages) where indid < 2** + sum(used) where indid = 255 (text)*/select @pages = sum(convert(dec(15),dpages))from sysindexeswhere indid < 2select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)from sysindexeswhere indid = 255update #spt_spaceset data = @pages /* index: sum(used) where indid in (0, 1, 255) - data */update #spt_spaceset indexp = (select sum(convert(dec(15),used))from sysindexeswhere indid in (0, 1, 255))- data /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */update #spt_spaceset unused = reserved- (select sum(convert(dec(15),used))from sysindexeswhere indid in (0, 1, 255)) select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +' ' + 'KB'),data = ltrim(str(data * d.low / 1024.,15,0) +' ' + 'KB'),index_size = ltrim(str(indexp * d.low / 1024.,15,0) +' ' + 'KB'),unused = ltrim(str(unused * d.low / 1024.,15,0) +' ' + 'KB')from #spt_space, master.dbo.spt_values dwhere d.number = 1and d.type = 'E'end /*** We want a particular object.*/elsebegin/*** Now calculate the summary data.** reserved: sum(reserved) where indid in (0, 1, 255)*/insert into #spt_space (reserved)select sum(reserved)from sysindexeswhere 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 sysindexeswhere indid < 2and id = @idselect @pages = @pages + isnull(sum(used), 0)from sysindexeswhere indid = 255and id = @idupdate #spt_spaceset data = @pages /* index: sum(used) where indid in (0, 1, 255) - data */update #spt_spaceset indexp = (select sum(used)from sysindexeswhere indid in (0, 1, 255)and id = @id)- data /* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */update #spt_spaceset unused = reserved- (select sum(used)from sysindexeswhere indid in (0, 1, 255)and id = @id)update #spt_spaceset rows = i.rowsfrom sysindexes iwhere i.indid < 2and i.id = @idinsert into #temp1select name = object_name(@id),rows = convert(char(11), rows),reserved = ltrim(str(reserved * d.low / 1024.,15,0) +' ' + 'KB'),data = ltrim(str(data * d.low / 1024.,15,0) +' ' + 'KB'),index_size = ltrim(str(indexp * d.low / 1024.,15,0) +' ' + 'KB'),unused = ltrim(str(unused * d.low / 1024.,15,0) +' ' + 'KB')from #spt_space, master.dbo.spt_values dwhere d.number = 1and d.type = 'E'Drop table #spt_spaceendfetch next from cur_table into @objnameendClose cur_tableDEALLOCATE cur_tableSelect * from #temp1 order by len(baoliukj) desc,baoliukj descDrop table #temp1return (0)end
sp_help 'tablename'--查看数据日志文件情况：
exec sp_helpfile
dbcc sqlperf('logspace')
作者:不祥
这个存储过程能查到当前连接的所有表的空间情况。
*/
if exists(select name from sysobjects where name='spaceused' and type='p')Drop procedure spaceusedGOcreate procedure spaceused asbegin
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 sysnamedeclare @dbsize dec(15,0)declare @logsize dec(15)declare @bytesperpage dec(15,0)declare @pagesperMB dec(15,0)declare @objname nvarchar(776) -- The object we want size on.declare @updateusage varchar(5) -- Param. for specifying that
create table #temp1(tablename varchar(200) null,--表名rownum char(11) null,--行数baoliukj varchar(15) null,--保留空间datausekj varchar(15) null,--数据使用空间indexkj varchar(15) null,--索引使用空间nousekj varchar(15) null--未用空间)--select @objname=''N_dep'' -- usage info. should be updated.select @updateusage='false'/*Create temp tables before any DML to ensure dynamic** We need to create a temp table to do the calculation.** reserved: sum(reserved) where indid in (0, 1, 255)** data: sum(dpages) where indid < 2 + sum(used) where indid = 255 (text)** indexp: sum(used) where indid in (0, 1, 255) - data** unused: sum(reserved) - sum(used) where indid in (0, 1, 255)*/declare cur_table cursor forselect name from sysobjects where type='u'
Open cur_tablefetch next from cur_table into @objname
While @@FETCH_STATUS=0begincreate 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 nullbeginselect @updateusage=lower(@updateusage)
if @updateusage not in ('true','false')beginraiserror(15143,-1,-1,@updateusage)return(1)endend/*** Check to see that the objname is local.*/if @objname IS NOT NULLbegin
select @dbname = parsename(@objname, 3)
if @dbname is not null and @dbname <> db_name()beginraiserror(15250,-1,-1)return (1)end
if @dbname is nullselect @dbname = db_name()
/*** Try to find the object.*/select @id = nullselect @id = id, @type = xtypefrom sysobjectswhere id = object_id(@objname)
/*** Does the object exist?*/if @id is nullbeginraiserror(15009,-1,-1,@objname,@dbname)return (1)end
if not exists (select * from sysindexeswhere @id = id and indid < 2)
if @type in ('P ','D ','R ','TR','C ','RF') --data stored in sysproceduresbeginraiserror(15234,-1,-1)return (1)endelse if @type = 'V ' -- View => no physical data storage.beginraiserror(15235,-1,-1)return (1)endelse if @type in ('PK','UQ') -- no physical data storage. --?!?! too many similar messagesbeginraiserror(15064,-1,-1)return (1)endelse if @type = 'F ' -- FK => no physical data storage.beginraiserror(15275,-1,-1)return (1)endend
/*** Update usages if user specified to do so.*/
if @updateusage = 'true'beginif @objname is nulldbcc updateusage(0) with no_infomsgselsedbcc updateusage(0,@objname) with no_infomsgsprint '' end
set nocount on
/*** If @id is null, then we want summary data.*//* Space used calculated in the following way** @dbsize = Pages used** @bytesperpage = d.low (where d = master.dbo.spt_values) is** the # of bytes per page when d.type = ''E'' and** d.number = 1.** Size = @dbsize * d.low / (1048576 (OR 1 MB))*/if @id is nullbeginselect @dbsize = sum(convert(dec(15),size))from dbo.sysfileswhere (status & 64 = 0)
select @logsize = sum(convert(dec(15),size))from dbo.sysfileswhere (status & 64 <> 0)
select @bytesperpage = lowfrom master.dbo.spt_valueswhere number = 1and type = 'E'select @pagesperMB = 1048576 / @bytesperpage
select database_name = db_name(),database_size =ltrim(str((@dbsize + @logsize) / @pagesperMB,15,2) + 'MB'),'unallocated space' =ltrim(str((@dbsize -(select sum(convert(dec(15),reserved))from sysindexeswhere indid in (0, 1, 255))) / @pagesperMB,15,2)+ ' MB')
print ''/*** Now calculate the summary data.** reserved: sum(reserved) where indid in (0, 1, 255)*/insert into #spt_space (reserved)select sum(convert(dec(15),reserved))from sysindexeswhere indid in (0, 1, 255)
/*** data: sum(dpages) where indid < 2** + sum(used) where indid = 255 (text)*/select @pages = sum(convert(dec(15),dpages))from sysindexeswhere indid < 2select @pages = @pages + isnull(sum(convert(dec(15),used)), 0)from sysindexeswhere indid = 255update #spt_spaceset data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */update #spt_spaceset indexp = (select sum(convert(dec(15),used))from sysindexeswhere indid in (0, 1, 255))- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */update #spt_spaceset unused = reserved- (select sum(convert(dec(15),used))from sysindexeswhere indid in (0, 1, 255))
select reserved = ltrim(str(reserved * d.low / 1024.,15,0) +' ' + 'KB'),data = ltrim(str(data * d.low / 1024.,15,0) +' ' + 'KB'),index_size = ltrim(str(indexp * d.low / 1024.,15,0) +' ' + 'KB'),unused = ltrim(str(unused * d.low / 1024.,15,0) +' ' + 'KB')from #spt_space, master.dbo.spt_values dwhere d.number = 1and d.type = 'E'end
/*** We want a particular object.*/elsebegin/*** Now calculate the summary data.** reserved: sum(reserved) where indid in (0, 1, 255)*/insert into #spt_space (reserved)select sum(reserved)from sysindexeswhere 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 sysindexeswhere indid < 2and id = @idselect @pages = @pages + isnull(sum(used), 0)from sysindexeswhere indid = 255and id = @idupdate #spt_spaceset data = @pages
/* index: sum(used) where indid in (0, 1, 255) - data */update #spt_spaceset indexp = (select sum(used)from sysindexeswhere indid in (0, 1, 255)and id = @id)- data
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */update #spt_spaceset unused = reserved- (select sum(used)from sysindexeswhere indid in (0, 1, 255)and id = @id)update #spt_spaceset rows = i.rowsfrom sysindexes iwhere i.indid < 2and i.id = @idinsert into #temp1select name = object_name(@id),rows = convert(char(11), rows),reserved = ltrim(str(reserved * d.low / 1024.,15,0) +' ' + 'KB'),data = ltrim(str(data * d.low / 1024.,15,0) +' ' + 'KB'),index_size = ltrim(str(indexp * d.low / 1024.,15,0) +' ' + 'KB'),unused = ltrim(str(unused * d.low / 1024.,15,0) +' ' + 'KB')from #spt_space, master.dbo.spt_values dwhere d.number = 1and d.type = 'E'Drop table #spt_spaceendfetch next from cur_table into @objnameendClose cur_tableDEALLOCATE cur_tableSelect * from #temp1 order by len(baoliukj) desc,baoliukj descDrop table #temp1return (0)end
gmlxf(烛光) 提供的方法这么有效。
我用count(*)花了45:12秒才出来结果：276895925条记录。
原来1秒钟不到就可以出来了。
---
该方法获取表的记录总数的速度非常快，在毫秒级就可以完成，相比select count(*) 要快上数万倍，但是大家在运用该方法是一定要注意，该方法得到的表的总记录数不是一个精确值，原因是MS SQL 并不是实时更新该字段的值，而是定时更新，当从实践来看该值和精确值一般误差不大，如果你希望快速的粗略估算表的大小，建议你采用该方法。如果你希望得到精确值，那么请在执行上述语句前执行DBCC UPDATEUSAGE(DatabaseName,[TABLENAME]) WITH ROW_COUNTS 强制更新该字段的值，但这样第一次更新时会耗费大量的时间。