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.

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

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

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.).

Fantastic.

Thank you very much Derrick

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