Script genereted by SSMS to drop a default constraint does not work (SQL Server 2008 R2)

Script genereted by SSMS to drop a default constraint does not work (SQL Server 2008 R2) by Wim SQL Server

Closed

as Fixed

Type:	Bug
ID:	610578
Opened:	10/5/2010 2:04:11 AM
Access Restriction:	Public
Primary Feedback Item:	473013

Workaround(s)

view

User(s) can reproduce this bug

I have a table called Calc.SalesItemCustomerShipTo where I want ot delete a default constraint named: DF_CalcSalesItemCustomerShipTo_SalesOrderedValue_0

If I use SSMS and right-click on the default constraint and choose:
Script Constraint As > Drop To > new file/clipboard the following script is generated:

USE ["DB"]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CalcSalesItemCustomerShipTo_SalesOrderedValue_0]') AND type = 'D')
BEGIN
ALTER TABLE [Calc].[SalesItemCustomerShipTo] DROP CONSTRAINT [DF_CalcSalesItemCustomerShipTo_SalesOrderedValue_0]
END

This script doesn't do anything because the object_id function is called with an incorrect input.
--has to be:
SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Calc].[DF_CalcSalesItemCustomerShipTo_SalesOrderedValue_0]') AND type = 'D'
--or
SELECT * FROM dbo.sysobjects WHERE name = N'DF_CalcSalesItemCustomerShipTo_SalesOrderedValue_0'
AND type = 'D' AND parent_obj=OBJECT_ID('Calc.SalesItemCustomerShipTo')

Details (expand)

English

SQL Server 2008 R2 - Enterprise Edition

Tools (SSMS, Agent, Profiler, Migration, etc.)

Windows 7 Professional

US English

CREATE SCHEMA Calc
GO
CREATE TABLE [Calc].[SalesItemCustomerShipTo](
    [ScenarioID] [smallint] NOT NULL,
    [SalesItemCustomerShipToID] [bigint] NOT NULL,
    [RevenueUnits] [float] NULL,
    [RevenueVolume] [float] NULL,
    [RevenueWeight] [float] NULL,
    [NbrOfSalesOrderlines] [int] NULL,
    [SalesOrderedValue] [float] NULL,
    [SalesOrderedQty] [float] NULL,
    [DeliveredQtyNoICT] [float] NULL,
    [NbrOfDeliveryLinesNoICT] [int] NULL,
CONSTRAINT [PK_CalcSalesItemCustomerShipTo] PRIMARY KEY CLUSTERED
([ScenarioID] ASC,[SalesItemCustomerShipToID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [Calc].[SalesItemCustomerShipTo] ADD CONSTRAINT [DF_CalcSalesItemCustomerShipTo_SalesOrderedValue_0] DEFAULT ((0)) FOR [SalesOrderedValue]
GO

USE [NextgenBase_Dev_Slot004]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CalcSalesItemCustomerShipTo_SalesOrderedValue_0]') AND type = 'D')
BEGIN
ALTER TABLE [Calc].[SalesItemCustomerShipTo] DROP CONSTRAINT [DF_CalcSalesItemCustomerShipTo_SalesOrderedValue_0]
END

X64

File Name	Submitted By	Submitted On	File Size
Capture.JPG	Wim SQL Server	10/5/2010	51 KB
Capture2.JPG	Wim SQL Server	10/5/2010	125 KB

Posted by Microsoft on 11/26/2010 at 9:33 AM

Hi,

Thanks for reporting this issue.
I am resolving this as duplicate of
http://connect.microsoft.com/SQLServer/feedback/details/473013/scripting-a-drop-of-a-column-default-is-incorrect-when-table-is-in-a-user-defined-schema
which has been fixed in Next Release (SQL Server Code-Named Denali)

Regards,
Alok

Posted by Wim SQL Server on 11/2/2010 at 7:15 AM

The script doesn't generate an error but it doesn't do anything, the default constraint is still there.
Please test with:
CREATE SCHEMA Calc
GO
IF OBJECT_ID('calc.test') IS NOT NULL
DROP TABLE calc.test

CREATE TABLE Calc.test(a CHAR(5) )

ALTER TABLE [Calc].[test]
ADD CONSTRAINT [DF_CalcTest] DEFAULT ((0)) FOR [a]

--drop constraint script from SSMS:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_CalcTest]') AND type = 'D')
BEGIN
ALTER TABLE [Calc].[test] DROP CONSTRAINT [DF_CalcTest]
END

--check if dropped:
SELECT * FROM dbo.sysobjects--returns same line
WHERE name='DF_CalcTest'

--this drops it:
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[Calc].[DF_CalcTest]') AND type = 'D')
BEGIN
ALTER TABLE [Calc].[test] DROP CONSTRAINT [DF_CalcTest]
END
SELECT * FROM dbo.sysobjects --returns nothing
WHERE name='DF_CalcTest'

The script only works on dbo tables not on tables with another schema!

Posted by Mohan Kumar - SQLVillage on 10/5/2010 at 9:24 AM

I am not sure what error message you received but I did same in SQL Server 2008 R2 and found it working with NO issue, here is what it generated for me and worked:

USE [AdventureWorks]
GO

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_ModifiedDate]') AND type = 'D')
BEGIN
ALTER TABLE [HumanResources].[Department1] DROP CONSTRAINT [DF_ModifiedDate]
END

GO