Putting Table Structure into Word Using Procedure

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Putting Table Structure into Word Using Procedure

Author

Topic

yasin
Yak Posting Veteran

68 Posts

Posted - 2006-01-22 : 08:31:35

Hi there

I have nearly 1000 table. I need all the table structure in word document. Is it possible to take table structure using Stored Procedure. If it is How to do that.

Thanx in advance

by
yasi

Kristen
Test

22859 Posts

Posted - 2006-01-22 : 08:37:44

You could, but I think you'd be better off with something that "diagrams" it for you.

How do you want it in word - just a list of tables & columns? Or do you need datatypes? PKs? Indexes? FKs? Default [etc] Constraints?

If you just want Tables & Columns it would be easy; the more of those you need he more you would be better off with a 3rd party tool, I reckon.

Kristen

afrika
Master Smack Fu Yak Hacker

2706 Posts

Posted - 2006-01-22 : 08:38:51

cross post
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=60677

yasin
Yak Posting Veteran

68 Posts

Posted - 2006-01-22 : 08:58:41

Kristen

Yes. I need table, column with Data Type only. How can i do that. Thanx in advance Kristen.

by
yasi

Nazim
A custom title

1408 Posts

Posted - 2006-01-22 : 09:27:20

If you dont have any third Party Tool.
you can do this,In Enterprise Manager Right Client on the Database, Click On All Tasks->Generate SQLScript.
Select All Tables(if you want only Table Structure's)
Click On Preview
Click On Copy
Paste it in your Word Doc.

You might have to Edit it to meet your requirements.

HTH

-------------------------
What lies behind you and what lies ahead of you are small matters compared to what lies within you.-Ralph Waldo Emerson

yasin
Yak Posting Veteran

68 Posts

Posted - 2006-01-22 : 09:42:53

Hello Mr. Nazim.

Now i got only the script on the word document. I want all columns name of table and data type in the word document. This is i want.
Can you help ne in this regards

thanx in advance

by
yasi

Kristen
Test

22859 Posts

Posted - 2006-01-22 : 09:51:34

You probably want something like this then (note there are other columns for Width, Precision, etc. that might be useful too)


SELECT	C.TABLE_SCHEMA + '.' + C.TABLE_NAME,
	C.COLUMN_NAME,
	C.DATA_TYPE
FROM	INFORMATION_SCHEMA.COLUMNS AS C
	JOIN INFORMATION_SCHEMA.TABLES AS T
		 ON T.TABLE_SCHEMA = C.TABLE_SCHEMA
		AND T.TABLE_NAME = C.TABLE_NAME
WHERE	    T.TABLE_TYPE = 'BASE TABLE'	-- Exclude 'VIEW'
	AND C.TABLE_NAME NOT IN ('dtproperties')
ORDER BY C.TABLE_SCHEMA, C.TABLE_NAME, C.ORDINAL_POSITION

Edit: Possibly Order By C.COLUMN_NAME instead of C.ORDINAL_POSITION

Kristen

yasin
Yak Posting Veteran

68 Posts

Posted - 2006-01-22 : 10:34:00

Hello Mr. Kriston

I need the tables information using Stored Procedure as follows

Table Name :
column Name Data Type & Size

Then how ...

Thanx in advance

Kristen
Test

22859 Posts

Posted - 2006-01-22 : 10:37:22

"Then how"

Then build a stored procedure with the query I've given you above, plus the appropriate columns from the INFORMATION_SCHEMA.COLUMNS "table" to give whatever you need for the Size data in your report, as I suggested.

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources