would like to check 100 tables for a largest in size

Hi TransBind,

Here is a script that I use to get the same thing you are looking for:

-- Script for record count for each table for MSSQL 7.0 / 2000

SET NOCOUNT ON
GO

DECLARE @V_SQL               NVARCHAR(2000),
        @V_TABLE_NAME        VARCHAR(30),
        @V_REC_CNT           INT

DECLARE CUR_LIST_TABLE CURSOR FOR
        SELECT NAME FROM SYSOBJECTS WHERE TYPE = 'U'
 
BEGIN
   -- If a table with the same name (Z_TAB_REC_CNT) as the one used to hold the record counts already exist,
   -- then it is dropped.
   IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME = 'Z_TAB_REC_CNT' AND TYPE = 'U') BEGIN
      BEGIN TRANSACTION
      EXECUTE SP_EXECUTESQL N'DROP TABLE Z_TAB_REC_CNT'
      COMMIT TRANSACTION
   END

   -- Create the temporary table Z_TAB_REC_CNT that contains the table names and the record count for each of them.
   BEGIN TRANSACTION
   EXECUTE SP_EXECUTESQL N'CREATE TABLE Z_TAB_REC_CNT (TABLENAME NCHAR(30),RECCOUNT INTEGER NULL)'
   COMMIT TRANSACTION
   

   -- Populates the temp table with a record for each user table along with its number of records in it.
   OPEN CUR_LIST_TABLE
   FETCH NEXT FROM CUR_LIST_TABLE INTO @V_TABLE_NAME
   WHILE @@FETCH_STATUS = 0 BEGIN
      -- Insert tablename into temp table.
      BEGIN TRANSACTION
      SET @V_SQL = N'INSERT INTO Z_TAB_REC_CNT (TABLENAME) VALUES (''' + @V_TABLE_NAME + ''')'
      EXECUTE SP_EXECUTESQL @V_SQL
      COMMIT TRANSACTION;
     
      -- Updates temp table with record count.
      BEGIN TRANSACTION
      SET @V_SQL = N'UPDATE Z_TAB_REC_CNT SET RECCOUNT = (SELECT COUNT(*) FROM ' + @V_TABLE_NAME + ')'
      SET @V_SQL = @V_SQL + N'WHERE TABLENAME = ''' + @V_TABLE_NAME + ''''
      EXECUTE SP_EXECUTESQL @V_SQL
      COMMIT TRANSACTION
     
      FETCH NEXT FROM CUR_LIST_TABLE INTO @V_TABLE_NAME
   END;
   CLOSE CUR_LIST_TABLE
   DEALLOCATE CUR_LIST_TABLE
END;

GO

SELECT TABLENAME,
       RECCOUNT,
FROM Z_TAB_REC_CNT
ORDER BY RECCOUNT DESC
GO

interesting approach ... however your scripts produces total row count for every user table in a database ... perhaps i wasn't clear ... but i am interested in a size of table in a database ...

for example if u do sp_spaceused tableNAme that will give u number of rows and space occupied on a database  etc ... I would like to see a report which will show this

[table Name]  [reserved space]  
table 1             32 KB
table 2             12kb
table 3             10kb

row count does not indicate that a table reserves more or less space ...

i could do this as well

select 'exec sp_spaceused ' + sysobjects.name from sysobjects where sysobjects.xtype = 'u'

and paste that in QA and compare and but thats not an idea solution or report

I like the approach aneeshattingal. I used to use sp_MSForEachTable but it seems to have so much overhead that it took more than a minute to return what the cursor does in a second. **Much cleaner though***

You could use that and just change the select statement at the bottom to get what you want:

SELECT name, reserved, rows
FROM #temp
order by cast(patindex('%[^0-9]%',reserved) as integer) desc

The solution actually builds a table with the results from sp_spaceused. It doesn't just generate select statements.

i also like his approach ..
this syntax i don't understand though

exec sp_MSForEachTable "Sp_SpaceUsed '?' "

???

sp_MSForEachTable  is an undocumented sp which loops thru all the tables and the '?' serves as a place holder for the table name.

Similarto this

sp_MSForEachDB whic loops thru all the databases

very cool ... thanks