SQL Help Please

Please try this,
you'll get approximate number of rows along with table size

sp_msforeachtable 'sp_spaceused ''?'''

or

sp_msforeachtable 'select ''?'' as table_name, count(*) as row_number from ?'

The first solution will be faster,
the second will be more accurate (sp_spaceused returns estimated number of rows, count(*) returns the exact count)

DECLARE @tablename nvarchar(255);

--DROP TABLE #resultset;
CREATE TABLE #resultset
( tablename nvarchar(255),
  TableRowcount int)


DECLARE tables CURSOR FOR
SELECT tablename from TableNames


Open tables
FETCH NEXT FROM tables
INTO @tablename


not the best solution, but works

WHILE @@FETCH_STATUS = 0
BEGIN
 EXEC('INSERT INTO #resultset SELECT '''+@tablename+''' AS tablename,COUNT(*) as TableRowcount FROM '+@tablename)
 FETCH NEXT FROM tables
 INTO @tablename
END

SELECT * FROM #resultset

CLOSE tables
DEALLOCATE tables
DROP TABLE #resultset;

Hi,

Thanks but I get this

Server: Msg 208, Level 16, State 1, Procedure sp_MSforeach_worker, Line 91
Invalid object name '#qtemp'.

Is there a way of doing this just using sql so i can use it on another db?

Thanks
Smurff

removed my comment in the middle of the script :)

---
script
----
DECLARE @tablename nvarchar(255);

--DROP TABLE #resultset;
CREATE TABLE #resultset
( tablename nvarchar(255),
  TableRowcount int)

DECLARE tables CURSOR FOR
SELECT tablename from TableNames

Open tables
FETCH NEXT FROM tables
INTO @tablename

WHILE @@FETCH_STATUS = 0
BEGIN
 EXEC('INSERT INTO #resultset SELECT '''+@tablename+''' AS tablename,COUNT(*) as TableRowcount FROM '+@tablename)
 FETCH NEXT FROM tables
 INTO @tablename
END

SELECT * FROM #resultset

CLOSE tables
DEALLOCATE tables
DROP TABLE #resultset;

>>Is there a way of doing this just using sql so i can use it on another db?<<
you have to use some sort of dynamic sql construct to retrive table names, therefore i can't think of single SQL statement for the above task

If you can work with an "estimated " row count and you don't need an actual, this will give you quick results:

SELECT so.[name] as [table name],rowcnt
   , CASE WHEN si.indid between 1 and 254
      THEN si.[name] ELSE NULL END
      AS [Index Name]
   , si.indid
   FROM sysindexes si
     INNER JOIN sysobjects so
       ON si.id = so.id
   WHERE si.indid < 2
    AND so.type = 'U' -- Only User Tables
    AND so.[name] != 'dtproperties'
   ORDER BY so.[name]



I don't recommend using system tables, but if you just need a quick estimate (and you aren't going to use the job all the time), the above works pretty good.

FDzjuba,

Thanks. I ran your script and it returned an error

Server: Msg 208, Level 16, State 1, Line 22
Invalid object name '#resultset'.

(1 row(s) affected)

Im using MSSql 2000 on NT
Thanks
Smurff

ping!