How do I print a list of tables, reports, forms, modules?

[VelvetVoice]

Hi! How do I print a list of tables, reports, forms, modules? I was looking at another forum and it gave me an SQL of how to do it, but I wasn't sure where to put it.

 

[John Big Booty]

Perhaps the article here will explain what you need to do.

 

[VelvetVoice]

Got it. Here's my solution:

SELECT IIf([MSysObjects].[Type]=1,'TABLE',
IIf([MSysObjects].[Type]=5,'QUERY',
IIf([MSysObjects].[Type]=-32768,'FORM',
IIf([MSysObjects].[Type]=-32764,'REPORT',
IIf([MSysObjects].[Type]=-32766,'MACRO',
IIf([MSysObjects].[Type]=-32761,'MODULE',
IIf([MSysObjects].[Type]=-32756,'PAGE',
IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',
IIf([MSysObjects].[Type]=6,'TABLE LINKED','Unknown'))))))))) AS ObjectType,
MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*"
And (MSysObjects.Name) Not Like "Msys*")
AND ((MSysObjects.Type) Not In (2,3)))
ORDER BY IIf([MSysObjects].[Type]=1,'TABLE',
IIf([MSysObjects].[Type]=5,'QUERY',
IIf([MSysObjects].[Type]=-32768,'FORM',
IIf([MSysObjects].[Type]=-32764,'REPORT',
IIf([MSysObjects].[Type]=-32766,'MACRO',
IIf([MSysObjects].[Type]=-32761,'MODULE',
IIf([MSysObjects].[Type]=-32756,'PAGE',
IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',
IIf([MSysObjects].[Type]=6,'TABLE LINKED','Unknown'))))))))),
MSysObjects.Name;

Paste this SQL in a query, I hit the Query design button. Run it, and you have your list.

 

[Steve@trop]

I came across this in a search. Even though it is over a year old it still did exactly what I needed!

Thanks!

Steve

 

[CCS]

This is perfect - thank you!
You should submit it to MS and suggest they include it with the documenter

Got it. Here's my solution:

SELECT IIf([MSysObjects].[Type]=1,'TABLE',
IIf([MSysObjects].[Type]=5,'QUERY',
IIf([MSysObjects].[Type]=-32768,'FORM',
IIf([MSysObjects].[Type]=-32764,'REPORT',
IIf([MSysObjects].[Type]=-32766,'MACRO',
IIf([MSysObjects].[Type]=-32761,'MODULE',
IIf([MSysObjects].[Type]=-32756,'PAGE',
IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',
IIf([MSysObjects].[Type]=6,'TABLE LINKED','Unknown'))))))))) AS ObjectType,
MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Name) Not Like "~*"
And (MSysObjects.Name) Not Like "Msys*")
AND ((MSysObjects.Type) Not In (2,3)))
ORDER BY IIf([MSysObjects].[Type]=1,'TABLE',
IIf([MSysObjects].[Type]=5,'QUERY',
IIf([MSysObjects].[Type]=-32768,'FORM',
IIf([MSysObjects].[Type]=-32764,'REPORT',
IIf([MSysObjects].[Type]=-32766,'MACRO',
IIf([MSysObjects].[Type]=-32761,'MODULE',
IIf([MSysObjects].[Type]=-32756,'PAGE',
IIf([MSysObjects].[Type]=8,'RELATIONSHIPS',
IIf([MSysObjects].[Type]=6,'TABLE LINKED','Unknown'))))))))),
MSysObjects.Name;

Paste this SQL in a query, I hit the Query design button. Run it, and you have your list.

 

[Alien]

Many thanks for this. It's just what I wanted - and it worked wonderfully.
Very grateful.