Question on generating SQL scripts using Enterprise Manager

[drdeadpan]

Hi,

I was using enterprise manager to generate a script for my DB. I
scripted only my tables and views and in Options I picked all the
options EXCEPT "script Primary Keys, Foreign Keys and Constraits " (
which I was going to script seperately ). I noticed that the the
generated file still had all FKs and PKs scripted. When I additionally
unchecked the "script Full-Text indexes" option, it worked as expected.
Any idea why the full-text option causes all constraints to be
scripted. Using SQL server 2000.

Thanks

[Erland Sommarskog]

drdeadpan (vk***********@yahoo.com) writes:

I was using enterprise manager to generate a script for my DB. I
scripted only my tables and views and in Options I picked all the
options EXCEPT "script Primary Keys, Foreign Keys and Constraits " (
which I was going to script seperately ). I noticed that the the
generated file still had all FKs and PKs scripted. When I additionally
unchecked the "script Full-Text indexes" option, it worked as expected.
Any idea why the full-text option causes all constraints to be
scripted. Using SQL server 2000.

Sounds like a bug.

It would be interesting to see a repro. That is a complete database script
with at most three tables with all these features, and when scripted in
EM displays all these problems. I doubt that the bug will ever be fixed
in Enterprise Manager, but since I'm on the SQL 2005 beta, I would like
to test if the problem is there as well.

By the way, did your tables actually have any full-text indexes?
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[drdeadpan]

Thanks Erland for your response.

No, we have NO full text indexes defined. Our tables have a rather
large number of columns so rather than pasting the script here ,I
tested it again. This time I picked 3 tables to be scripted with the
following options.

Script Database
Script database users and database roles
Script object-level permissions
Script indexes
Script full-text indexes.

The above options once again scripted all PKs and FKs even though it
was not requested.

I reran the script without the full-text scripting option and it works
fine i.e no Pks and FKs. SO, I guess it is prefectly reproducable on
Sql Server 2000. I just wanted to make sure I was'nt seeing things.
Great website BTW.

DrD

[Erland Sommarskog]

drdeadpan (vk***********@yahoo.com) writes:

No, we have NO full text indexes defined. Our tables have a rather
large number of columns so rather than pasting the script here ,I
tested it again. This time I picked 3 tables to be scripted with the
following options.

Script Database
Script database users and database roles
Script object-level permissions
Script indexes
Script full-text indexes.

The above options once again scripted all PKs and FKs even though it
was not requested.

You don't have to post your actual tables. It's enough to post a few
tables for which the problem appears.

Anyway, I was able to reproduce the problem in SQL 2000, but when I did
a quick test in SQL 2005, no constraints were brought it.

As I mentioned earlier, the likelyhood that this will be fixed in SQL2000
is about nil.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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