sql statement to return all fields from all tables

Question

select * from *

yes this is what I want I want to return all fields from all table in my ACCESS database regardless the fields names or the tables names!!!

for example if I have table1 and table2 as tables in my database access file what I want is to generate this statement

 select * from table1,table2

from sql query which run fine in access query

but again I don't know the name of the tables which in the access file.

is there a way for that?

Answer 1

To achieve this you should use from Master Database in SQL Server

Master Database contain information about all fields of all of your DBs. also there is CatalogView to gain information about an exact DB.

just use Master Storeprocedures

Answer 2

This query will list all the table names

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=6))

You can also use this bit of code to go through every table and list every field

Public Sub List_fields_in_tables()
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

For Each tdf In CurrentDb.TableDefs
    For Each fld In tdf.Fields
        Debug.Print "Table='" & tdf.name & "'  Field='" & fld.name & "'"

    Next fld

Next tdf

End Sub

Hope this helps