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

William Brewer gravatar image

William Brewer
71 2 2 4

(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

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.5k 61 65 87

Just gonna agree. You want to use SMO/DMO. Powershell would be a good way to do it and there are PS scripts out there already.
Oct 12, 2009 at 12:57 PM Jack Corbett
(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

David 1 gravatar image

David 1
1.8k 1 3

Oct 20, 2009 at 05:59 AM Melvyn Harbour 1 ♦♦
(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

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

I always have to spend time giving the SSMS results a good haircut if I use this technique. I also like to put in my own 'if exists, then don't bother' code. Also, I occasionally like using a text editor to program and it is nice not to have to crank up SSMS just to get the latest table script. I have to admit also that I just like creation scripts to look a particular way, I guess.
Oct 12, 2009 at 02:22 PM William Brewer
If you don't like the SSMS ending, you might want to spend a little time and write a routine to "give the haircut" for you. It might be better than trying to ensure you have all the correct script possibilities from the start.
Oct 13, 2009 at 03:50 PM Steve Jones - Editor ♦♦
(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

dvroman gravatar image

1.1k 2 2

(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



Answers and Comments

SQL Server Central

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



asked: Oct 12, 2009 at 12:39 PM

Seen: 1772 times

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