Link to home
Start Free TrialLog in
Avatar of tommym121
tommym121Flag for Canada

asked on

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

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.
Avatar of Abdulmalek_Hamsho
Abdulmalek_Hamsho
Flag of United Arab Emirates image

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."
ASKER CERTIFIED SOLUTION
Avatar of 8080_Diver
8080_Diver
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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

Avatar of indikad
indikad

look at
INFORMATION_SCHEMA.TABLES

and
INFORMATION_SCHEMA.COLUMNS
Avatar of tommym121

ASKER

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'
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.
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
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.
Thanks