Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

selecting table names

Discussion in 'Information Technology' started by srikanthr@gmail.com, Jul 28, 2006.

  1. srikanthr@gmail.com

    srikanthr@gmail.com
    Expand Collapse
    Guest

    Is there any query to get all the table names in an mdb file. i.e
    similar to select * from information_schema.tables will fetch all the
    tables names in a database in SQL Server.
     
  2. Loading...

    Similar Threads Forum Date
    As A Child, Public Marches Of Sikhism Made Me Uncomfortable. They Still Do . Why ? Whats The Logic Hard Talk Oct 16, 2016
    Arts/Society Backyard Vegetable Garden Language, Arts & Culture Dec 27, 2013
    Heritage Now a Vegetable Market, Ranjit Singh's Royal Haveli a Picture of Neglect History of Sikhism Nov 11, 2013
    Sikhism Helium: 1984 and the "Periodic Table of Hate" (Jaspreet Singh) Book Reviews & Editorials Oct 28, 2013
    Heritage How our entire history was dumped in a horse stable History of Sikhism Oct 28, 2013

  3. Nikos Yannacopoulos

    Nikos Yannacopoulos
    Expand Collapse
    Guest

    The table you are looking for in Access is MSysObjects. This query:

    SELECT Name FROM MSysObjects
    WHERE Type = 1 AND NAme Not Like "MSys*"

    will return all native Access (Jet) tables. You might also want to
    include type 6 (linked tables). Have a look at the table (Tools >
    Options, tab View, click Hidden Objects) to see all object types.

    HTH,
    Nikos
     
  4. srikanthr@gmail.com

    srikanthr@gmail.com
    Expand Collapse
    Guest

    Thanks a lot Nikos

    Nikos Yannacopoulos wrote:
    > The table you are looking for in Access is MSysObjects. This query:
    >
    > SELECT Name FROM MSysObjects
    > WHERE Type = 1 AND NAme Not Like "MSys*"
    >
    > will return all native Access (Jet) tables. You might also want to
    > include type 6 (linked tables). Have a look at the table (Tools >
    > Options, tab View, click Hidden Objects) to see all object types.
    >
    > HTH,
    > Nikos
     
  5. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    In addition, type 4 is the tables linked using ODBC.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
    news:uQfrQgxpGHA.2256@TK2MSFTNGP03.phx.gbl...
    > The table you are looking for in Access is MSysObjects. This query:
    >
    > SELECT Name FROM MSysObjects
    > WHERE Type = 1 AND NAme Not Like "MSys*"
    >
    > will return all native Access (Jet) tables. You might also want to
    > include type 6 (linked tables). Have a look at the table (Tools >
    > Options, tab View, click Hidden Objects) to see all object types.
    >
    > HTH,
    > Nikos
     
  6. srikanthr@gmail.com

    srikanthr@gmail.com
    Expand Collapse
    Guest

    Is it possible to get the table constraints like PK, FK details. Will
    "msysrelationships" table help if so what are its columns.

    Douglas J Steele wrote:
    > In addition, type 4 is the tables linked using ODBC.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
    > news:uQfrQgxpGHA.2256@TK2MSFTNGP03.phx.gbl...
    > > The table you are looking for in Access is MSysObjects. This query:
    > >
    > > SELECT Name FROM MSysObjects
    > > WHERE Type = 1 AND NAme Not Like "MSys*"
    > >
    > > will return all native Access (Jet) tables. You might also want to
    > > include type 6 (linked tables). Have a look at the table (Tools >
    > > Options, tab View, click Hidden Objects) to see all object types.
    > >
    > > HTH,
    > > Nikos
     
  7. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Yes
    Ccolumn
    Grbit
    Icolumn
    Szcolumn
    Szobject
    Szreferencedcolumn
    Szreferencedobject
    Szrelationship

    Field Order
    ???
    ???
    Child Column
    Child Table
    Parent Column
    Parent Table
    Relation Name



    HTH

    Pieter


    <srikanthr@gmail.com> wrote in message
    news:1152878385.061115.167190@i42g2000cwa.googlegroups.com...
    > Is it possible to get the table constraints like PK, FK details. Will
    > "msysrelationships" table help if so what are its columns.
    >
    > Douglas J Steele wrote:
    >> In addition, type 4 is the tables linked using ODBC.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
    >> news:uQfrQgxpGHA.2256@TK2MSFTNGP03.phx.gbl...
    >> > The table you are looking for in Access is MSysObjects. This query:
    >> >
    >> > SELECT Name FROM MSysObjects
    >> > WHERE Type = 1 AND NAme Not Like "MSys*"
    >> >
    >> > will return all native Access (Jet) tables. You might also want to
    >> > include type 6 (linked tables). Have a look at the table (Tools >
    >> > Options, tab View, click Hidden Objects) to see all object types.
    >> >
    >> > HTH,
    >> > Nikos

    >
     
  8. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    I don't believe there's any way to query that information from the system
    tables. You need to use DAO or ADOX.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    <srikanthr@gmail.com> wrote in message
    news:1152878385.061115.167190@i42g2000cwa.googlegroups.com...
    > Is it possible to get the table constraints like PK, FK details. Will
    > "msysrelationships" table help if so what are its columns.
    >
    > Douglas J Steele wrote:
    > > In addition, type 4 is the tables linked using ODBC.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
    > > news:uQfrQgxpGHA.2256@TK2MSFTNGP03.phx.gbl...
    > > > The table you are looking for in Access is MSysObjects. This query:
    > > >
    > > > SELECT Name FROM MSysObjects
    > > > WHERE Type = 1 AND NAme Not Like "MSys*"
    > > >
    > > > will return all native Access (Jet) tables. You might also want to
    > > > include type 6 (linked tables). Have a look at the table (Tools >
    > > > Options, tab View, click Hidden Objects) to see all object types.
    > > >
    > > > HTH,
    > > > Nikos

    >
     
  9. Pieter Wijnen

    Pieter Wijnen
    Expand Collapse
    Guest

    Yes
    Ccolumn
    Grbit
    Icolumn
    Szcolumn
    Szobject
    Szreferencedcolumn
    Szreferencedobject
    Szrelationship

    Field Order
    ???
    ???
    Child Column
    Child Table
    Parent Column
    Parent Table
    Relation Name



    HTH

    Pieter


    <srikanthr@gmail.com> wrote in message
    news:1152878385.061115.167190@i42g2000cwa.googlegroups.com...
    > Is it possible to get the table constraints like PK, FK details. Will
    > "msysrelationships" table help if so what are its columns.
    >
    > Douglas J Steele wrote:
    >> In addition, type 4 is the tables linked using ODBC.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Nikos Yannacopoulos" <nyannacoREMOVETHISBIT@in.gr> wrote in message
    >> news:uQfrQgxpGHA.2256@TK2MSFTNGP03.phx.gbl...
    >> > The table you are looking for in Access is MSysObjects. This query:
    >> >
    >> > SELECT Name FROM MSysObjects
    >> > WHERE Type = 1 AND NAme Not Like "MSys*"
    >> >
    >> > will return all native Access (Jet) tables. You might also want to
    >> > include type 6 (linked tables). Have a look at the table (Tools >
    >> > Options, tab View, click Hidden Objects) to see all object types.
    >> >
    >> > HTH,
    >> > Nikos

    >




    --
    ----------------------------------------
    I am using the free version of SPAMfighter for private users.
    It has removed 4231 spam emails to date.
    Paying users do not have this message in their emails.
    Get the free SPAMfighter here: http://www.spamfighter.com/len
     
  10. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    Douglas J Steele wrote:
    > > Is it possible to get the table constraints like PK, FK details.

    >
    > I don't believe there's any way to query that information from the system
    > tables. You need to use DAO or ADOX.


    ADODB may be more appropriate, considering the OP is asking about
    querying system tables, because the ADODB.Connection object's
    OpenSchema method fetches a *recordset* of schema information:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenschema.asp

    This is an alternative approach to traversing and object model's
    collections with DAO/ADOX. In fact, it is my understanding that ADOX
    uses OpenSchema under the covers anyhow:

    http://support.microsoft.com/default.aspx?scid=kb;en-us;271483

    Also a recordset can be more flexible than a Collection e.g. you can
    Filter, Sort, GetString, GetRows, etc.

    Jamie.

    --
     
  11. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    news:1152884042.734815.13050@m73g2000cwd.googlegroups.com...
    >
    > Douglas J Steele wrote:
    > > > Is it possible to get the table constraints like PK, FK details.

    > >
    > > I don't believe there's any way to query that information from the

    system
    > > tables. You need to use DAO or ADOX.

    >
    > ADODB may be more appropriate, considering the OP is asking about
    > querying system tables, because the ADODB.Connection object's
    > OpenSchema method fetches a *recordset* of schema information:
    >
    >

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenschema.asp
    >
    > This is an alternative approach to traversing and object model's
    > collections with DAO/ADOX. In fact, it is my understanding that ADOX
    > uses OpenSchema under the covers anyhow:
    >
    > http://support.microsoft.com/default.aspx?scid=kb;en-us;271483
    >
    > Also a recordset can be more flexible than a Collection e.g. you can
    > Filter, Sort, GetString, GetRows, etc.


    Yeah, you're right. I have that in my "stock answers" at home, but I'm not
    at home right now...

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)
     
  12. srikanthr@gmail.com

    srikanthr@gmail.com
    Expand Collapse
    Guest

    Thanks a lot for all your suggestions. As am reading via java,
    selecting
    MSysRelationships helped me in identifying the relationships. However
    is it possible to get the column constaints like NULL CHECK, Precision
    of the DataTYPE of the column, validation rule applied for the column
    etc. Basically I need to generate CREATE TABLE script by reading the
    mdb file. I can get the column name, its data type, however the cloumn
    constraints are essential to generate the CREATE TABLE script. Any
    pointers will be greatfully received.

    Douglas J Steele wrote:
    > "Jamie Collins" <jamiecollins@xsmail.com> wrote in message
    > news:1152884042.734815.13050@m73g2000cwd.googlegroups.com...
    > >
    > > Douglas J Steele wrote:
    > > > > Is it possible to get the table constraints like PK, FK details.
    > > >
    > > > I don't believe there's any way to query that information from the

    > system
    > > > tables. You need to use DAO or ADOX.

    > >
    > > ADODB may be more appropriate, considering the OP is asking about
    > > querying system tables, because the ADODB.Connection object's
    > > OpenSchema method fetches a *recordset* of schema information:
    > >
    > >

    > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdmthopenschema.asp
    > >
    > > This is an alternative approach to traversing and object model's
    > > collections with DAO/ADOX. In fact, it is my understanding that ADOX
    > > uses OpenSchema under the covers anyhow:
    > >
    > > http://support.microsoft.com/default.aspx?scid=kb;en-us;271483
    > >
    > > Also a recordset can be more flexible than a Collection e.g. you can
    > > Filter, Sort, GetString, GetRows, etc.

    >
    > Yeah, you're right. I have that in my "stock answers" at home, but I'm not
    > at home right now...
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
     
  13. Jamie Collins

    Jamie Collins
    Expand Collapse
    Guest

    srikanthr@gmail.com wrote:
    > is it possible to get the column constaints like NULL CHECK, Precision
    > of the DataTYPE of the column, validation rule applied for the column
    > etc. Basically I need to generate CREATE TABLE script by reading the
    > mdb file.


    The ADODB OpenSchema method can again use used for these purposes. For
    the kind of schema information that is available in theory, take a look
    at the SchemaEnum ADO enumeration:

    http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp

    I say 'in theory' because the OLE DB provider for Jet 4.0 does not
    support all the rowsets and others are only available on a
    table-by-table basis (rather than at the schema level). Some have
    quirks.

    As a good example, take CHECK constraints. Use adSchemaTableConstraints
    with the table name then filter the resulting recordset for
    CONSTRAINT_TYPE = 'CHECK'; however, this only gets you the CHECK name
    and definition. Use adSchemaCheckConstraints and the resulting
    recordset for CONSTRAINT_NAME to get the table against which the CHECK
    was defined (and column name if that matters to you, however for Jet
    the seems to be no way of definig a column-level CHECK). Remember that
    Jet, unlike other engines (including SQL Server), does allow multiple
    tables to be referenced in a CHECK constraints, but only changes to the
    table against which it was defined will cause the CHECK to be invoked.
    There are a few of gotchas: for Access-created Validation Rules it is
    not a simple 1:1 mapping between CONSTRAINT_NAME values in each
    recordset: adSchemaTableConstraints [<table>].[<column>].ValidationRule
    maps to adSchemaCheckConstraints [<column>].ValidationRule; also,
    adSchemaTableConstraints is sometimes terminated with a Chr$(0),
    sometimes not.

    Hopefully this will give the impression that writing a program to
    generate a CREATE TABLE script wouldn't be a minor task (my pet project
    is circa 8K lines of VBA and counting). Also consider that some
    Access-only properties do not show up in the schema rowsets.

    Your best approach may be to purchase a third party tool which does all
    this out of the box.

    Jamie.

    --
     
  14. srikanthr@gmail.com

    srikanthr@gmail.com
    Expand Collapse
    Guest

    Thank Jamie for the info. Is there any third party tool which java
    based


    Jamie Collins wrote:
    > srikanthr@gmail.com wrote:
    > > is it possible to get the column constaints like NULL CHECK, Precision
    > > of the DataTYPE of the column, validation rule applied for the column
    > > etc. Basically I need to generate CREATE TABLE script by reading the
    > > mdb file.

    >
    > The ADODB OpenSchema method can again use used for these purposes. For
    > the kind of schema information that is available in theory, take a look
    > at the SchemaEnum ADO enumeration:
    >
    > http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstschemaenum.asp
    >
    > I say 'in theory' because the OLE DB provider for Jet 4.0 does not
    > support all the rowsets and others are only available on a
    > table-by-table basis (rather than at the schema level). Some have
    > quirks.
    >
    > As a good example, take CHECK constraints. Use adSchemaTableConstraints
    > with the table name then filter the resulting recordset for
    > CONSTRAINT_TYPE = 'CHECK'; however, this only gets you the CHECK name
    > and definition. Use adSchemaCheckConstraints and the resulting
    > recordset for CONSTRAINT_NAME to get the table against which the CHECK
    > was defined (and column name if that matters to you, however for Jet
    > the seems to be no way of definig a column-level CHECK). Remember that
    > Jet, unlike other engines (including SQL Server), does allow multiple
    > tables to be referenced in a CHECK constraints, but only changes to the
    > table against which it was defined will cause the CHECK to be invoked.
    > There are a few of gotchas: for Access-created Validation Rules it is
    > not a simple 1:1 mapping between CONSTRAINT_NAME values in each
    > recordset: adSchemaTableConstraints [<table>].[<column>].ValidationRule
    > maps to adSchemaCheckConstraints [<column>].ValidationRule; also,
    > adSchemaTableConstraints is sometimes terminated with a Chr$(0),
    > sometimes not.
    >
    > Hopefully this will give the impression that writing a program to
    > generate a CREATE TABLE script wouldn't be a minor task (my pet project
    > is circa 8K lines of VBA and counting). Also consider that some
    > Access-only properties do not show up in the schema rowsets.
    >
    > Your best approach may be to purchase a third party tool which does all
    > this out of the box.
    >
    > Jamie.
    >
    > --
     

Share This Page