how to generate a script for the entire database-CodePudding

I've attempted to

When attempting to execute the generated script, it works on the initial database creation.

All subsequent attempts to run the generated script result in the following error:

Msg 1781, Level 16, State 1, Line 8739
Column already has a DEFAULT bound to it.
Msg 1750, Level 16, State 0, Line 8739
Could not create constraint or index. See previous errors.

This code seems to work on initial database creation but not on subsequent exections:

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[cfg].[DF__LookupCol__IsPri__46DD686B]') AND type = 'D')
BEGIN
ALTER TABLE [cfg].[LookupColumns] ADD  DEFAULT (CONVERT([bit],(0))) FOR [IsPrimaryKey]
END
GO

I've attempted it this as well with the same result:

ALTER TABLE [cfg].[LookupColumns] ADD CONSTRAINT LookupColumnsIsPrimaryKeyConstraint DEFAULT (CONVERT([bit],(0))) FOR [IsPrimaryKey]

What am I doing wrong? What is the meaning of this error? Column already has a DEFAULT bound to it?

CodePudding user response：

This is indeed because you didn't name your constraints, so the second time it attempts to drop the old name then add an additional constraint.

The first time you run the script it checks for a default constraint called DF__LookupCol__IsPri__46DD686B, which of course does not exist.

Then it runs

ALTER TABLE [cfg].[LookupColumns] ADD DEFAULT (CONVERT([bit],(0))) FOR [IsPrimaryKey]

Which creates a new constraint with a new auto-generated name. Next time you run the script, it tries to drop DF__LookupCol__IsPri__46DD686B again, which still doesn't exist, and then tries to add a new constraint, which fails.

So to be able to re-run this script, you should name all your constraints and indexes before generating the scripts.

You can find the system-named default constraints like this:

select name, object_name(object_id) table_name 
from sys.default_constraints 
where is_system_named = 1

select name, object_name(object_id) table_name 
from sys.check_constraints 
where is_system_named = 1

select name, object_name(object_id) table_name 
from sys.key_constraints 
where is_system_named = 1

etc

Or you can look for the __ in the names, but you would want to manually review those of course.