Shulou(Shulou.com)05/31 Report--

How to view all table sizes in sql? for this problem, this article describes in detail the corresponding analysis and solutions, hoping to help more partners who want to solve this problem to find a more simple and easy way.

The code is as follows:

Declare @ id intdeclare @ type character (2) declare @ pages intdeclare @ dbname sysnamedeclare @ dbsize dec (1510) declare @ bytesperpage dec (1510) declare @ pagesperMB dec (15)

Create table # spt_space ([objid] int null, [rows] int null, [reserved] dec (15) null, [data] dec (15) null, [indexp] dec (15) null, [unused] dec (15) null)

Set nocount on

-- Create a cursor to loop through the user tables

Declare c_tables cursor forselect id from sysobjects where xtype ='U'

Open c_tables fetch next from c_tables into @ id

While @ @ fetch_status = 0begin / * Code from sp_spaceused * / insert into # spt_space (objid, reserved) select objid = @ id, sum (reserved) from sysindexes where indid in (0,1,255) and id = @ id select @ pages = sum (dpages) from sysindexes where indid < 2 and id = @ id select @ pages = @ pages + isnull (sum (used), 0) from sysindexes where indid = 255and id = @ id update # spt_space set data = @ id / * index: sum (used) where indid in (0,1) Data * / update # spt_space set indexp = (select sum (used) from sysindexes where indid in (0,1,255) and id = @ id)-data where objid = @ id / * 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) And id = @ id) where objid = @ id update # spt_space set [rows] = I. [rows] from sysindexes i where i.indid < 2 and i.id = @ id and objid = @ id fetch next from c_tables into @ idend

Select TableName = (select left (name,60) from sysobjects where id = objid), [Rows] = convert (char (11), rows), ReservedKB = ltrim (str (reserved * d.low / 1024.pr. 15pp.0) +'+ 'KB'), DataKB = ltrim (str (data * d.low / 1024.pr. 15pr 0) +' + 'KB'), IndexSizeKB = ltrim (indexp * d.low / 1024.pr 15pp0) +' + 'KB'), UnusedKB = ltrim (str (unused * d.low / 1024. 15) +'+ 'KB') from # spt_space, master.dbo.spt_values dwhere d.number = 1and d.type =' E'

Order by reserved desc

Drop table # spt_spaceclose c_tablesdeallocate c_tables

This is the answer to the question about how to view all the table sizes in sql. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.