Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2002-10-25 : 09:41:13
|
| John writes "How do I obtain a list of all tables within a database together with the number of records in each? Eg:Tablename RecordstblA 100tblB 40tblC 1000I'm currently using SQL Server 7 running on Win NT 4 Workstation." |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2002-10-25 : 10:37:01
|
| exec sp_msforeachtable 'select ''?'' as Tablename, count(*) as Records from ?'Jay White{0} |
 |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-25 : 12:58:06
|
| select Tablename = t.name, Records = i.rowsfrom sysobjects t, i.sysindexeswhere t.xtype = 'U'and i.id = t.idand i.indid in (0,1)==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Kevin Snow
Posting Yak Master
149 Posts |
Posted - 2002-10-25 : 13:12:51
|
| nr-I remember a forum discussing the use of the row count from sysindexes table. It suggested that the number there was close to the actual row count, but was not always guaranteed to be up to the second accurate.thoughts? |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-25 : 13:21:50
|
| For 6.5 it was inaccurate.For 7/2000 I've never found it to be wrong but then I haven't needed it to be accurate so maybe....If you have a volatile database I wouldn't try using count(*) against a table (and especially not all tables) - almost certain to cause trouble.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-10-25 : 13:23:28
|
| You'd have to have queried sysindexes either during, or immediately (and I mean 10 ms at most) after a bulk insert operation of some kind. In general the only time that sysindexes will be off is if the table is not indexed at all (and Nigel's query won't return anything for such tables) |
|
|
|
rkc01
Starting Member
43 Posts |
Posted - 2002-10-25 : 16:42:44
|
| Nigel's query is about a gazillion times faster too. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-10-28 : 04:46:42
|
quote:
You'd have to have queried sysindexes either during, or immediately (and I mean 10 ms at most) after a bulk insert operation of some kind. In general the only time that sysindexes will be off is if the table is not indexed at all (and Nigel's query won't return anything for such tables)
True for 6.5 where it isn't reliable anyway.For v7 and 2000 tables always have an entry in sysindexes. If there is no clustered index it will have indid 0. If you add a clustered index this entry will be replaced by one with indid 1.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
List of tables and record count