x

Is it possible to create a table build-script from the information schema?

I can get the script of all the routines for the database I'm working on from the QA/SSMS. However, Tables are a problem because there is no associated text that is stored in the database (I've never really worked out why this should be). Is there a way of creating a build script via the information schema. I've never worked out how to do it.

more ▼

asked Oct 12, 2009 at 12:39 PM in Default

avatar image

William Brewer
71 2 2 6

(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

It depends how far you want to go with it. I've written a large library which scripts things out of the database based on system meta-data, and in total it's about 130,000 lines of code.

If you're not worried about it being immensely quick, then your best option is to use DMO/SMO.

If you really want to go with the meta-data route, then you can quite simply join from sys.tables -> sys.columns -> sys.types to get most of the way there. There are little complications (like -1 for length means MAX, and the length will be double what is specified in the CREATE statement for unicode types).

Then you get to things like IDENTITY properties, and computed columns, which make it more complicated still.

After that, there are FILESTREAM and SPARSE columns.

If you can, use SMO/DMO.

more ▼

answered Oct 12, 2009 at 12:44 PM

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

(comments are locked)
10|1200 characters needed characters left

It's not possible to script databases accurately using the information schema tables alone because they don't contain information about all database features - no indexes for example. I think it must be possible using the system tables but I wouldn't bother. Use a tool such as the Red Gate toolset or Visual Studio Team System that will do it all for you.

more ▼

answered Oct 19, 2009 at 07:14 PM

avatar image

David 1
1.8k 3 5

(comments are locked)
10|1200 characters needed characters left

If you right click the table(s) in SSMS, you can generate a script for the DDL. You can do it with code, but I'm not sure you get much out of it, other than some T-SQL practice.

more ▼

answered Oct 12, 2009 at 12:56 PM

avatar image

Steve Jones - Editor ♦♦
5.1k 79 93 87

(comments are locked)
10|1200 characters needed characters left

I use ScriptDB http://scriptdb.codeplex.com/ to generate a backup of our source. It will do all of the objects in the database that you select. I did this because we upgraded from SQL server 7 & 2000 to 2005 and I needed a backup of the objects before I put them into sourcesafe. It successfully created over 130000 objects from our database and the only problem is that one of the databases had over 15000 objects which Windows has problems with (not SQL Server).

more ▼

answered Oct 12, 2009 at 07:08 PM

avatar image

dvroman
1.1k 1 4 4

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1066
x152

asked: Oct 12, 2009 at 12:39 PM

Seen: 1942 times

Last Updated: Oct 12, 2009 at 12:39 PM

Copyright 2016 Redgate Software. Privacy Policy