422,530 Members | 1,031 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 422,530 IT Pros & Developers. It's quick & easy.

how to get the complete table schema including constraints thru SQL query

P: n/a
Hi ,

can anyone tell me how to get the complete table schema including
constraints, primary key ..etc.
through sql Query in Ms access.

We can get table information in MS SQL Server by means using sp_help.I
want to know the equivalent sql command for ms-access.

thanks in advance

Regards
visu

May 8 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi, Visu.
We can get table information in MS SQL Server by means using sp_help.I
want to know the equivalent sql command for ms-access.
SQL Server has system tables that contain information on each table's
structure. Access does not. Therefore, there are no system tables to query
with Jet SQL. Use the ADO and ADOX libraries, or the DAO library in VBA
instead. Use Tables and Indexes if using ADOX. Use TableDefs, Fields, and
the PrimaryKey Index if using DAO. One must use ADO's OpenSchema function
of the Connection object to retrieve the list of constraints, not DAO,
because it's not available in DAO.

If you just want the column names of a table, that's easy enough with SQL.
Try the following syntax to export the column names to a text file:

SELECT * INTO [TEXT;HDR=Yes;DATABASE=F:\Work\Test\].MyTable.txt
FROM MyTable
WHERE (1 = 0);

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"visu" <k.******@gmail.comwrote in message
news:11*********************@e65g2000hsc.googlegro ups.com...
Hi ,

can anyone tell me how to get the complete table schema including
constraints, primary key ..etc.
through sql Query in Ms access.

We can get table information in MS SQL Server by means using sp_help.I
want to know the equivalent sql command for ms-access.

thanks in advance

Regards
visu

May 8 '07 #2

P: n/a
On May 8, 12:19 pm, "'69 Camaro" <ForwardZERO_SPAM.To.
69Cam...@Spameater.orgZERO_SPAMwrote:
Hi, Visu.
We can get table information in MS SQL Server by means using sp_help.I
want to know the equivalent sql command for ms-access.

SQL Server has system tables that contain information on each table's
structure. Access does not. Therefore, there are no system tables to query
with Jet SQL. Use the ADO and ADOX libraries, or the DAO library in VBA
instead. Use Tables and Indexes if using ADOX. Use TableDefs, Fields, and
the PrimaryKey Index if using DAO. One must use ADO's OpenSchema function
of the Connection object to retrieve the list of constraints, not DAO,
because it's not available in DAO.

If you just want the column names of a table, that's easy enough with SQL.
Try the following syntax to export the column names to a text file:

SELECT * INTO [TEXT;HDR=Yes;DATABASE=F:\Work\Test\].MyTable.txt
FROM MyTable
WHERE (1 = 0);

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blogs:http://www.DataDevilDog.BlogSpot.com...utors2.htmlfor contact
info.

"visu" <k.vis...@gmail.comwrote in message

news:11*********************@e65g2000hsc.googlegro ups.com...
Hi ,
can anyone tell me how to get the complete table schema including
constraints, primary key ..etc.
through sql Query in Ms access.
We can get table information in MS SQL Server by means using sp_help.I
want to know the equivalent sql command for ms-access.
thanks in advance
Regards
visu- Hide quoted text -

- Show quoted text -
thanks .. i ll try with ado.

Regards
Visu

May 8 '07 #3

P: n/a
You're welcome. Good luck with it.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/ex...ributors2.html for contact
info.
"visu" <k.******@gmail.comwrote in message
news:11*********************@h2g2000hsg.googlegrou ps.com...
On May 8, 12:19 pm, "'69 Camaro" <ForwardZERO_SPAM.To.
69Cam...@Spameater.orgZERO_SPAMwrote:
>Hi, Visu.
We can get table information in MS SQL Server by means using sp_help.I
want to know the equivalent sql command for ms-access.

SQL Server has system tables that contain information on each table's
structure. Access does not. Therefore, there are no system tables to
query
with Jet SQL. Use the ADO and ADOX libraries, or the DAO library in VBA
instead. Use Tables and Indexes if using ADOX. Use TableDefs, Fields,
and
the PrimaryKey Index if using DAO. One must use ADO's OpenSchema
function
of the Connection object to retrieve the list of constraints, not DAO,
because it's not available in DAO.

If you just want the column names of a table, that's easy enough with
SQL.
Try the following syntax to export the column names to a text file:

SELECT * INTO [TEXT;HDR=Yes;DATABASE=F:\Work\Test\].MyTable.txt
FROM MyTable
WHERE (1 = 0);

HTH.
Gunny

Seehttp://www.QBuilt.comfor all your database needs.
Seehttp://www.Access.QBuilt.comfor Microsoft Access tips and tutorials.
Blogs:http://www.DataDevilDog.BlogSpot.com...utors2.htmlfor
contact
info.

"visu" <k.vis...@gmail.comwrote in message

news:11*********************@e65g2000hsc.googlegr oups.com...
Hi ,
can anyone tell me how to get the complete table schema including
constraints, primary key ..etc.
through sql Query in Ms access.
We can get table information in MS SQL Server by means using sp_help.I
want to know the equivalent sql command for ms-access.
thanks in advance
Regards
visu- Hide quoted text -

- Show quoted text -

thanks .. i ll try with ado.

Regards
Visu

May 8 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.