asked on

Default constraint related in SQL

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())

Open in new window

The default constraint does not have any name.

GO
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]
GO

Open in new window

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.

Anthony Perkins

What makes you think the CONSTRAINT does not have a name?

Anthony Perkins

Actually after re-reading your question I think I understand it now. You are asking how do you identify DEFAULT CONSTRAINTS that have not been named explicitly. Here is how:

SELECT  dc.*
FROM    sys.tables t
        INNER JOIN sys.columns c ON t.object_id = c.object_id
        INNER JOIN sys.default_constraints dc ON t.object_id = dc.parent_object_id
                                                 AND c.column_id = dc.parent_column_id
WHERE   t.name = 'YourTableNameGoesHere'
        AND c.name = 'YourColumnNameGoesHere'
	AND dc.is_system_named = 1	    -- Comment out if you want all CONSTRAINTs

Open in new window

Easwaran Paramasivam

ASKER

@ acperkins: I would like to search the default constraints all over the DB without explictly providing Table or Column name. Please let me know how to achieve it?

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Endurance Umenzeh

Dear Team,

I'm currently have challenges with my SAGE Pastel Evolution Synchronization. I' m running Inter branch accounting with 16 depots.
None of the 16 Branches are importing from Consolidated. The two major errors i'm getting are:
1. 2014-11-06 12:40:38.260 [E] An unexpected error occurred during import. Violation of PRIMARY KEY constraint 'PK__etblDeleted'. Cannot insert duplicate key in object 'dbo._etblDeleted'
2. [W] Import of "F:\ftp\Consolidation\0000-0016-000000.dat" was unexpected. Expected update no: 160.
3.Argument is incomplete.
Thank You as you help unravel this issues

Anthony Perkins

Endurance Umenzeh,

Rather than piggy-backing on this thread, I suggest you may have better luck if you post your own question.

Thanks,
Anthony

Endurance Umenzeh

Dear Anthony Perkins,

Thank You for your comment, but that is actually my question. What you saw as Piggy -Backing is the error log am getting due to inability to achieve Synchronization.
Thank You

Anthony Perkins

Suit yourself. If you want quick help with your question, post a new question with a link to this one if you think it is appropriate. If you don't need quick help, then don't bother about posting a new question.