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.
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.
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.
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).
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.
