Home Dashboard Directory Help
Search

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


Status: 

Closed
 as Fixed Help for as Fixed


4
0
Sign in
to vote
Type: Bug
ID: 610578
Opened: 10/5/2010 2:04:11 AM
Access Restriction: Public
Primary Feedback Item: 473013
0
Workaround(s)
view
2
User(s) can reproduce this bug

Description

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
Sign in to post a comment.
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 - SQLServerExperts.com 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
Sign in to post a workaround.
File Name Submitted By Submitted On File Size  
Capture2.JPG 10/5/2010 125 KB
Capture.JPG 10/5/2010 51 KB