List availables tables

How to list available tables in a database using an sql
statement?
The following code does not work with ms-sql:
select table_name from user_tables;SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
"joey32" <joey32@.total.net> wrote in message
news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
> How to list available tables in a database using an sql
> statement?
> The following code does not work with ms-sql:
> select table_name from user_tables;
>|||i tried with your query, but i get 'table sysobjets not
recognized' from Access.
by the same time, i found a another query that looks like
the same style:
SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=5) AND
((Left([name],4))<>"Msys"))
ORDER BY MSysObjects.Name;
but again i get an error from ms-access:
>> Records can not be read, no read permission on
MSysObjects
by the way, i'm queying via odbc with sql statements, if
that might help you
>--Original Message--
>select name from sysobjects where type='U'. This will
>give you all the names of the tables present in the
>database. Make sure you are in the database in which you
>want to run the query.
>HTH
>>--Original Message--
>>How to list available tables in a database using an sql
>>statement?
>>The following code does not work with ms-sql:
>>select table_name from user_tables;
>>
>>.
>.
>|||When were you planning on mentioning you're using Access? You said sql,
ms-sql, etc.
Try SELECT * FROM MSysObjects or SELECT * FROM MSSysObjects (forget
which)...
"joey32" <joey32@.total.net> wrote in message
news:044301c34725$1c648e60$a301280a@.phx.gbl...
> i get this following message when executing the request:
> >> Could not find '...\INFORMATION_SCHEMA.mdb"
> and that's all i have.
> >--Original Message--
> >SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
> >
> >
> >
> >"joey32" <joey32@.total.net> wrote in message
> >news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
> >> How to list available tables in a database using an sql
> >> statement?
> >>
> >> The following code does not work with ms-sql:
> >> select table_name from user_tables;
> >>
> >>
> >
> >
> >.
> >|||Well, look at the other thread in same post, information is
there, but i might be not very visible to you, sorry for
that mistake.
I runned the query and get this error:
>> no read access to 'MSysObjets' table
by the way, i am quering via odbc using sql statements on a
ms-access database, i think version is 2002 (xp).
so how to i get the MSysObjets table visible for read
access?
>--Original Message--
>When were you planning on mentioning you're using Access?
You said sql,
>ms-sql, etc.
>Try SELECT * FROM MSysObjects or SELECT * FROM
MSSysObjects (forget
>which)...
>
>
>
>"joey32" <joey32@.total.net> wrote in message
>news:044301c34725$1c648e60$a301280a@.phx.gbl...
>> i get this following message when executing the request:
>> >> Could not find '...\INFORMATION_SCHEMA.mdb"
>> and that's all i have.
>> >--Original Message--
>> >SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
>> >
>> >
>> >
>> >"joey32" <joey32@.total.net> wrote in message
>> >news:03ee01c3471f$7fa45420$a301280a@.phx.gbl...
>> >> How to list available tables in a database using an
sql
>> >> statement?
>> >>
>> >> The following code does not work with ms-sql:
>> >> select table_name from user_tables;
>> >>
>> >>
>> >
>> >
>> >.
>> >
>
>.
>|||I got this answer from another ms-access forum wich is
exactly what i was looking for.
It might help somebody.
Thanks for your help.
----
Look in the MSysObjects table (tools|options| check system
objects). You
don't have to unhide the table to run the query, but it
wouldn't hurt for
you to poke around those tables to see what info is
available. Native
access tables are type 1. Attached access tables are type
6.
Select name, type from msysobjects where type = 1 or type
= 6
Richard Bernstein
"swat42" <swat42@.bit.com> wrote in
news:ETjPa.16788$Tx.811910@.news20.bellglobal.com:
> How to list available tables in a db by their table name
with an sql
> query?
> The following piece of code don't work:
> select table_name from user_tables;
>|||Thanks a lot but it's not MS-Access forum (it's MS SQL Server one) so I
don't think this might help anyone here
"joey32" <joey32@.total.net> wrote in message
news:057d01c3473a$0fc65fc0$a301280a@.phx.gbl...
> I got this answer from another ms-access forum wich is
> exactly what i was looking for.
> It might help somebody.