If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Query showing Tablesize in MB

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 32
Query showing Tablesize in MB

Hello all,

Is there an SQL query that can be run against a database that displays both the number of rows and the physical size of the tables in that database?

I'm basically after the information displayed in Taskpad view >> Table Info, but in an easy to manipulate and publish format.

Thanks for your time.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Kansas City, MO
Posts: 734
The sp_spaceused query gives you the same information that taskpad gives you. If you call it by itself, it gives you a summary of the whole database. If you call it like:

EXEC sp_spaceused 'owner.table'

then it gives you the information for a specific table.

SET NOCOUNT ON

--Get a summary of the entire database.
EXEC sp_spaceused

--Declare needed variables.
DECLARE
@max INT,
@min INT,
@owner NVARCHAR(256),
@table_name NVARCHAR(256),
@sql NVARCHAR(4000)

DECLARE @table TABLE(
ident INT IDENTITY(1,1) PRIMARY KEY,
owner_name NVARCHAR(256),
table_name NVARCHAR(256))

IF (SELECT OBJECT_ID('tempdb..#results')) IS NOT NULL
BEGIN
DROP TABLE #results
END

CREATE TABLE #results(
ident INT IDENTITY(1,1) PRIMARY KEY, --Will be used to update the owner.
table_name NVARCHAR(256),
owner_name NVARCHAR(256),
table_rows INT,
reserved_space NVARCHAR(55),
data_space NVARCHAR(55),
index_space NVARCHAR(55),
unused_space NVARCHAR(55))

--Loop through statistics for each table.
INSERT @table(owner_name, table_name)
SELECT
su.name,
so.name
FROM
sysobjects so
INNER JOIN sysusers su ON so.uid = su.uid
WHERE
so.xtype = 'U'

SELECT
@min = 1,
@max = (SELECT MAX(ident) FROM @table)

WHILE @min <= @max
BEGIN

SELECT
@owner = owner_name,
@table_name = table_name
FROM
@table
WHERE
ident = @min

SELECT @sql = 'EXEC sp_spaceused ''[' + @owner + '].[' + @table_name + ']'''

INSERT #results(table_name, table_rows, reserved_space, data_space, index_space, unused_space)
EXEC (@sql)

UPDATE #results
SET owner_name = @owner
WHERE ident = (SELECT MAX(ident) FROM #results)

SELECT @min = @min + 1
END

SELECT * FROM #results
__________________
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA.
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 32
Thanks Derrick.

I understand that

EXEC sp_spaceused

returns the total for the whole database, and that

EXEC sp_spaceused 'owner.table'

returns specific single table information, but I don't understand what the SQL you've supplied does -

Is that the SQL required to create a procedure that will return the information for all tables in a single database???? (which was what my original question was looking for)

Thanks
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Apr 2004
Location: Kansas City, MO
Posts: 734
Yes. Just open that up in Query Analyzer and run it on whatever database you want. It will give you a summary of every table (rows, size, etc.).
__________________
MeanOldDBA
derrickleggett@hotmail.com
When life gives you a lemon, fire the DBA.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2004
Posts: 32
Fantastic.

Thank you very much Derrick
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Nov 2009
Posts: 1
something a little zippier

after a little digging thru sp_spaceused, we have:

--- SQL2000
select o.name
, reserved = isnull(sum(reserved), 0)
, pages = isnull(sum(case when indid in (0, 1) then dpages else used end), 0)
, IXpages = isnull(sum(case when indid in (0, 1) then used - dpages end), 0)
, unused = isnull(sum(reserved - used), 0)
, rows = max(rows)
from sysobjects o
join sysindexes i on i.id = o.id
where i.indid in (0,1,255) and o.xtype = 'U'
group by o.name order by 3 desc --biggest tables first

--- SQL2005
select o.name
, reservedpages = sum(a.total_pages)
, usedpages = sum(a.used_pages)
, pages = sum(case when a.type <> 1 then a.used_pages
when p.index_id < 2 then a.data_pages else 0 end)
, rows = sum(case when (p.index_id < 2) and (a.type = 1) then p.rows else 0 end)
from sys.objects o
join sys.partitions p on p.object_id = o.object_id
join sys.allocation_units a on p.partition_id = a.container_id
where o.type = 'U'
group by o.name
order by 3 desc -biggest tables first
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On