How can I delete or alter a default constraint from table in SQL Server database for multiple database?

Question

I have gone thorough these previous questions Q1, Q2, Q3. Using this I can catch my exact constraint name. But it is not enough for me.

For example I have done somthing

ALTER TABLE dbo.Documents ADD ShowOnHandset BIT  NOT NULL DEFAULT 'FALSE' 

Here automatically my constrant named by the machine was DF__Documents__ShowO__7AB2122C on my machine. But I have run the same script in multiple PC, on those PC those constraint are almost same except the last hashed value. DF__Documents__ShowO__54A20B0D DF__Documents__ShowO__5D5216D7

I have seen that the last 8 bit hashed value is not similar. But I need to remove this constraint from all table and I want to replace them with

ALTER TABLE dbo.Documents ADD ShowOnHandset BIT  NOT NULL DEFAULT ((1))  

But I can't identify the the exact constraint name, so how can I drop it using a single script?

I have found hard-coded solution by those mentioned questions. But I need a single script to do it. Please help me to solve this.

I can catch the constraint name using this code

select name from sys.objects where name like 'DF__Documents__ShowO%'

I know the way how to delete it. Here it is.

ALTER TABLE dbo.AppSystems DROP constraint  [constraint_name]

But I am unable to do it. Because I couldn't put the value constraint_name even if I can caught it. So how could I put this name here to drop it.

Update Modified the Question.

Answer 1

Try dynamic query:

Declare @MyVariable varchar(max)
Declare @Variable varchar(max)
Set @MyVariable=(Select name from sys.objects 
                 where parent_object_id=Object_ID('Documents','U') and name like 'DF__Documents__Show%')

Set @variable='ALTER TABLE dbo.AppSystems DROP constraint' +@MyVariable

Exec(@variable)

Answer 2

Run this and output as text, then copy the result and run in another query window

Fritz with the code below to ADD the updated constraint:

;With cte As (select object_id From sys.objects where name like 'DF__Documents__ShowO%')
Select 'Alter Table ' + Object_Name(df.object_id) + N' Add Constraint Default (1) For ShowOnHandset'
From sys.default_constraints As df
Join cte As c
On c.object_id = df.object_id

This deletes the constraints

;With cte As (select object_id From sys.objects where name like 'DF__Documents__ShowO%')
Select 'Alter Table ' + Object_Name(df.object_id) + N' Drop Constraint [' + df.Name + ']'
From sys.default_constraints As df
Join cte As c
On c.object_id = df.object_id

Answer 3

In MS SQL you can specify constraint name explicit:

ALTER TABLE dbo.Documents ADD ShowOnHandset BIT  NOT NULL CONSTRAINT DF_Documents_ShowOnHandset DEFAULT 'FALSE'