Schema script generation from T-SQL ?

Question

Schema script generation from T-SQL ?

I.W Coetzer

Ten Centuries

Points: 1060
More actions
August 26, 2009 at 1:47 am

#156145

Hi,
I have a database with thousands of tables,
I have a list of some table names,
I want to programatically in T-SQL call something like:
Generate Schema for Tables: ('tblOne', 'tblTwo');
The reason for this is that it would take me forever to generate the schema script if I used the dialogs in ms sql management studio to select the tables I want, since I want about 300 out of 50,000! yes it is a large database...

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 1

Hi

T-SQL does not support any build in feature to script the DDL for a table but there are many custom scripts on this site to generate DDL scripts.

Another option would be PowerShell and SMO library.

Flo

I.W Coetzer Ten Centuries Points: 1060 More actions · Answer 2

Hi,

Could you please guide me in the right direction?

Links to these scripts you mention, or a link to powershell article etc. that describes how to do this?

Thanks

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 3

Here are some references:

A referenced tool from sourceforge:

http://www.sqlservercentral.com/scripts/Miscellaneous/31882/

A script from this site

http://www.sqlservercentral.com/scripts/Create+DDL+sql+statements/65863/

Here you see a sample of Barry how to use SMO within VB.NET (can also be done in VB-script)

http://www.sqlservercentral.com/Forums/Topic470115-145-1.aspx

If you have SSMS 2008 or install PowerShell here you can see a sample of Jeffrey how to script a table:

http://www.sqlservercentral.com/Forums/Topic422424-338-2.aspx

I also have a script to generate DDL statements. If you want I can post a part of it. Just let me know what you want to script (tables? PKs? indexes? FKs? CKs? alter statements to add missing columns?)

Greets

Flo