Solved

SQL Help Please

Posted on 2004-09-28
14
454 Views
Last Modified: 2010-10-05
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
Comment
Question by:smurff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +1
14 Comments
 
LVL 26

Expert Comment

by:Hilaire
ID: 12168522
Please try this,
you'll get approximate number of rows along with table size

sp_msforeachtable 'sp_spaceused ''?'''
0
 
LVL 26

Expert Comment

by:Hilaire
ID: 12168533
or

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

Expert Comment

by:Hilaire
ID: 12168544
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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 7

Expert Comment

by:FDzjuba
ID: 12168745
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
 
LVL 3

Author Comment

by:smurff
ID: 12168765
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
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12168879
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
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12168921
>>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
 
LVL 34

Expert Comment

by:arbert
ID: 12169120
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
 
LVL 3

Author Comment

by:smurff
ID: 12170978
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
 
LVL 7

Accepted Solution

by:
FDzjuba earned 125 total points
ID: 12171213
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
 
LVL 7

Expert Comment

by:FDzjuba
ID: 12198776
ping!
0

Featured Post

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question