Export table/column information.

[XopherS]

I am trying to make a sort of database map, listing what fields connect to what other fields, what certain fields even do, things like that. (For info's sake, I'm working with MS CRM, using SQL Server 2005.)

I want to export the list of tables (since there are ~350 tables). Ideally, I'd love to export the list of columns per table. Nothing hugely fancy, just the same info I'd get if I were to select the Columns folder under a table name, and look at the Summary tab.

What should I do?

 

[gmmastros]

[tt][blue]
Select *
From Information_Schema.Columns
Order By Table_Name, Ordinal_Position
[/blue][/tt]

-George

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

 

[XopherS]

No, that doesn't return anything useful to me. When I run it, I get tables like 'spt_monitor' and 'spt_fallback_dev'. The tables I'm looking for have names like 'ContactBase' and 'SystemUserRoles' and the like.

 

[jbenson001]

Make sure you are ruunning George's code against the correct database

 

[gmmastros]

Those are system tables within the master database.

If you connect to 'your' database and try again, I think you will be pleasantly surprised. You could also try qualifying the database name, like this...

Select   * 
From     [!]YourDatabaseName.[/!]Information_Schema.Columns 
Order By Table_Name, Ordinal_Position

-George

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

 

[XopherS]

Holy cats!

I think I thought I was connected to it, but I wasn't. I did the qualification there, and it worked! Thanks!

 

[gmmastros]

I thought you thought you were connected to it, but now think that you weren't but now are.

-George

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

 

[SQLDenis]

>>I thought you thought you were connected to it, but now think that you weren't but now are.

I see the bubbly is still in your system George

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/