How to remove WITH FILLFACTOR = 100 when Generate Sql Script ?

[rabii]

Hello,

I'm using Entreprise Manager (for Sql Server 2000) to generate my
database's script. By mistake, i've changer FillFactor one time. And,
now I can't remove this data from generated sql script. How to remove
that ?

Thank's a lot.

[Erland Sommarskog]

rabii (ra********@gmail.com) writes:

I'm using Entreprise Manager (for Sql Server 2000) to generate my
database's script. By mistake, i've changer FillFactor one time. And,
now I can't remove this data from generated sql script. How to remove
that ?

Can't you just run the file through a search/replace session in some
text editor? Then you could build a new database, and then script
from that one.

Even better, have all your SQL code under version control, so you
don't have to script from the databaes.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[rabii]

Thanks for reply :),

I know this solutions :), but, the problem that I have a production
database. For each new version of my product, I have to compare my new
database schema and the old one (current production database). So the
tool "Sql Server Compare" (by comparing the databases script) show me
always that all the table are different although the difference is
simply the FILLFACTOR (in production database). Now I can change it
using Entreprise Manager but I can't remove it ?

[Thomas R. Hummel]

If that's Red-Gate's SQL Compare, I think that there is an option to
ignore fillfactors, among other things. Unfortunately, I'm at a machine
that doesn't have that tool installed, so I can't confirm that. I know
that you can definitely ignore some things, and I *think* that
FILLFACTOR is one of them.

Good luck,
-Tom.

[Erland Sommarskog]

rabii (ra********@gmail.com) writes:

I know this solutions :), but, the problem that I have a production
database. For each new version of my product, I have to compare my new
database schema and the old one (current production database). So the
tool "Sql Server Compare" (by comparing the databases script) show me
always that all the table are different although the difference is
simply the FILLFACTOR (in production database). Now I can change it
using Entreprise Manager but I can't remove it ?

So where is the fill factor wrong? In the development database or in
the production database? But whichever, can't you just adapt the
fillfactor of the development database to the production database?

Then again, fill factor is one of these things that could be different
from database to database, because different instances of the schema
has different data load. So you should probably see if your comparison
tool can ignore difference in fill factor.

Finally, I can't keep from saying that the whole thing of comparing
database schemas to build change scripts is not a very good idea. Do
you really want all sort of test junk in the dev database hit the
production server? If you have your code under version control,
you can build change scripts from the version-control system. This
gives you a much more solid base to stand on.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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

[rabii]

thanks again for every one :)

I've said that I'm using the tool "Sql Server Compare" (it's a free
tool).

You're right when you said that it is better to use a version control
solution. But now the problem, that by mistake, I've added fillfactor =
x in the production database. In the dev database, I havn't did that.
When I compare the two databases, I notice that the comprator (sql
server compare) show me that all the table are different. When I
analyse the code, simply the fill factor is not the same. I can resolve
that by adding the same fillfactor in my dev database but I wan't to
know " if there is a way by script to remove fillfactor = x" ?

[Erland Sommarskog]

rabii (ra********@gmail.com) writes:

I've said that I'm using the tool "Sql Server Compare" (it's a free
tool).

You're right when you said that it is better to use a version control
solution. But now the problem, that by mistake, I've added fillfactor =
x in the production database. In the dev database, I havn't did that.
When I compare the two databases, I notice that the comprator (sql
server compare) show me that all the table are different. When I
analyse the code, simply the fill factor is not the same. I can resolve
that by adding the same fillfactor in my dev database but I wan't to
know " if there is a way by script to remove fillfactor = x" ?

For an index you should be able to get rid of with with CREATE INDEX ...
WITH DROP_EXISTING. If it's a PRIMARY KEY constraint of a UNIQUE constraint,
I think you have to drop and recreate. Since you typically have FK
constraints to a PK, that can be kind of messy.

I played around a little, and it seems that Enterprise Manager does not
include fillfactor for primary keys, so changing your dev database may
not help.

Looks as if you either have to rebuild the production database or give
SQL Server Compare the boot...
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

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