Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss

List available tables

1 view
Skip to first unread message

swat42

unread,
Jul 10, 2003, 4:21:31 PM7/10/03
to
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;


Anith Sen

unread,
Jul 10, 2003, 5:04:22 PM7/10/03
to
Some options here...

--#1
EXEC sp_tables

--#2
SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE_TABLE'

--#3
SELECT *
FROM sysobjects
WHERE xtype = 'u'

--
- Anith
( Please reply to newsgroups only )


Erland Sommarskog

unread,
Jul 10, 2003, 6:14:33 PM7/10/03
to
Anith Sen (an...@bizdatasolutions.com) writes:
> --#3
> SELECT *
> FROM sysobjects
> WHERE xtype = 'u'

To be picky it should be 'U'. 'u' will not work in case-sensitive databases.


--
Erland Sommarskog, SQL Server MVP, som...@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

swat42

unread,
Jul 10, 2003, 6:13:00 PM7/10/03
to

----- Original Message -----
From: "Anith Sen" <an...@bizdatasolutions.com>
Newsgroups: comp.databases.ms-sqlserver
Sent: Thursday, July 10, 2003 5:04 PM
Subject: Re: List available tables


> Some options here...
>
> --#1
> EXEC sp_tables
>

error: table 'sp_tables' si not recognized

> --#2
> SELECT *
> FROM INFORMATION_SCHEMA.TABLES
> WHERE TABLE_TYPE = 'BASE_TABLE'
>

error: could not find file INFORMATION_SCHEMA.mdb

> --#3
> SELECT *
> FROM sysobjects
> WHERE xtype = 'u'
>

error: table 'sysobjets' is not recognized

> --
> - Anith
> ( Please reply to newsgroups only )
>
>

i am quering the datbase (ms access) using odbc with sql queries if that
might help you.

By the same time, I found another type of query that looks like to the 2n
one:
SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects

but i get error msg:
>>> no read access to 'MSysObjects' table

So how would i get the tables in the db or how do i configure the database
so that the MSysObject table is visible for read access?

Security is alos important, i don't want to show all table just to get this
feature working.

Erland Sommarskog

unread,
Jul 10, 2003, 6:43:21 PM7/10/03
to
swat42 (swa...@bit.com) writes:
> error: table 'sp_tables' si not recognized
>...

> error: could not find file INFORMATION_SCHEMA.mdb
>...
> error: table 'sysobjets' is not recognized
>...
> i am quering the datbase (ms access) using odbc with sql queries if that
> might help you.

You posted your question to forum dedicated to SQL Server, and you did
not indicate that you are using something else. Thus, Anith gave you
the answer for SQL Server.

If you are using Access, you may want to try comp.databases.ms-access.
If you are using some other engine, you need to find a forum for that
DBMS.

swat42

unread,
Jul 10, 2003, 7:20:13 PM7/10/03
to
I posted to ms-access forum as suggested and got this answer wich is excatly
what i was looking for.

Thanls 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" <swa...@bit.com> wrote in
news:ETjPa.16788$Tx.8...@news20.bellglobal.com:

0 new messages