Showing posts with label textdocument. Show all posts
Showing posts with label textdocument. Show all posts

Friday, February 24, 2012

How to get list (text) of all tables and columns?

Is there a way using MS SQL Server and Enterprise Manager to get a text
document (or perhaps even a Word document) listing all table names,
column names, etc of a database?

--
Sugapablo
-----------
http://www.sugapablo.com <--music
http://www.sugapablo.net <--personal"Sugapablo" <russREMOVE@.sugapablo.com> wrote in message
news:vmu7b03j5uos68@.corp.supernews.com...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?
> --
> Sugapablo
> -----------
> http://www.sugapablo.com <--music
> http://www.sugapablo.net <--personal

It would probably be easier to use Query Analyzer instead, and get the
details you need from the INFORMATION_SCHEMA views (assuming you have SQL7
or 2000), eg.:

select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
order by TABLE_NAME, ORDINAL_POSITION

If it's a one-off task, then you can just cut and paste the results, but if
you need to do it regularly, then you could consider using SQLDMO from a
client application. That way you can create the whole Word document using VB
or something similar.

Simon|||Sugapablo <russREMOVE@.sugapablo.com> wrote in message news:<vmu7b03j5uos68@.corp.supernews.com>...
> Is there a way using MS SQL Server and Enterprise Manager to get a text
> document (or perhaps even a Word document) listing all table names,
> column names, etc of a database?

Hello,

To get a list of user tables use this query:

select name from ssysobjects where type = 'u'

And for a list of columns related for all objects (tables,views,sps)

select name, object_name(id) from sysindexes

The results could be pushed to a text document or any other ODBC
compliant datasource using DTS.

Regards,
-Manoj