We help IT Professionals succeed at work.

SQL - is there a way to find out the tables  from system tables based on the name of the database

tommym121
tommym121 asked
on
231 Views
Last Modified: 2012-06-06
I see there is sys.databases to get the list of databases
I see there is sys.tables to get a list of tables once I set the current database

But I am not sure how to join or need other sys tables to get the list of tables within a database.
Comment
Watch Question

Can you rephrase the question?

It's not clear to me what do you want "But I am not sure how to join or need other sys tables to get the list of tables within a database."
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Try this

Select * from Database1.dbo.TableName 
select * from Database2.dbo.TableName

Open in new window


to join them

select * from Database1.dbo.TableName  D1 Inner join Database2.dbo.TableName D2 on D1.ID = D2.ID

Open in new window

Commented:
look at
INFORMATION_SCHEMA.TABLES

and
INFORMATION_SCHEMA.COLUMNS

Author

Commented:
Abdulmalek_HamshoP

But I am not sure how to join or need other sys tables to get the list of tables within a database.

What I meant is I would like to construct a Select statement that will give me tables when I state database,  schema within the where constrain

for examples

Select tables from sys.??
Join sys.??
where database='xyz' and schema='abc'
tommym121,

Did you try the query I supplied?
You can use this one and filter accordingly (execute it against the required DB):

   SELECT *
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'


You can use TABLE_TYPE = 'VIEW' to show the views

tommym121, are you trying to search the DB for some value?
I'm sorry, I read your post quickly.

If you must you use the DB name in the WHERE statement you can filter using "TABLE_CATALOG":

        SELECT *
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'
        AND TABLE_CATALOG = 'MyDB'

Author

Commented:
8080_Diver,

I try the query,  but how do I specify the database so that is only return the tables info for that database only.

Author

Commented:
Abdulmalek_Hamsho

        SELECT *
        FROM     INFORMATION_SCHEMA.TABLES
        WHERE   TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'dbo'
        AND TABLE_CATALOG = 'MyDB'

The about Select is based on what database I query againtst.  If I set 'Use OtherDB', it will not return any result, unless I set 'Use MyDB'  , What I am trying to achieve is to be able to query the tables information of any database no matter what being set using the 'Use' statement

Author

Commented:
8080_Diver,

select 'MyDB' AS db, * from MyDB.sys.tables

Based on what you have I can get the tables for the database I specified. Thanks.

Author

Commented:
Thanks
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.