How to list Tables by passing in DBName? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to list Tables by passing in DBName?

Author

Topic

phrankbooth
Posting Yak Master

162 Posts

Posted - 2012-05-22 : 21:23:09

Using MS SQL 2008 R2:
I'd like to list table names for a DB in the WHERE clause, something along these lines:

Select TableName
From <sys view or infoschema join or something>
WHERE DBName = 'dbname'

Been looking all over and haven't found it.

Thanks!

--PhB

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-22 : 21:27:02

either this


select	*
from	dbname.INFORMATION_SCHEMA.TABLES

OR


select *
from   dbname.sys.tables

if you must pass in the dbname as a parameter, you will need to use Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

phrankbooth
Posting Yak Master

162 Posts

Posted - 2012-05-22 : 21:31:41

Ah got it!! Thanks for the quick reply!

--PhB

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-23 : 15:59:20

quote:
Originally posted by khtan

either this

select *
from dbname.INFORMATION_SCHEMA.TABLES

OR

select *
from dbname.sys.tables

if you must pass in the dbname as a parameter, you will need to use Dynamic SQL

KH
[spoiler]Time is always against us[/spoiler]

Please keep in mind this will also return views so if you want to return only tables use additional condition like


select	*
from	dbname.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources