By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,164 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

Scripting Databases

P: n/a
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

Jun 8 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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


Jun 8 '06 #2

P: n/a
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
Jun 8 '06 #3

P: n/a
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


Jun 9 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.