sql statement to return all fields from all tables

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!!!

f开发者_如何学Goor 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?

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

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

Master database contains information about all columns of all of your databases. also there is catalog view to gain information about an exact database.

Just use Master stored procedures