Get all tables in sql server instance

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

SQL Server 2005 Forums

Transact-SQL (2005)

Get all tables in sql server instance

Author

Topic

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-21 : 06:13:43

hi
how to get list of all tables that exist in my sql server instance ?
i'm using this scrpit :

select * from sys.tables

but this command return only tables that exist in Current Database.
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 06:50:20

sp_msforeachdb 'SELECT * FROM INFORMATION_SCHEMA.TABLES'

E 12°55'05.25"
N 56°04'39.16"

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-21 : 07:56:02

thanks for reply
i use this script to get all tables in all databases :

use northwind
declare @cmd1 varchar(500)
set @cmd1 = 'select * from sys.tables'
exec sp_MSforeachdb @command1=@cmd1

but when i tried this what i does execute that task/command number of times the available databases on same database instead execute that task once on each database.
how to solve my problem ?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-04-21 : 08:35:14

quote:
Originally posted by hdv212

thanks for reply
i use this script to get all tables in all databases :

use northwind
declare @cmd1 varchar(500)
set @cmd1 = 'select * from sys.tables'
exec sp_MSforeachdb @command1=@cmd1

but when i tried this what i does execute that task/command number of times the available databases on same database instead execute that task once on each database.
how to solve my problem ?

Use this

sp_msforeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES'

Note that sp_msforeachdb is undocumented

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 09:18:24

Thanks.

E 12°55'05.25"
N 56°04'39.16"

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-21 : 09:46:21

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-21 : 09:57:23

thanks madhivanan
my main problem was solved by this script :

sp_msforeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES'

now, how to union all tables that return by this code ?
thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-21 : 10:03:46

[code]CREATE TABLE #Stage
(
TABLE_CATALOG SYSNAME,
TABLE_SCHEMA SYSNAME,
TABLE_NAME SYSNAME,
TABLE_TYPE SYSNAME
)

INSERT #Stage
EXEC ('sp_msforeachdb ''SELECT * FROM ?.INFORMATION_SCHEMA.TABLES''')

SELECT *
FROM #Stage

DROP TABLE #Stage[/code]

E 12°55'05.25"
N 56°04'39.16"

hdv212
Posting Yak Master

140 Posts

Posted - 2008-04-21 : 11:15:23

very very thanks Peso.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources