February 14, 2008 at 10:53 am
I've found a solution to this but I think there must be a better one....
I'm trying to add a Default Value Constraint but only if the Constraint doesn't already exist.
The solution I've concocted so far looks like......
if not(exists(SELECT * FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'AID_AGENCY_STAFF' AND COLUMN_NAME = 'CSR' AND COLUMN_DEFAULT IS NOT NULL))
ALTER TABLE dbo.AID_AGENCY_STAFF ADD CONSTRAINTDF_AID_AGENCY_STAFF_CSR DEFAULT 0 FOR CSR
GO
......however, in my head I'm thinking 'I'm providing a name for the constraint so I should be able to query that somehow' but I couldn't find the INFORMATION_SCHEMA table that stores that information.
Can anyone show me a better way to do this?
TIA
February 14, 2008 at 10:43 pm
Your query looks great!!
After this article I tried here and there but colud not get a compact solution, however I get a query to get all the constraints for a table, but you are trying to get which columns are not having constraints.
I am looking for this, hopefully we can get a better solution if there one exist.
Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
February 14, 2008 at 11:11 pm
Hi
You can use information_schema.table_constraints.
"Keep Trying"
February 18, 2008 at 9:18 am
Chirag (2/14/2008)
HiYou can use information_schema.table_constraints.
Have you ever tried this for a Default Value Constraint?
I can't find any trace of the constraint in question within that schema anywhere. Even doing a search just based on table name, all I see is the PKey, no other constraints.....and I know that the constraint exists because after using the table_constraint search and finding no trace of the constraint I get a 'Column already has a DEFAULT bound to it' when trying to add it.
February 18, 2008 at 10:47 am
Hi
I find a script which also get the default constraints.
This script I already added to my blog.
http://matespoint.blogspot.com
Thanks.
Mohammad Irfan
http://matespoint.blogspot.com
http://www.irfit.com
February 18, 2008 at 7:39 pm
Try this:
[font="Courier New"]if not exists (select name from sys.objects where type_desc = 'DEFAULT_CONSTRAINT' and name = 'DF_AID_AGENCY_STAFF_CSR')
ALTER TABLE dbo.AID_AGENCY_STAFF ADD CONSTRAINT DF_AID_AGENCY_STAFF_CSR DEFAULT 0 FOR CSR
[/font]
MARCUS. Why dost thou laugh? It fits not with this hour.
TITUS. Why, I have not another tear to shed;
--Titus Andronicus, William Shakespeare
February 20, 2008 at 1:30 pm
Thanks for everyone's responses.
I ended up taking it to the next level because there's really a double check that needs to be done to be 100% sure that you will be able to create the constraint. You need to make sure that the column in question doesn't already have a default value constraint and you have to make sure no constraint exists with the name you're going to use.
What I ended up with this...
CREATE FUNCTION dbo.FFG_FX_GET_CONSTRAINT_ID (@constraintName varchar(125),@tableName varchar(125),@columnName varchar(125)) RETURNS bit
AS
BEGIN
DECLARE @return int
SELECT @return = id FROM
(
SELECT constid as id FROM sysconstraints
WHERE id = (SELECT id FROM sysobjects WHERE name = @tableName)
AND colid = (SELECT colid FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = @tableName) AND name = @columnName)
UNION
SELECT id FROM sysobjects WHERE name = @constraintName
) TBL
RETURN CONVERT(bit,isnull(@return,0))
END
GO
if not(exists(SELECT 'go' WHERE dbo.FFG_FX_GET_CONSTRAINT_ID('DF_AID_AGENCY_STAFF_CSR','AID_AGENCY_STAFF','CSR') = 1))
ALTER TABLE dbo.AID_AGENCY_STAFF ADD CONSTRAINTDF_AID_AGENCY_STAFF_CSR DEFAULT 0 FOR CSR
GO
It's not too hard to follow.
If either a constraint with that name is found or a constraint on that column is found then the function will return a 1.
If you are not concerned with one of these aspects, then you can just send in '' for either the constraint name or for the table and column name.
I had to use a function because I couldn't find the way to use a SProc within a If exists() statement......so if someone knows that trick then you could easily make this a SProc (which makes more sense given its use).
July 3, 2008 at 11:36 am
Hi...I was just trying to find constraints that existed and I did it this way:
SELECT * FROM dbo.sysobjectsso
where so.name = 'DF_SortsMtfSendingProcessConfig_SShKeyFile'
and so.xtype = 'D')
I then built a test script that checked other servers to be sure that they existed....
hope this helps. I tried to make it TOO HARD, when it came down to being pretty simple.....
November 12, 2008 at 4:10 pm
I ended up taking a slightly different approach and created a stored procedure to call before creating a default constraint. The stored procedure is merely an IF EXISTS...DROP CONSTRAINT query, but determines the default name and only executes if there is in fact, a default constraint currently in place. Originally intended for use within SQL scripts sent to users, this method will not cause an error if ran more than once before a new default constraint is created.
[font="Courier New"]CREATE PROCEDURE [dbo].[spDropDefaultConstraint]
(
@tableName varchar(100),
@columnName varchar(100)
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @dfName varchar(100)
SELECT @dfName = df.[name]
FROM sys.columns c
JOIN sys.default_constraints df
ON df.parent_object_id = c.object_id
AND df.parent_column_id = c.column_id
WHERE c.object_id = OBJECT_ID(@tableName)
AND c.[name] = @columnName
IF @dfName IS NOT NULL
BEGIN
EXEC ('ALTER TABLE ' + @tableName + ' DROP CONSTRAINT ' + @dfName)
PRINT 'Constraint [' + @dfName + '] dropped for ' + @tableName + '.' + @columnName
END
ELSE
PRINT 'Constraint not found for ' + @tableName + '.' + @columnName
END[/font]
So instead of the usual:
[font="Courier New"]ALTER TABLE invrecvr ADD CONSTRAINT DF_invrecr_IsExcluded DEFAULT 0 FOR IsExcluded[/font]
And receiving the warning: [font="Courier New"]Column already has a DEFAULT bound to it.[/font]
Just add this line prior to the ALTER TABLE command:
[font="Courier New"]EXEC spDropDefaultConstraint @tableName = 'invrecvr', @columnName = 'IsExcluded'[/font]
This could have easily been incorpoarated as a function, but either way, it solves the problem.
Michael
November 17, 2008 at 6:48 am
I also ended up creating a view to be able to see the DF constraints the same way I can see any other constraint via Information Schema views.
It doesn't have everything the official views has but it works pretty well for my needs...
CREATE VIEW [dbo].[SYS_V_DEFAULT_CONSTRAINTS] AS
SELECT c.name as constraint_name,t.name as table_name,cl.name as column_name
FROM
sysconstraints cn
INNER JOIN
sysobjects c on cn.constid = c.id
INNER JOIN
sysobjects t on cn.id = t.id
INNER JOIN
syscolumns cl on t.id = cl.id and cn.colid = cl.colid
WHERE c.xtype = 'D'
January 30, 2009 at 4:58 am
You can simply try using:
SELECT [name] from sys.default_constraints
WHERE parent_object_id = OBJECT_ID(N'[dbo].tbYourTableName')
AND ([name] = 'YourConstraintName' )
May 9, 2011 at 4:57 pm
This is a good solution i have used this like this
Many thanks
Rohan
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply