Query to return row count for each table (from MS Access)

A

Andrew John

Dear All,

I'm trying to right a query for MS Access, that returns the number of rows in each table in the database.
This query works fine for SQL server. Is there an MS Access equivalent?

- It needs to be a query, NOT VBA code, so I can't figure out how to "get at" tabledefs, or whatever Access uses (if
anything), as the equivalent of systables/sysindexs/information_schema's

declare @sCurTable varchar(400)
declare @sCurTableType varchar(400)
set nocount on
declare crsTables cursor for
select TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES
where TABLE_NAME not in ( 'sysconstraints', 'syssegments')

create table #TblCounts(TblName sysname, TblType varchar(30), QtyRows bigint)

open crsTables
fetch next from crsTables into @sCurTable, @sCurTableType

while @@fetch_status = 0
begin
insert into #TblCounts (TblName, TblType, QtyRows)
exec ('select ''' + @sCurTable + ''', ''' + @sCurTableType + ''', '''' + count(*) from ' + @sCurTable)
fetch next from crsTables into @sCurTable, @sCurTableType
end

select TblName, TblType, QtyRows
from #TblCounts
order by TblType desc, TblName

close crsTables
deallocate crsTables
drop table #TblCounts

Regards
AJ
 
R

Rick Gittins

In Access create the query as a PassThrough Query. In a PassThrough Query
you can put your SQL Server code in and it will run. You will need to go
into the properties for the PassThrough Query that you create and set you
ODBC string so it will not prompt you each time for it.

Rick
 
A

Andrew John

I was kinda hoping for a query, not VB/ASP code running multiple queries - one for each table
I guess the answer is - not possible

Thanks for the suggestions.

Regards
AJ
 
A

Aaron Bertrand [SQL Server MVP]

Yep, same result. Any way you can find the thread in groups.google.com
instead? I can't be the only one having issues...
 
G

Guest

Thanks Aaron. I will post the code again.


AMB

Aaron Bertrand said:
Yep, same result. Any way you can find the thread in groups.google.com
instead? I can't be the only one having issues...
Click to expand...
 
G

Guest

You can use a non-documented sp sp_msforeachtable (not recommended in
production code) or create a cursor to traverse information_schema.tables.

use northwind
go

exec sp_msforeachtable 'select ''?'', count(*) from ?'
go

create table #t (
tname sysname,
rcnt int
)

declare @tn sysname
declare @sql nvarchar(4000)

declare my_cursor cursor local fast_forward
for
select
quotename(table_schema) + '.' + quotename(table_name)
from
information_schema.tables
where
objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0

open my_cursor

while 1 = 1
begin
fetch next from my_cursor into @tn

if @@error != 0 or @@fetch_status != 0 break

set @sql = N'select ''' + @tn + N''', count(*) from ' + @tn

insert into #t
exec sp_executesql @sql
end

close my_cursor
deallocate my_cursor

select * from #t

drop table #t
go


AMB
 
A

Aaron Bertrand [SQL Server MVP]

You can use a non-documented sp sp_msforeachtable (not recommended in
production code) or create a cursor to traverse information_schema.tables.
Click to expand...

But not in MS Access... unless you know of some way to do this against an
Access linked server, like OPENQUERY or OPENROWSET. I think the user is
better off just running the SELECT COUNT query from client code, looping
through the tables using ADOX.
 
D

Dirk Goldgar

Andrew John said:
Dear All,

I'm trying to right a query for MS Access, that returns the number of
rows in each table in the database.
This query works fine for SQL server. Is there an MS Access
equivalent?

- It needs to be a query, NOT VBA code, so I can't figure out how to
"get at" tabledefs, or whatever Access uses (if anything), as the
equivalent of systables/sysindexs/information_schema's

declare @sCurTable varchar(400)
declare @sCurTableType varchar(400)
set nocount on
declare crsTables cursor for
select TABLE_NAME, TABLE_TYPE from INFORMATION_SCHEMA.TABLES
where TABLE_NAME not in ( 'sysconstraints', 'syssegments')

create table #TblCounts(TblName sysname, TblType varchar(30), QtyRows
bigint)

open crsTables
fetch next from crsTables into @sCurTable, @sCurTableType

while @@fetch_status = 0
begin
insert into #TblCounts (TblName, TblType, QtyRows)
exec ('select ''' + @sCurTable + ''', ''' + @sCurTableType +
''', '''' + count(*) from ' + @sCurTable) fetch next from crsTables
into @sCurTable, @sCurTableType
end

select TblName, TblType, QtyRows
from #TblCounts
order by TblType desc, TblName

close crsTables
deallocate crsTables
drop table #TblCounts

Regards
AJ
Click to expand...

Are you asking specifically about an Access query to get this
information for an Access (Jet) database? If so, the following (while
inefficient) will do it:

SELECT
MSysObjects.Name AS TableName,
DCount("*",[MSysObjects].[Name]) AS NRecs
FROM MSysObjects
WHERE (Left$([Name],1)<>"~")
AND (Left$([Name],4) <> "MSys")
AND (MSysObjects.Type In (1, 4, 6))
ORDER BY MSysObjects.Name;

Everybody else in this thread seems to be talking about getting info
from a SQL Server database, but it's not clear to me from your question
that that is what you want.
 
A

Andrew John

Hmm, cleaner? Undocumented/supported features of SQL server (sp_msForEachTable)
- not sure I agree there. I also specifically wanted a query so that I would get a rowset back :)

Never mind, knowing it's not possible allows me to give up, and do it the VB/multiple queries way.

I thought I was being clear it was for Access, not SQL Server - I just posted the SQL code because
a) I'm a darn site better at programming in SQL server than Access
b) To show exactly what I wanted.

Regards
AJ
 
G

Guest

Aaron,

You are completely right. I was thinking in SQL Server and not MS Access, my
post does not make any sense.


AMB
 
A

Aaron Bertrand [SQL Server MVP]

Hmm, cleaner? Undocumented/supported features of SQL server
(sp_msForEachTable)
- not sure I agree there.
Click to expand...

You'll notice I specified two DIFFERENT things in my post and in the article
it referenced. A cleaner way:

SELECT
[TableName] = so.name,
[RowCount] = MAX(si.rows)
FROM
sysobjects so,
sysindexes si
WHERE
so.xtype = 'U'
AND
si.id = OBJECT_ID(so.name)
GROUP BY
so.name
ORDER BY
2 DESC

(Any undocumented/supported features of SQL Server there? No, of course
not. And the code is much cleaner and by definition much less impact on the
system than your cursor.)
I also specifically wanted a query so that I would get a rowset back :)
Click to expand...

I'm not sure what you're talking about here. The above gets a rowset back
just like your cursor option.
Never mind, knowing it's not possible allows me to give up, and do it the
VB/multiple queries way.
Click to expand...

Did you see Dirk's post? There may be a way after all.
I thought I was being clear it was for Access, not SQL Server - I just
posted the SQL code because
a) I'm a darn site better at programming in SQL server than Access
b) To show exactly what I wanted.
Click to expand...

Yes, I was clear on that all along. I was trying to show you a better way
to do your SQL Server query in SQL Server. Since this is a SQL Server
group, and all. I'll keep quiet next time.
 
A

Andrew John

Yes, thank you - thats exactly what i was after, and it works a treat.

I suppose my question was a little ambiguos, considering you can replace Jet with MSDE,
or use Access as the front end to SQL Server. I did say Access in the post title, in the post,
and cross-posted to the Access newsgroup. So I'm a bit disconcerted that it wasn't clear.

Weird that people didn't work out why I was posting perfectly working SQL server code, if I wanted a SQL Server answer
:)

Thanks again.


"Dirk Goldgar"
 
A

Aaron Bertrand [SQL Server MVP]

Weird that people didn't work out why I was posting perfectly working SQL
server code, if I wanted a SQL Server answer :)
Click to expand...

Not everybody reads every line in every post. But being explicit doesn't
hurt, either.
 
D

Dirk Goldgar

Andrew John said:
Yes, thank you - thats exactly what i was after, and it works a treat.

I suppose my question was a little ambiguos, considering you can
replace Jet with MSDE,
or use Access as the front end to SQL Server. I did say Access in
the post title, in the post,
and cross-posted to the Access newsgroup. So I'm a bit disconcerted
that it wasn't clear.

Weird that people didn't work out why I was posting perfectly working
SQL server code, if I wanted a SQL Server answer :)
Click to expand...

In all fairness, I wasn't at all sure that you wanted a "pure Access"
answer, since you cross-posted to the .sqlserver group. And it seems
like you hit on a question that is interesting in the SQL Server
context, too.
 
Top