June 8, 2011 at 5:32 pm
How do you programmatically drop a specific constraint on a table which has 3 default constraints?
Until recently, I have been able to programmatically drop constraints with the below script, but now I am encountering tables with more than one and for those tables it is failing.
---- drops constraint on IsActive column if one exists
IF EXISTS
(
select
object_name(constid)--default constraint
from
sysconstraints
where
[id] = object_id(@Table_Name)
and objectproperty(constid, 'IsDefaultCnst') = 1
)
BEGIN
DECLARE @sql1 nvarchar(4000)
SET @sql1 = 'ALTER TABLE '+ @Table_Name + ' DROP ' + @default
exec (@sql1)
END
ELSE
print @Table_name + ' has no constraint'
June 8, 2011 at 5:47 pm
i'd used this in the past...i originally made it in SQL 2000, so it pretty much ignores schemas and assumes dbo.
see how it;s using a cursor to loop through what may be multiple constraints for the same column, all with different names.
CREATE PROCEDURE DROP_UNIQUE_CONS_FROM_SPECIFIC_COLUMN
@TableName varchar(30),
@ColumnName varchar(30)
AS
BEGIN
DECLARE @Constraint_to_Delete varchar(100)
DECLARE Constraint_Cursor CURSOR FOR
select c_obj.name as CONSTRAINT_NAME
from sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id
where c_obj.xtype= 'D' --works for check, foreign key and 'UQ' also
and t_obj.name =@Tablename
and col.name = @ColumnName
OPEN Constraint_Cursor
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_Delete
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'ALTER TABLE [' + @TableName + '] DROP CONSTRAINT ['+@Constraint_to_Delete+ ']'
EXEC ( 'ALTER TABLE [' + @TableName + '] DROP CONSTRAINT ['+@Constraint_to_Delete+ ']' )
FETCH NEXT FROM Constraint_Cursor INTO @Constraint_to_delete
END
CLOSE Constraint_Cursor
DEALLOCATE Constraint_Cursor
END
Lowell
June 8, 2011 at 6:18 pm
Oh your goodness, you're helping me again! I am all over this and will let you know how it goes. :w00t:
June 8, 2011 at 6:45 pm
Lowell, we all agree they are acceptable for system maintenance however there is no need to use one here so I'm just as happy having one less cursor in the world.
USE AdventureWorks
GO
-----
DECLARE @table_name SYSNAME = N'[HumanResources].[Employee]' ;
-----
DECLARE @sql NVARCHAR(MAX) = N'' ;
WITH cte ( drop_cmd )
AS (
SELECT DISTINCT
N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t_obj.id)) + N'.' + QUOTENAME(t_obj.NAME) + N' DROP ' + QUOTENAME(c_obj.name) + CHAR(13) + CHAR(10) + N'GO' + CHAR(13) + CHAR(10)
FROM sys.sysobjects c_obj
JOIN sys.sysobjects t_obj ON c_obj.parent_obj = t_obj.id
JOIN sys.sysconstraints con ON c_obj.id = con.constid
JOIN sys.syscolumns col ON t_obj.id = col.id
WHERE --works for check 'C', foreign key 'F' and Unique Constraint 'UQ' also
c_obj.xtype IN ( 'D' )
AND t_obj.id = OBJECT_ID(@table_name)
)
FROM cte ;
SELECT @sql
--EXEC (@sql1)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 7:45 pm
opc.three i absolutely agree; i just happen to have a saved snippet that did what the OP was asking for...i was thinking of re-doing it, since it doesn't cover shemas; thanks for the model to finish with 😀
Lowell
June 8, 2011 at 8:05 pm
Cool, I am always happy to lend a hand in the eradication of a cursor 🙂
Mine handles schemas now but I stopped short of (actually backed out of the rabbit hole) converting to the newer views like sys.objects because I wasn't sure how much work it would be to maintain support for the 4-types of constraints the original supported.
Feel free to share your snippet down the line 🙂
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 8, 2011 at 11:09 pm
OK, just going by the select statement in Lowell's response, i don't think it answers the questoin. I am trying to drop only one of the constraints, even if there are 3, and the one I need to drop operates on a particular column in the table, which is called Active.
When I run select statement from above it is returning the names of all constraints for table 'VSite', even when I specify the column name 'Active'. That would suggest there are three different constraints for that column, but there are not.
select c_obj.name
from sysobjects c_obj
join sysobjects t_obj on c_obj.parent_obj = t_obj.id
join sysconstraints con on c_obj.id = con.constid
join syscolumns col on t_obj.id = col.id
where c_obj.xtype= 'D' --works for check, foreign key and 'UQ' also
and t_obj.name = 'VSite'
and col.name = 'Active';
What did I miss?
June 8, 2011 at 11:28 pm
I like the way opc.three's cte uses a select statement to create a drop command, which can then be executed. Very cool. So that is the new way of doing things...better performance, I am to understand. Still....it too would delete all the constraints and I need to be able to delete only the one that pertains to a specific column.
Can you help me with a query that will retrieve only one specific column's default constraints, not all the constraints of the table?
June 9, 2011 at 12:04 am
But this worked:
select t.name, c.name, d.name, d.definition
from sys.tables t
join
sys.default_constraints d
on d.parent_object_id = t.object_id
join
sys.columns c
on c.object_id = t.object_id
and c.column_id = d.parent_column_id
where t.name = 'VSite'
and c.name = 'Active'
Finched it from http://stackoverflow.com/questions/1430456/how-to-drop-sql-default-constraint-without-knowing-its-name
Still do not know why Lowell's select didn't retrieve just one DF....looked right to me.
June 9, 2011 at 8:44 am
I am confident the original query could be fixed but here is the cte with the query you just provided. The downside is that it only handles default constraints. It would require additional work to get this to handle the other constraints the original query handles.
USE AdventureWorks
GO
DECLARE @table_name SYSNAME,
@column_name SYSNAME ;
-----
-- use NULL for all
SET @table_name = N'[HumanResources].[Employee]' ;
-- use NULL for all
SET @column_name = N'SalariedFlag' ;
-----
DECLARE @sql NVARCHAR(MAX) = N'' ;
WITH cte(drop_cmd)
AS (
SELECT DISTINCT
N'ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.object_id)) + N'.' + QUOTENAME(t.name) + N' DROP ' + QUOTENAME(d.name) + CHAR(13)
+ CHAR(10) + N'GO' + CHAR(13) + CHAR(10)
FROM sys.tables t
JOIN sys.default_constraints d ON d.parent_object_id = t.object_id
JOIN sys.columns c ON c.object_id = t.object_id
AND c.column_id = d.parent_column_id
WHERE (
@table_name IS NULL
OR t.object_id = OBJECT_ID(@table_name)
)
AND (
@column_name IS NULL
OR c.name = @column_name
)
)
FROM cte ;
SELECT @sql
--EXEC (@sql1)
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 9, 2011 at 10:09 am
Yes, I see how that would work. I am sticking with my cursor based script because it is strictly an administrative task, but I appreciate knowing about this other way cte.three.
June 9, 2011 at 10:17 am
hxkresl (6/9/2011)
Yes, I see how that would work. I am sticking with my cursor based script because it is strictly an administrative task, but I appreciate knowing about this other way cte.three.
Booooo 😛
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 9, 2011 at 2:18 pm
cte.three, please give me the main points about using ctes rather than cursors.
June 9, 2011 at 2:27 pm
hxkresl (6/9/2011)
cte.three, please give me the main points about using ctes rather than cursors.
In general it's not about always using a CTE over CURSOR...the main point to take away is to always choose set-based processing over row-by-row processing where possible.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 9, 2011 at 3:22 pm
OK, to be consistent with the theoretical basis behind RDBMSs I could try, but feeling competent at a cursos based solution, rocks my world.:hehe:
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply