Scripting Databases

[Ryan]

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.

It's really just a discussion point. Say we have a number of very
similar databases whereby there is a database per client and the
structure is almost the same. This was done for simplicity (rightly or
wrongly) and there is a fair amount of data.

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

We would likely need a table to hold the names of the tables to include
from each database. We would then create an SP passing the name of the
database to copy from and one to copy to. This would then create a
script we can then automatically run to create a new database. Could
this be done in an SP where you pass these parameters, it creates
another SP with the script and then runs it ?

I've simplified some of this, so I hope it makes sense. It's not
something I would have normally done, but I'm curious how this problem
would be approached. Often people would take a script (or use a saved
one) and run that after manually creating the database.

Thanks in advance
Ryan

[Eugene]

In enterprise manager, right click on the database - tasks - generate
sql scripts - follow the wizard.

Ryan wrote:

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.

It's really just a discussion point. Say we have a number of very
similar databases whereby there is a database per client and the
structure is almost the same. This was done for simplicity (rightly or
wrongly) and there is a fair amount of data.

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

We would likely need a table to hold the names of the tables to include
from each database. We would then create an SP passing the name of the
database to copy from and one to copy to. This would then create a
script we can then automatically run to create a new database. Could
this be done in an SP where you pass these parameters, it creates
another SP with the script and then runs it ?

I've simplified some of this, so I hope it makes sense. It's not
something I would have normally done, but I'm curious how this problem
would be approached. Often people would take a script (or use a saved
one) and run that after manually creating the database.

Thanks in advance
Ryan

[Erland Sommarskog]

Ryan (ry********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[Ryan]

Erland,

Thanks for the reply. It's what I thought, but always nice to confirm
it.

Thanks

Ryan
Erland Sommarskog wrote:

Ryan (ry********@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx