Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 458
  • Last Modified:

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
0
smurff
Asked:
smurff
  • 5
  • 3
  • 2
  • +1
1 Solution
 
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
 
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
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:
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
 
FDzjubaCommented:
ping!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now