Listing table names and columns in a database

Question

Post reply

Listing table names and columns in a database

ramadesai108

SSChampion

Points: 10609
More actions
December 19, 2008 at 11:48 am

#129338

Hi All,
How do I get a list of all the table names and their column names from a database?
Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Use Books On Line (BOL) entriy for System Views:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/13bccc2f-ed3c-4b58-abd0-ca8bf34a66b8.htm

This in turn will allow you to view what is contained in sys.tables and sys.objects

And finally read about sys.all_columns at:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/40e04fe9-0b64-4799-84c0-57f128b2bdc2.htm

After all that, if you have further questions repost.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

David Benoit SSC-Dedicated Points: 34562 More actions · Answer 2

Try looking at the syscolumns table and the function object_name() to get all the information you want out. The id column in that table is what you are going to put in the function. Like object_name(id).

If you have problems with some of that post the query and you will get some help.

David

@SQLTentmaker

“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 3

select st.name,sc.name from

sys.all_columns sc join sys.tables st

on st.object_id=sc.object_id

order by st.name

HTH!

MJ

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Actually this is extremely easy:

Select * From Information_Schema.Columns

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Paresh Prajapati SSCertifiable Points: 7567 More actions · Answer 5

select table_name, column_name

from information_schema.columns

order by table_name

_____________________________________________________________________________________________________________
Paresh Prajapati
➡ +919924626601
http://paresh-sqldba.blogspot.com/[/url]
LinkedIn | Tweet Me | FaceBook | Brijj

NitinPatel31 Hall of Fame Points: 3721 More actions · Answer 6

If you wish to have lists in HTML format you can use my script

http://www.codeproject.com/KB/database/SQL_DB_DOCUMENTATION.aspx

This script generates HTML report of the database objects. You can edit this script to get the table and column list only

Best Regards

Nitin

Regards,
Nitin

damonj SSC Rookie Points: 39 More actions · Answer 7

MANU (12/20/2008)
select st.name,sc.name from
sys.all_columns sc join sys.tables st
on st.object_id=sc.object_id
order by st.name
HTH!
MJ

Thanks MJ!

That helped me out, although I'm not the OP.

DJ

Robert Bedick Old Hand Points: 310 More actions · Answer 8

If you want the list of database table names and columns because you want to document your database, you might want to consider using SchemaToDoc (http://www.schematodoc.com)