Posted by Erland Sommarskog on 06/08/06 21:49

Ryan (ryanofford@hotmail.com) writes:
> Is there an easy way to script the tables from within a database to
> include the indexes etc...
>
> Basically, if I want to script a number of tables, I can right hand
> click on them and generate the script that way. What I'd like to do is
> this same process in T-SQL if possible so I can build an SP which I can
> then run.

If you are on SQL 2000, you would have to use sp_OAmethod and friends
to run SQL-DMO. Generally, you will probably find it easier to write
it some client language which is better fitted for COM programming.

If you are SQL 2005, you would instead use SMO for scripting. I guess
you should be able to this from a CLR stored procedure. Again, T-SQL
is not the best choice here.

> When we take on a new client, we would need to take a copy of the
> structure and create a new database with the tables from a known
> 'base'.

The way we do it is that we point our build tool to our version-control
system and say "get this version". Then we know exactly what we have
shipped.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]