List of user tables? 1

[k108]

OK, I know I've asked this before, but I guess I just don't get it!

How can I see ALL the user tables for a *given database*?

This is the query I've been using, but it doesn't break down the tables by database:

select * from sysobjects where xtype = 'U'

There's gotta be an easier way. Yes, I know I can use EM for this, but I want a SQL version of the query.

Thanks!

 

[k108]

Oh, ok - I just found this:

select * from information_schema.tables

But you have to be IN the database you want to query in order for it to work.

Are there any other ways to do this?

Thanks

 

[mrdenny]

You can put the database name in front of the table name

select *
from {Database}.INFORMATION_SCHEMA.tables

That's pretty much the easiest way other than the sysobjects table within each database.

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

 

[mrdenny]

I forgot to mention it's better to access tables via the INFORMATION_SCHEMA views rather than the system tables directly as the system tables change from version to version where the INFORMATION_SCHEMA views don't change.

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

 

[k108]

I forgot to mention it's better to access tables via the INFORMATION_SCHEMA views rather than the system tables directly as the system tables change from version to version where the INFORMATION_SCHEMA views don't change.

Right. I just learned that in my reading for the first MCDBA exam. Thanks for the info. But for some reason I am inclined to use the sys tables b/c that's how I did it in Oracle but I'll get over it

 

[mrdenny]

Yeah, everyone knows they are supose to use the Schema views. Most people go to the system tables directly for quick lookups. When you are writting production code just make sure to use the views.

For one off stuff, go straight to the tables.

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