List all the tables on a SQL Server

How do I list all the tables on a SQL Server, along with the database that
they are in ?
Have tried the following ways, but they only list tables in the current
database:
select * from sysobjects
exec sp_tables
select * from INFORMATION_SCHEMA.tables
Thanks, CraigAm Tue, 7 Mar 2006 03:43:59 -0800 schrieb Craig HB:

> How do I list all the tables on a SQL Server, along with the database that
> they are in ?
> Have tried the following ways, but they only list tables in the current
> database:
> select * from sysobjects
> exec sp_tables
> select * from INFORMATION_SCHEMA.tables
> Thanks, Craig
maybe this can help:
http://www.dbazine.com/sql/sql-articles/larsen5
bye, Helmut|||This should get you starting:
EXEC sp_MSforeachdb 'SELECT * FROM ?.INFORMATION_SCHEMA.TABLES'
sp_MSforeachdb is not documented, so you really should create your own versi
on of it, with a bit of
inspiration from the procedure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A0DD05BA-5962-4D6E-A641-496D8E2684A8@.microsoft.com...
> How do I list all the tables on a SQL Server, along with the database that
> they are in ?
> Have tried the following ways, but they only list tables in the current
> database:
> select * from sysobjects
> exec sp_tables
> select * from INFORMATION_SCHEMA.tables
> Thanks, Craig|||Try:
create table #t
(
dbname sysname not null
, tabschema sysname not null
, tablename sysname not null
, primary key (dbname, tabschema, tablename)
)
go
exec master.dbo.sp_MSforeachdb 'insert #t select table_catalog,
table_schema, table_name from ?.information_schema.tables where table_type =
''base table'''
select * from #t
drop table #t
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Craig HB" <CraigHB@.discussions.microsoft.com> wrote in message
news:A0DD05BA-5962-4D6E-A641-496D8E2684A8@.microsoft.com...
How do I list all the tables on a SQL Server, along with the database that
they are in ?
Have tried the following ways, but they only list tables in the current
database:
select * from sysobjects
exec sp_tables
select * from INFORMATION_SCHEMA.tables
Thanks, Craig|||Thanks All -- that was exactly what I was after|||Run this
select 'exec '+name+'..sp_tables' from Master..sysdatabases
Copy the result back to QA and run them one by one
Madhivanan