Exporting the Table Structure...

Hye guys,
I want 2 export the field names and their properties of my tables to a file by which I would be able 2 print it , Study it and share it with my other friends... for discussions...

Which tool can be used 2 export the table stture in a printable format?

Kabin

Why don't you create a Diagram?

|||

Hi Kabin,

You have a few options depending on what you're looking for. You don't mention which version of SQL Server you have, so I'll try to provide instructions for 2000 and 2005 versions.

1. Create a script of the table's definition.

From Management Studio in Object Explorer, right-click the table and point to Script Table as and then select Create to and choose to save it to a file.

or for SQL Server 2000

From Query Analyzer, right-click the table and select Script Object to File as and then select Create.

2. Use sp_help.

From either Management Studio or Query Analyzer run the following statement.

EXEC sp_help ('your_table_name')

3. Create a database diagram.

See Books Online topics for creating database diagrams. (although your print options are somewhat limited with this).

Regards,

Gail

|||

Hi guys,
well I first tried creating diagram and then printing it. It was really litte bit some tedious work as I can only print from it. I can export it to excel or even notepad for formatting also. Diagram gave me really limited feature which is not sufficient.

Secondly I tried with SP_help. It also didn't worked.

THirdlr I tried with creating script for the object or table that worked fine but not also fully as i wanted. I just wanted the column name in left side and its properties in formatted way in right side in 2 column format but well Script also provided me some help.

Thanks Guys.

|||

Actually, sp_help should produce the information you want. Saying "It didn't work" doesn't give us much to go on to help you. Did you get an error message? If so, what was it?

You might want to query the table metadata by using the system tables (in SS 2000) or the catalog views (in SS2005). For example, in SQL Server 2005, you can write a query like the following example to return the table and column names and their properties.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.Max_Length,
c.Precision,
c.Scale
FROM sys.objects AS o
INNER JOIN sys.columns AS c ON o.object_id = c.object_id
INNER JOIN sys.types AS t ON c.user_type_id = t.user_type_id
WHERE type = 'U'

And here's an equivalent query in SQL Server 2000.

SELECT o.name AS TableName,
c.name AS ColumnName,
t.name AS DataType,
c.length,
c.xprec,
c.xscale
FROM sysobjects AS o
INNER JOIN syscolumns AS c ON o.id = c.id
INNER JOIN systypes AS t ON c.usertype = t.usertype
WHERE o.type = 'U'

I only selected a few column properties. To see all the columns available in the system tables/views, see Books Online.

Regards,

Gail

|||

hi..Guys..

Thanks a lot ur suggestions helped me alot man...

|||

You can get the structure from the query analyser by the command --> sp_help tablename