The code supplied by twinnyfo will ONLY find linked Access/Excel/text tables. Local tables won't be listed.
If you need local tables, those have MSysObjects.Type=1 & ODBC tables such as SQL Server have MSysObjects.Type=4
To get all tables, you need:
- SELECT MSysObjects.Name
-
FROM MSysObjects
-
WHERE MSysObjects.Type IN (1,4,6)
-
ORDER BY MSysObjects.Name;
That code will also list system tables which you probably don't want. So a further modification will exclude them but show all other local & linked tables:
- SELECT MSysObjects.Name
-
FROM MSysObjects
-
WHERE ((Not ((MSysObjects.Name) Like "MSys*"
-
Or (MSysObjects.Name) Like "USys*"
-
Or (MSysObjects.Name) Like "f_*"))
-
AND ((MSysObjects.Type) In (1,4,6)))
-
ORDER BY MSysObjects.Name;
-