SQL Help Please

Hi,

I have a table called table_list which has a list of tables an application uses. What I would like to do is have some sql that would list all those tables and have the amount of rows for each table.

e.g.

table1 200
table2 10
table3 100000000


Many thanks
Smurff
LVL 3
smurffAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
FDzjubaConnect With a Mentor Commented:
Line 22 is - SELECT * FROM #resultset, hmmm, strange. IF it creates temp table correctly,
try the following, temp table as global, it works fine in my environment, tried on two systems
----------------------------------

DECLARE @tablename nvarchar(255);
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;
0
 
HilaireCommented:
Please try this,
you'll get approximate number of rows along with table size

sp_msforeachtable 'sp_spaceused ''?'''
0
 
HilaireCommented:
or

sp_msforeachtable 'select ''?'' as table_name, count(*) as row_number from ?'
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
HilaireCommented:
The first solution will be faster,
the second will be more accurate (sp_spaceused returns estimated number of rows, count(*) returns the exact count)
0
 
FDzjubaCommented:
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;
0
 
smurffAuthor Commented:
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
0
 
FDzjubaCommented:
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;
0
 
FDzjubaCommented:
>>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
0
 
arbertCommented:
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.
0
 
smurffAuthor Commented:
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
0
 
FDzjubaCommented:
ping!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.