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.

score 1 · Answer 1 · answered Jun 02 '17 at 09:56

1

In MS SQL you can specify constraint name explicit:

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

answered Jun 02 '17 at 09:56

Mikhail Lobanov

2,901
7
23

1

Think the OP knows that, Trouble is they don't know the system generated names. – Rachel Ambler Jun 02 '17 at 09:57

Rachel Ambler · Accepted Answer · 2017-06-02T10:18:55.577

1

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

edited Jun 02 '17 at 10:18

answered Jun 02 '17 at 09:57

Rachel Ambler

1,233
9
22

What is `Name` here? please say. – gdmanandamohon Jun 02 '17 at 10:01
Updated to include your criteria – Rachel Ambler Jun 02 '17 at 10:01
It is showing me a error `Ambiguous column name 'object_id'.` – gdmanandamohon Jun 02 '17 at 10:03
Sorry - I'd f'd up a little - it's already fixed. Penalty for typing code in on the fly without testing! – Rachel Ambler Jun 02 '17 at 10:03
How can I give new property on it? Please specify that by `BIT NOT NULL DEFAULT ((1)) ` – gdmanandamohon Jun 02 '17 at 10:14
This just allows you to delete the defaults. If you want to create new ones then modify my script to change the output to ADD the new defaults, save that off, THEN delete the old ones. – Rachel Ambler Jun 02 '17 at 10:15
Could you please do me a favor. I am too poor to write that combined query. As I don't understand you query too. But I have seen that it is working. – gdmanandamohon Jun 02 '17 at 10:17
With all due respect, I've answered your question you raised now, so please, work with what I've given you and accept or ignore my answer (that I've spent the last 20 minutes on). I need to get back to work. – Rachel Ambler Jun 02 '17 at 10:21
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/145710/discussion-between-lazycoder-and-rachel-ambler). – gdmanandamohon Jun 02 '17 at 10:24
Let's not. I'm working. Please respect my work on this and move on. – Rachel Ambler Jun 02 '17 at 10:27
I have solved it. Thank you for you kind support. I have solved it by using a dynamic query by just using a variable. Thanks for your kind support. – gdmanandamohon Jun 02 '17 at 11:08

JayaPrakash · Answer 3 · 2017-06-02T12:06:24.253

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)

edited Jun 02 '17 at 12:06

answered Jun 02 '17 at 10:47

JayaPrakash

179
1
6

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

3 Answers3

Linked