Find all table names and columns in all DB's

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 2000 Forums

Transact-SQL (2000)

Find all table names and columns in all DB's

Author

Topic

stumbling
Posting Yak Master

104 Posts

Posted - 2009-07-13 : 22:30:55

Hi there i have the following code sorted but i need to run this accross all databses on the server and i need to output it to the one file.

select distinct table_catalog,table_name,column_name from information_schema.columns
order by table_catalog,table_name

Is there a way I can i make this search all databases on the server?

Cheers
Phil

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-13 : 22:47:25

[code]
create table #temp
(
TABLE_CATALOG sysname,
TABLE_NAME sysname,
COLUMN_NAME sysname,
)
insert into #temp
exec dbo.sp_MSforeachdb 'use ?;select TABLE_CATALOG, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS'

SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
FROM #temp
[/code]

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

stumbling
Posting Yak Master

104 Posts

Posted - 2009-07-13 : 23:39:21

Thank you very much for that.

quote:

Originally posted by khtan


create table #temp
(
	TABLE_CATALOG	sysname,
	TABLE_NAME	sysname,
	COLUMN_NAME	sysname,
)
insert into #temp
exec dbo.sp_MSforeachdb 'use ?;select TABLE_CATALOG, TABLE_NAME, COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS'

SELECT TABLE_CATALOG, TABLE_NAME, COLUMN_NAME
FROM   #temp

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-07-14 : 03:18:26

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

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources