largest tables

[Baudewijn Vermeire]

I want to program piece of code to retrieve the table size of the databases
on the SQL server.
Is there an easy query or other way to do that? I cannot predict the table
properties.

regards
bav

[Anith Sen]

For a single table:

--#1. sp_spaceused with @updateusage argument
--#2. sp_MStablespace <tbl>

For all the tables there are different methods:

--#1 (Unreliable)

SELECT CAST(sysobjects.[name] AS VARCHAR) AS [TABLE_NAME],
sysindexes.[rows] AS [NO_OF_ROWS],
sysindexes.reserved AS [RESERVED_SPACE],
sysindexes.used AS [USED_SPACE]
FROM sysobjects
INNER JOIN sysindexes
ON sysobjects.[id] = sysindexes.[id]
WHERE sysindexes.indid < 2
AND sysobjects.type = 'U'

--#2 (Reliable; you can use system tables as well.)

DECLARE @TableName SYSNAME
DECLARE COUNT_CUR CURSOR READ_ONLY FOR
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table'
OPEN COUNT_CUR
FETCH NEXT FROM COUNT_CUR INTO @TableName
WHILE @@fetch_status = 0
BEGIN
EXEC ('SELECT ''' + @TableName + ''', COUNT(*)
FROM [' + @TableName + ']')
FETCH NEXT FROM COUNT_CUR INTO @TableName
END
CLOSE COUNT_CUR
DEALLOCATE COUNT_CUR

--#3 (Undocumented)

EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'
OR
EXEC sp_msforeachtable 'SELECT COUNT(*), PARSENAME(''?'', 1) FROM ?'

--
- Anith
(Please respond only to newsgroups)