December 19, 2008 at 11:48 am
Hi All,
How do I get a list of all the table names and their column names from a database?
Thanks.
December 19, 2008 at 12:18 pm
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.
December 19, 2008 at 12:21 pm
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
December 20, 2008 at 2:55 pm
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
December 20, 2008 at 9:50 pm
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]
December 22, 2008 at 1:56 am
select table_name, column_name
from information_schema.columns
order by table_name
December 22, 2008 at 2:06 am
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
December 22, 2008 at 7:41 am
MANU (12/20/2008)
select st.name,sc.name fromsys.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
December 23, 2008 at 7:53 am
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)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply