generating alter table script

[Matik]

Hello,

I need to change collation in my database (more databases acctualy).
Therefore, I wanted to make a script, which will do it at one more
time.

I already have a cursor, updating collation on all tables (fields) in
database.
The problem is, before I will to update the collations, I need to drop
all constrains and pk's.
But I do not want to erase them. After the collation will be updated,
these should be restored.
Therefore I wanted to additionaly script all cs, pk's for all tables,
drop them, and after updateing the collation, update the tables.

Does enyone have an idea how to do that? Or how can I get (in tsql) an
string containing information like:

ALTER TABLE [dbo].[PrmUserGroup] ADD
CONSTRAINT [FK_PrmUserGroup_PrmGroup] FOREIGN KEY
(
[id_group]
) REFERENCES [dbo].[PrmGroup] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE ,
CONSTRAINT [FK_PrmUserGroup_PrmUser] FOREIGN KEY
(
[id_user]
) REFERENCES [dbo].[PrmUser] (
[id]
) ON DELETE CASCADE ON UPDATE CASCADE
GO

If i can have such a part of script (it will complete script of cs and
pk's) i could store it temporary in the table, and after updating a
collation, run them with dynamic sql.

Or maybe there is any other, better way?

Thank you in advance

Mateusz

[Erland Sommarskog]

Matik (ma****@sauron.xo.pl) writes:

I need to change collation in my database (more databases acctualy).
Therefore, I wanted to make a script, which will do it at one more
time.

I already have a cursor, updating collation on all tables (fields) in
database.
The problem is, before I will to update the collations, I need to drop
all constrains and pk's.
But I do not want to erase them. After the collation will be updated,
these should be restored.
Therefore I wanted to additionaly script all cs, pk's for all tables,
drop them, and after updateing the collation, update the tables.

Does enyone have an idea how to do that? Or how can I get (in tsql) an
string containing information like:

I would rather build a new database from scripts, and then use BCP to
bulk data out and in. You can easily script the BCP commands from
sysobjects. Don't forget to include a condition to get -E for tables
with the IDENTITY property.

Best is to apply constraints after the load, but you can load them as
NOCHECK and the enable them with CHECK WITH CHECK. (That's a horrible
syntax, but it's important to actually verify the constraints when you
enable them, or else the optimizer cannot make use of them.)
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp