I create a new table with default value as given below:
IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'tAccounting' AND
COLUMN_NAME = 'CreatedDtm' AND DATA_TYPE = 'datetimeoffset') ALTER TABLE [dbo].[tAccounting] ADD [CreatedDtm] [datetimeoffset] (2) NOT NULL DEFAULT (GETDATE())
The default constraint does not have any name.
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE PARENT_OBJ = OBJECT_ID(N'tAccountingDtl') AND NAME = 'DF_tAccountingDtl_CreatedDtm' AND type = 'D') ALTER TABLE [dbo].[tAccountingDtl] ADD CONSTRAINT [DF_tAccountingDtl_CreatedDtm] DEFAULT (getutcdate()) FOR [CreatedDtm]
Using above code I check for existing constraint name. Hence I get an error Column already has a DEFAULT bound to it
I have below questions:
But this works fine in One DB. But failing in another DB. What would be the problem?
How to ensure that before creating a default constraint whether already default constraint is associated with it (which does not contain name)? If so, how to drop it?
Please do assist.