SQL Server 2000 Table Structure 4

[SteveMillman]

Is there an easy way to get a printable copy of all of the SQL tables in our SQL Server 2000 database system?

We are looking at converting several tables from Access to SQL Server and while I found a great freeware utility for ACCESS (EzDocumentor) I have nothing for the SQL Server side.

I was hoping to view the Table Name, Field Name, Size and Type for all tables in an easy to view and print format by either running a free or cheap product or by building one SQL statement that would handle it all

I am new to this environment and am hoping such tools either exist commonly or are easy to build and run.. .

Thank you,

Steve Millman

 

[essa2000]

Dear Millman ;

U have asked this before sometime and the threadId is

thread183-273580

But, for you the copy again.

This is from me essa2000:

There are many way to printing the structure of your tables. The easiest way to printing the structure is that just copy all the table after selecting them in Enterprise manager and then paste them in a Word File or even in Query Analyser then you will the scripts of all the tables with its structure.

You can use Erwin Logic works to print the complete structure of your SQL server tables. But, to do this you have to perform reverse engineering in Erwin meaning you have to import all the tables in the sheet of Erwin and then you will take print out of it.

You can also use Visio 2000.

You can use SQL Server Diagrams.

You can access system tables to get the information about tables and its columns. like sysobjects and syscolumns.

Regards,
Essa


This is from :
tlbroadbent (MIS) May 15, 2002

You can query the Information_Schema Views using SQL Query Analyzer. For example, the following will list the metadata for each column in every table of the current database.

Select c.*
From information_schema.Columns c
Join information_schema.Tables t
on c.table_name=t.Table_name
Where TABLE_TYPE='BASE TABLE'
Order By c.Table_Name, c.Ordinal_Position

For a complete listing of the Information_Schema views, see SQL BOL.




Regards,
Muhammad Essa Mughal

 

[garwain]

I would have to say the easiest way would be to open up the enterprise manager, and go into the Diagrams section for your DB. Create a new diagram, add all the tables that you care about, depending on the number of tables and relationships, this could take a while to draw. The standard display should be the tablename, and all the column name, datatype, length and Allow Nulls. If you don't have all the info you need, you can create a custom display.

The tables should automatically arrange in a viewable manner, but you can easily drag them around as wanted. you can always hit the arrange tables button to have the computer arrange them again.

This is expecially handy because you can see all relationships and print the model.

 

[SteveMillman]

Essa and Garwain,

Thank you.

Essa, I tried to find my former thread as I was not sure where I put my original email .. but was not sure how to. I ended up searching on a keyword which brought up millions (slight exaggeration) of entries.

How do I find previous threads in a given forum?

Steve Millman

 

[MapMan]

Steve,

If you look at the top left of this page you'll see a link to "My Threads" in the box that says "Hi SteveMillman". This will show all the threads you've created or responded to. Another way is to use the search feature.

When you pick a topic in the "Thread Minder" a listing of recent topics is displayed, on the right. Above those topics is a simulated tab structure that contains a "Keyword Search" tab. Using that tab you can search for messages by a variety of methods, including handle.

HTH, MapMan

Assume nothing, question everything, be explicit not implicit, and you'll always be covered.

 

[crystalized]

If you have Visio you could also try reverse engineering your database and creating a database diagram from it. It works moderately well although the bigger the number of tables selected at a time the less likely it is to work properly. It also might drop a couple relationships (just from the diagram) but overall is a fairly quick way to go, and I have found the printing quality a little better than from the DB diagram.

Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[SteveMillman]

Mapman,

Thanks. I will note that for the future.

Steve