drop column with default
-
Anyone know if there's a way to drop a column that has been previously added with a default.
e.g.
ALTER TABLE Test
ADD newcol int NOT NULL DEFAULT 0;
It seems that to drop this column, I must first remove the default, but I don't know what this default is called so how can I delete it? Or can I somehow get SQL Server to ignore the default in the DROP Column statement?
Doug
-
select * from sysobjects where xtype ='D' and parent_obj = OBJECT_ID('Table1')
above will give you all the default constrainst that the table1 has
My Blog:
http://dineshasanka.spaces.live.com/ -
This is happening automatically on a website (through ColdFusion) and transparent to the user, so I need to identify this particular default on this particular column in this table.
Can I do that? And can I do it all within the one database access? Or can I somehow identify the default and drop it and then drop the column.
Doug
-
From the info you gave me, I have got it working like this:
SELECT name
FROM sysobjects
WHERE name LIKE 'DF__Test__newcol%';
ALTER TABLE Test
DROP CONSTRAINT #CF puts the result of the previous select here#;
ALTER TABLE Test
DROP COLUMN newcol;
Is there a way to write these three separate database calls in one single statement?
ColdFusion (CF) puts in the table name and column name.
-
use this sp
CREATE PROCEDURE usp_DropColumn
@TableNameVarchar(50),
@ColumnNameVarchar(50)
AS
/*====================================================================================================
NAME: usp_DropcolumnTYPE: stored procedure (SQL 7)CREATION DATE: 08/01/01
USAGE:
DECLARE @TableNameVarchar(50)
DECLARE @ColumnNameVarchar(50)
SET @TableName = 'MyTable'
SET @ColumnName = 'MyColumn'
Exec usp_DropColumn @TableName, @ColumnName
PURPOSE: To Drop a Column from a Table irrespective of the constraints
defined on it.
Verifications:
Check existence of table
Check existence of column within table
Check if this is the only column in the table
Check existence of Default constraint on the column
Check existence of Multiple Foreign Key constraints on the column
Check existence of a Primary Key constraint on the column
Check existence of Multiple Unique Constraint/Indexs on the column
NOTE : Only Table Owner or a dbo can Alter the Table.
=====================================================================================================*/
/*___________________________________________________________________________________________________*/
SET NOCOUNT ON
/* Declaring Variables*/
Declare @Qry1Varchar(1000)
Declare @Qry2Varchar(1000)
Declare @Qry3Varchar(1000)
Declare @Qry4Varchar(1000)
Declare @Qry5Varchar(1000)
Declare @Qry6Varchar(1000)
Declare @DFNameVarchar(500)
Declare @FKNameVarchar(500)
Declare @PKFKNameVarchar(500)
Declare @Tablename2Varchar(500)
Declare @PKNameVarchar(500)
Declare @UixNameVarchar(500)
BEGIN TRAN
-- Check Existence of Table.
if exists (select * from sysobjects where id = object_id(@TableName) and
OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
-- Check to see that the column already exist
IF (SELECT COLUMNPROPERTY( OBJECT_ID(@TableName),@ColumnName,'AllowsNull'))
IS NOT NULL
Begin
--Check to see if the column count in the table is more than 1.
IF (SELECT Count(*) FROM syscolumns sc INNER JOIN sysobjects so
ON sc.id = so.id
WHERE so.name = @TableName) <= 1
BEGIN
Raiserror('ERROR : %s is the only column in the table %s. It cannot be
dropped',16,1, @ColumnName, @TableName)
Rollback Tran
Return(@@Error)
END
--Check to see if the column has a Default constraint defined on it.
IF Exists (SELECT syscolumns.* FROM syscolumns INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN sysobjects so
ON syscolumns.cdefault = so.id
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
AND syscolumns.cdefault 0
)
BEGIN
SELECT @DFName = so.name
FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id
INNER JOIN sysobjects so ON syscolumns.cdefault = so.id
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
AND syscolumns.cdefault 0
SET @Qry1 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @DFName
Print 'Dropping Default constraint ' + @DFName
Exec(@Qry1)
IF @@Error 0
BEGIN
Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1,
@DFName, @TableName)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'Default ' + @DFName + ' Dropped from ' + @TableName + ' Table.'
END
--Check to see if the column has a FK constraint defined on it.
IF Exists (SELECT so.name FROM syscolumns INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN sysreferences
ON syscolumns.id = sysreferences.fkeyid
INNER JOIN sysobjects so
ON sysreferences.constid = so.id
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
)
BEGIN
DECLARE FK_Cursor CURSOR FOR
SELECT so.name FROM syscolumns INNER JOIN sysobjects ON syscolumns.id =
sysobjects.id
INNER JOIN sysreferences ON syscolumns.id = sysreferences.fkeyid
INNER JOIN sysobjects so ON sysreferences.constid = so.id
WHERE sysobjects.name = @TableName AND syscolumns.name = @ColumnName
OPEN FK_Cursor
FETCH NEXT FROM FK_Cursor
INTO @FKName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Qry2 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @FKName
Print 'Dropping FK constraint ' + @FKName
Exec(@Qry2)
IF @@Error 0
BEGIN
Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1,
@FKName, @TableName)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'FK ' + @FKname + ' Dropped from ' + @TableName + ' Table.'
FETCH NEXT FROM FK_Cursor INTO @FKName
END/* WHILE @@FETCH_STATUS = 0*/
CLOSE FK_cursor
DEALLOCATE FK_cursor
END
--Check to see if the column has a PK constraint defined on it.
--This even takes care of Unique Constraints defined on the column.
IF Exists (SELECT so2.name
FROM sysobjects so1
INNER JOIN sysobjects so2
ON so1.id = so2.Parent_Obj
INNER JOIN sysindexes
ON so2.name = sysindexes.name
INNER JOIN sysindexkeys
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid = sysindexkeys.indid
INNER JOIN syscolumns
ON so1.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
WHERE so1.name = @TableName
AND so2.xtype = 'PK'
AND syscolumns.name = @ColumnName
)
BEGIN
-- Check to see if the PK is being Referenced by a FK
IF Exists (SELECT so.name
FROM sysreferences
INNER JOIN syscolumns
ON sysreferences.rkeyid = syscolumns.id
INNER join sysobjects
ON sysreferences.rkeyid = sysobjects.id
INNER JOIN sysobjects so
ON sysreferences.constid = so.id
WHERE syscolumns.name = @ColumnName
AND sysobjects.name = @TableName
)
BEGIN
DECLARE PKFK_Cursor CURSOR FOR
SELECT so.name, so2.name
FROM sysreferences INNER JOIN syscolumns
ON sysreferences.rkeyid = syscolumns.id
INNER join sysobjects ON sysreferences.rkeyid = sysobjects.id
INNER JOIN sysobjects so ON sysreferences.constid = so.id
INNER JOIN sysobjects so2
ON sysreferences.fkeyid = so2.id
WHERE syscolumns.name = @ColumnName AND sysobjects.name = @TableName
OPEN PKFK_Cursor
FETCH NEXT FROM PKFK_Cursor
INTO @PKFKName, @TableName2
WHILE @@FETCH_STATUS = 0
BEGIN
SET @Qry3 = 'ALTER TABLE ' + @TableName2 + ' DROP Constraint ' +
@PKFKName
Print 'Dropping FK constraint ' + @PKFKName
Exec(@Qry3)
IF @@Error 0
BEGIN
Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1,
@PKFKName, @TableName2)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'FK ' + @PKFKname + ' Dropped from ' + @TableName2 + ' Table.'
FETCH NEXT FROM PKFK_Cursor INTO @PKFKName, @TableName2
END/* WHILE @@FETCH_STATUS = 0*/
CLOSE PKFK_cursor
DEALLOCATE PKFK_cursor
END
-- Drop the PK now
SELECT @PKName = so2.name
FROM sysobjects so1
INNER JOIN sysobjects so2
ON so1.id = so2.Parent_Obj
INNER JOIN sysindexes
ON so2.name = sysindexes.name
INNER JOIN sysindexkeys
ON sysindexes.id = sysindexkeys.id
AND sysindexes.indid = sysindexkeys.indid
INNER JOIN syscolumns
ON so1.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
WHERE so1.name = @TableName
AND so2.xtype = 'PK'
AND syscolumns.name = @ColumnName
SET @Qry4 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' + @PKName
Print 'Dropping PK constraint ' + @PKName
Exec(@Qry4)
IF @@Error 0
BEGIN
Raiserror('ERROR : Failed to Drop constraint %s From %s Table.',16,1,
@PKName, @TableName)
RollBack Tran
Return(@@Error)
END
ELSE
Print 'PK ' + @PKname + ' Dropped from ' + @TableName + ' Table.'
END
--Check to see if the column has a Unique Index defined on it.
IF Exists (SELECT sysindexes.name
FROM syscolumns INNER JOIN sysobjectsON syscolumns.id =
sysobjects.id
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
INNER JOIN sysindexkeys
ON sysindexkeys.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
AND sysindexkeys.indid = sysindexes.indid
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
)BEGIN
DECLARE IX_Cursor CURSOR FOR
SELECT sysindexes.name
FROM syscolumns INNER JOIN sysobjects
ON syscolumns.id = sysobjects.id
INNER JOIN sysindexes
ON sysobjects.id = sysindexes.id
INNER JOIN sysindexkeys
ON sysindexkeys.id = syscolumns.id
AND sysindexkeys.colid = syscolumns.colid
AND sysindexkeys.indid = sysindexes.indid
WHERE sysobjects.name = @TableName
AND syscolumns.name = @ColumnName
OPEN IX_Cursor
FETCH NEXT FROM IX_Cursor
INTO @UIXName
WHILE @@FETCH_STATUS = 0
BEGIN
Print 'Dropping Index ' + @UIXName + ' from ' + @TableName + ' Table'
-- If the Index Name is there in sysobjects then it's a Unique Constraint
-- Otherwise it's a Unique Index.
IF Exists(SELECT * FROM sysobjects WHERE name = @UIXName)
SET @Qry5 = 'ALTER TABLE ' + @TableName + ' DROP Constraint ' +
@UIXName
ELSE
SET @Qry5 = 'DROP INDEX ' + @TableName + '.' + @UIXName
Exec(@Qry5)
IF @@Error 0
BEGIN
Raiserror('ERROR : Failed to Drop Index %s from %s Table',16,1,
@UIXName, @TableName)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'Index ' + @UIXName + ' removed from ' + @TableName + ' table'
FETCH NEXT FROM IX_Cursor INTO @UIXName
END
CLOSE IX_cursor
DEALLOCATE IX_cursor
END
-- Drop the Column Now. All the constraints have been removed.
SET @Qry6 = 'ALTER TABLE ' + @TableName + ' DROP COLUMN ' + @ColumnName
Exec(@Qry6)
IF @@Error 0
BEGIN
Raiserror('ERROR : Failed to Drop column %s from %s Table',16,1,
@ColumnName, @TableName)
Rollback Tran
Return(@@Error)
END
ELSE
Print 'Column ' + @ColumnName + ' removed from ' + @TableName + ' table'
End
Else
BEGIN
Raiserror('ERROR : Column %s does not exist in %s Table',16,1,
@ColumnName, @TableName)
Rollback Tran
Return(@@Error)
END
END
ELSE
BEGIN
Raiserror('ERROR : Table %s does not Exist',16,1, @TableName)
Rollback Tran
Return(@@Error)
END
COMMIT TRAN
SET NOCOUNT OFF
GO
My Blog:
http://dineshasanka.spaces.live.com/ -
Thanks so much !!
I can extract exactly what I need from that.
Doug
-
I wrote this to solve the same problem. Mine is not as thorough since it doesnt drop foriegn keys, which I think would be a good thing. I opted not to drop indexes because our indexes involve more than one field generally, and I like the error message that I cannot drop a field when its involved in a multifield indexs so I get reminded to address that issue. This could be changed to drop indexes and primary keys that are only on the one field. I think you could use the function index_col() to see if there are more than one column in the index.
I didnt use any cursors to do this and all the SQL gets executed in one query. Not that performance on this is a big deal...
Here is a sample:
I Added this column to my Bank Table:
ALTER TABLE Bank ADD cUsed INT NOT NULL DEFAULT (0)
CREATE STATISTICS Statistics_CUsed on Bank (cUsed)
CREATE STATISTICS Statistics_CUsed1 on Bank (cUsed)
ALTER TABLE Bank ADD CONSTRAINT Woooo1 CHECK (cUsed < 10000)
Then I executed this statement:
EXEC DropColumn 'Bank','cUsed'
Which generated and executed these statements:
DROP STATISTICS Bank.Statistics_CUsed
DROP STATISTICS Bank.Statistics_CUsed1
ALTER TABLE Bank DROP CONSTRAINT DF__bank__cUsed__07D70320, CONSTRAINT Woooo1
ALTER TABLE Bank DROP COLUMN cUsed
---------------------------------------------------------------------------------
-- This procedure drops the named column from a table. It also drops all constraints, defaults
-- and statistics tied to the column so it drops without complaining.
-- Does not drop indexes since they may involve other fields and this may cause undesired side effects.
---------------------------------------------------------------------------------
CREATE PROCEDURE DropColumn (
@Stable VARCHAR(100)
, @sColumn VARCHAR(100)
) AS BEGIN
DECLARE @sSQL VARCHAR(4000)
DECLARE @nsSQL NVARCHAR(4000)
IF EXISTS ( -- see if the column exists
SELECT c.name
FROM SysColumns c
INNER JOIN SysObjects t ON c.id = t.id
WHERE c.Name = @sColumn
AND t.name = @Stable
) BEGIN -- column exists
-- Add SQL to drop any statistics
SELECT @sSQL = ISNULL(@sSQL + CHAR(13), '') + 'DROP STATISTICS ' + t.name + '.' + i.name
FROM SysIndexes i
INNER JOIN SysObjects t ON i.id=t.id
WHERE (i.status & 64) <> 0 -- bit 64 is for a statistic
AND index_col(t.name,indid, 1) = @sColumn
AND t.name = @Stable
IF NOT @sSQL IS NULL
SET @nsSQL = CAST(@sSQL AS NVARCHAR(4000))
-- Add SQL to drop default and check constraints
SET @sSQL = NULL
SELECT @sSQL = ISNULL(@sSQL + ', ', '') + 'CONSTRAINT ' + s.name
FROM SysObjects s
INNER JOIN SysObjects t ON t.Id = s.Parent_Obj
INNER JOIN SysColumns c on s.info = c.colid AND c.id=t.id
WHERE s.xType IN('C', 'D') -- (c)heck constraint, and (d)efault constraint
AND t.name = @Stable
AND c.Name = @sColumn
IF NOT @sSQL IS NULL
SET @nsSQL = ISNULL(@nsSQL + CHAR(13),N'') + N'ALTER TABLE ' + CAST(@sTable AS NVARCHAR(100)) + N' DROP ' + CAST(@sSQL AS NVARCHAR(4000))
-- Add SQL to Drop the column
SET @nsSQL = ISNULL(@nsSQL + CHAR(13),N'') + N'ALTER TABLE ' + CAST(@sTable AS NVARCHAR(100)) + N' DROP COLUMN ' + CAST(@sColumn AS NVARCHAR(100))
--PRINT @nsSQL
--Finally execute everything in one clean shot
EXEC sp_executesql @nsSQL
END
END
- John
-
Thanks to everyone for that. In fact my requirement is much simpler since no one can access the database directly and can only use the routines available to them in the interface program ColdFusion. So I know that this table must exist, that the column is not the Primary Key and that it has no other constraints than the default.
In fact it seems to me that this is a point on which the makers could improve SQL Server.
Wouldn't it be better if defaults were not considered as a constraint - they differ from any other kind of constraint - and if you want to delete a column, what is the point of preventing you because a default exists?
It can only alter the data as you enter new records and if you want to delete the column it must be true that you don't want to enter any more data in that column, so why should we have to care about the default?
If a default exists then on data entry, if no data, use default - so defaults are a conditional test on data entry, not a constraint in any sense of the word.
Doug
-
Hello All,
My query is little different, I need to check the default value on the column using the System tables. How do i do that ??
I gotta know all about identifying PK,FK and defaults from this mail...but i need to know the value of the default.
Thanks
-
chetan - you should've really started another thread for this since it is easily missed when embedded in someone else's post...
Anyway, here's the query to get default values...
select column_name, column_default from information_schema.columns
where table_name in ('tbl1', 'tbl2', 'tbl3') and
is_nullable = 'No'
**ASCII stupid question, get a stupid ANSI !!!**
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply