List Database, Tables, and Fields

[ohmbru2]

Is there a way to query the entire server to return a list like:

Database1 Table1 Field1
Database1 Table1 Field2
Database1 Table1 Field3
Database1 Table2 Field1
Database1 Table2 Field2
Database2 Table1 Field1
Database2 Table1 Field2
...

 

[ptheriault]

Are you sure you want a report like that? It would be difficult to read.

What verion of SQL is this for?


- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

[COLOR=blue]Create[/color] [COLOR=blue]Table[/color] #Temp(Database_Name [COLOR=blue]sysname[/color], Table_Name [COLOR=blue]sysname[/color], Column_Name [COLOR=blue]sysname[/color])

[COLOR=blue]exec[/color] sp_msforeachdb [COLOR=red]'Insert Into #Temp Select ''?'' AS Database_Name, Table_Name, Column_Name From [?].Information_Schema.Columns'[/color]

[COLOR=blue]Select[/color] * 
[COLOR=blue]From[/color]   #Temp
[COLOR=blue]Order[/color] [COLOR=blue]By[/color] Database_Name, Table_Name, Column_Name

[COLOR=blue]Drop[/color] [COLOR=blue]Table[/color] #Temp

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

here you go

 EXEC sp_MSForEachDB 'IF ''[?]'' NOT IN (''master'', ''model'', ''msdb'', ''tempdb'') 
select ''[?]'' as DB_name,table_name,column_name from [?].information_schema.columns
order by 1,table_name,ordinal_position'

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions