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

[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

 

[Aaron Bertrand [SQL Server MVP]]

http://www.aspfaq.com/2273

 

[Alejandro Mesa]

See if this helps:

http://support.microsoft.com/newsgr...-4714-ac5d-b233c6ec8001&sloc=en-us&sloc=en-us


AMB

 

[Aaron Bertrand [SQL Server MVP]]

I get "Sorry, the page you requested is not available."

 

[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

 

[Alejandro Mesa]

Thanks Aaron. I can see it without problems, can you try one more time, please?


AMB

 

[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

 

[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...

 

[Alejandro Mesa]

Thanks Aaron. I will post the code again.


AMB

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

 

[Alejandro Mesa]

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

 

[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.

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.

 

[Aaron Bertrand [SQL Server MVP]]

This query works fine for SQL server.

BTW, there are cleaner ways to do this in SQL Server, as well as a few
undocumented methods to get more information.
http://www.aspfaq.com/2428

 

[Dirk Goldgar]

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

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.

 

[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

 

[Alejandro Mesa]

Aaron,

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


AMB

 

[Aaron Bertrand [SQL Server MVP]]

Hmm, cleaner? Undocumented/supported features of SQL server

(sp_msForEachTable)
- not sure I agree there.

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

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.

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.

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.

 

[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"

 

[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

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

 

[Andrew John]

Tut, Tut. Referencing sysobjects and sysindexes in a query and calling it cleaner.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ia-iz_4pbn.asp

I thought that was argued out years ago, when I used to post here regularly?

Thank you for your response, I was going to use the multiple query code in the faq,
but Dirk Goldgar's query exactly fits my requirement.

Regards
AJ

 

[Dirk Goldgar]

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

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.