all tables 2

[toyt78]

How do I find all tables in the sql server database?

 

[toyt78]

I really just want all the table names only.

 

[amrita418]

In query analyzer select the data base you need the table names from and query the information_schema.tables view.

something like:

select table_name from information_schema.tables

Regards,
AA

 

[toyt78]

thanks

 

[NoCoolHandle]

Doesn't information_schema.tables also return views?


try
select table_name from information_schema.tables where table_type='Base Table'

 

[mrdenny]

Nope.

Contains one row for each table in the current database for which the current user has permissions. The INFORMATION_SCHEMA.TABLES view is based on the sysobjects system table.

To retrieve information from these views, specify the fully qualified name of INFORMATION_SCHEMA view_name.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[NoCoolHandle]

Mr Denny..

Don't believe all you read.

Try running the non filtered query in a database you have that has views in it.

You should seee all of them also

Documentation is never to be believed blindly. Especially when someone points out there is an issue.

 

[mrdenny]

I stand corrected. I just checked the code for the view (much easier that creating a view, as I don't use them) and it does in fact call tables and views.

where
	o.xtype in ('U', 'V') and /*User Tables, Views*/
	permissions(o.id) != 0

Apparently BOL is confused it self. A little farther down the page where it defines what the TABLE_TYPE column in the output is it says:

Type of table. Can be VIEW or BASE TABLE.

Sorry for posting the bad info. I try to avoid doing so.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

Donate to Katrina relief

 

[NoCoolHandle]

THis and other issues are reasons I NEVER use the information schema veiws.

Yes I know about issues with the next gen of SQL but, I don't like to program problems into a currnet app to fix one that MIGHT pop up.

Rob