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.
LVL 16
Easwaran ParamasivamAsked:
Who is Participating?
 
Anthony PerkinsCommented:
Quite simple, remove the conditions on table and column name, as in:
SELECT  t.name, c.name, 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   dc.is_system_named = 1	    -- Comment out if you want all CONSTRAINTs and not just the ones that have not been named explicitly

Open in new window

0
 
Anthony PerkinsCommented:
What makes you think the CONSTRAINT does not have a name?
0
 
Anthony PerkinsCommented:
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

0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
Easwaran ParamasivamAuthor Commented:
@ 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?
0
 
Endurance UmenzehHead ITCommented:
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
0
 
Anthony PerkinsCommented:
Endurance Umenzeh,

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

Thanks,
Anthony
0
 
Endurance UmenzehHead ITCommented:
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
0
 
Anthony PerkinsCommented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.