Avatar of tommym121
tommym121
Flag 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.
Microsoft SQL Server 2008

Avatar of undefined
Last Comment
tommym121

8/22/2022 - Mon
Abdulmalek_Hamsho

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
8080_Diver

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
sachinpatil10d

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

indikad

look at
INFORMATION_SCHEMA.TABLES

and
INFORMATION_SCHEMA.COLUMNS
Your help has saved me hundreds of hours of internet surfing.
fblack61
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'
8080_Diver

tommym121,

Did you try the query I supplied?
Abdulmalek_Hamsho

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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Abdulmalek_Hamsho

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'
tommym121

ASKER
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.
tommym121

ASKER
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
tommym121

ASKER
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.
tommym121

ASKER
Thanks