All Table - SQL Server Forums

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

All Table

Author

Topic

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-17 : 04:19:07

How to get all tables from al databases from my instance/Sql Server
I like to get the list of all tables in each Database from a single query there are
Sys.tables,sys.information_schema.tables,SYS.databases and all
but i am not able to relate them to get list of all the tables along with the tables,,,
so there is any way to get that thing...

Thanks in Advance

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-17 : 04:23:45

sp_msforeachdb 'select * from information_schema.tables'

E 12°55'05.63"
N 56°04'39.26"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-17 : 04:56:13

Thanks for your reply sir..
is there any way to do so with in query mean without using store procedure is there any another way..

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-17 : 04:58:35

and this one also dont give me tables from all databases...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-17 : 05:32:07

Which database(s) are you missing?

E 12°55'05.63"
N 56°04'39.26"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-17 : 09:38:09

if i run this in database Ashish1 then it shows tables of Ashish1 5 times,,,
and missing Ashish2,Ashish3,Ashish4,Ashish5 tables,,
so there is any other way by which i get that,,
or without using that store procedure
Thanks...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-17 : 09:41:26

Try this:

exec sp_msforeachdb 'select * from [ ? ].information_schema.tables'

Remove the spaces inside the [ ].

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 09:45:23

sp_msforeachdb 'use ?;select * from information_schema.tables'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 09:49:46

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-17 : 10:50:33

quote:
Originally posted by visakh16

Any way without using that store procedure....

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-17 : 11:28:38

The suggestion above are not a stored procedure.

E 12°55'05.63"
N 56°04'39.26"

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-17 : 11:30:11

ohkk
i mean without sp_msforeachd

Thanks for being patience...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

robvolk
Most Valuable Yak

15732 Posts

Posted - 2009-05-17 : 12:00:45

Why can't you use sp_msforeachdb?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 13:17:57

quote:
Originally posted by robvolk

Why can't you use sp_msforeachdb?

May be that was homework or interview question

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-17 : 13:28:55

ohkkk....no not a homework question but i started crawl in system views and system tables and all.so i just want to know if there is any way..
without using that sp_msforeachdb ..
but as you guys are more senior than me and has much more knowledge than me thats you able to comment on this..
like "May be that was homework or interview question"
well thanks for help...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-17 : 13:31:43

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/58b493d9-52ee-484d-b9e3-3785edaa07fe

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-05-18 : 02:18:37

quote:
Originally posted by ashishashish

ohkkk....no not a homework question but i started crawl in system views and system tables and all.so i just want to know if there is any way..
without using that sp_msforeachdb ..
but as you guys are more senior than me and has much more knowledge than me thats you able to comment on this..
like "May be that was homework or interview question"
well thanks for help...

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

http://sqlblogcasts.com/blogs/madhivanan/archive/2008/05/13/simulating-undocumented-procedures.aspx

Madhivanan

Failing to plan is Planning to fail

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-05-18 : 07:51:35

Thanks for your kind replies.....

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-19 : 10:23:57

welcome

chriztoph
Posting Yak Master

184 Posts

Posted - 2009-05-31 : 22:44:41

you can use

USE Ashish1 GO
select * from sys.tables
GO

USE Ashish2 GO
select * from sys.tables
GO

USE n... GO
select * from sys.tables

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-01 : 13:05:10

quote:

Originally posted by chriztoph

you can use

USE Ashish1 GO
select * from sys.tables
GO

USE Ashish2 GO
select * from sys.tables
GO

USE n... GO
select * from sys.tables

only if you know before running how many dbs you want run query for

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources