How to Drop a Column with Object Dependencies in SQL Server 2008

How do I drop a column with object dependencies in SQL Server 2008?

I could solve the problem now. I'm sorry, I did not copy the full error message, which was:

Msg 5074, Level 16, State 1, Line 1

The object 'defEmptyString' is dependent on column 'fkKeywordRolleKontakt'.

Msg 5074, Level 16, State 1, Line 1
The object
'FK_tlkpRolleKontakt_tlkpKeyword' is dependent on column
'fkKeywordRolleKontakt'.
Msg 4922, Level 16, State 9, Line 1 ALTER TABLE DROP COLUMN fkKeywordRolleKontakt failed because one or
more objects access this column.

I could generate a script to drop the column by right-clicking on the column entry (dbo.tlkpRolleKontakt > Columns > fkKeywordRolleKontakt) (in MSSQL Server Manager), selecting Modify and deleting the column. Then Table Designer > Generate Change Script generated the necessary commands:

ALTER TABLE dbo.tlkpRolleKontakt
    DROP CONSTRAINT FK_tlkpRolleKontakt_tlkpKeyword
EXECUTE sp_unbindefault N'dbo.tlkpRolleKontakt.fkKeywordRolleKontakt'
ALTER TABLE dbo.tlkpRolleKontakt
    DROP COLUMN fkKeywordRolleKontakt

Drop column and all dependent objects using data definition language

Try this code:

Declare @TABLENAME varchar(max), @COLUMN varchar(max)
SET @TABLENAME = 'YOURTableName'
SET @COLUMN = 'YOURColumnName'
Declare @CONSTRAINT varchar(max)
                    set @CONSTRAINT ='ALTER TABLE '+@TABLENAME+' DROP CONSTRAINT '
                    set @CONSTRAINT = @CONSTRAINT + (select SYS_OBJ.name as CONSTRAINT_NAME
                    from sysobjects SYS_OBJ
                    join syscomments SYS_COM on SYS_OBJ.id = SYS_COM.id
                    join sysobjects SYS_OBJx on SYS_OBJ.parent_obj = SYS_OBJx.id 
                    join sysconstraints SYS_CON on SYS_OBJ.id = SYS_CON.constid
                    join syscolumns SYS_COL on SYS_OBJx.id = SYS_COL.id
                    and SYS_CON.colid = SYS_COL.colid
                    where
                    SYS_OBJ.uid = user_id() and SYS_OBJ.xtype = 'D'
                    and SYS_OBJx.name=@TABLENAME and SYS_COL.name=@COLUMN)
                    exec(@CONSTRAINT)

and then run your regular alter table:

ALTER TABLE YOURTABLENAME
DROP COLUMN YOURCOLUMNNAME

With the first code you remove all the dependencies on that column and then you can remove it without problems.

EDIT - Removing Default Values Constraints:

The code above does not seems to remove DEFAULT_CONSTRAINTS so, in that case you must also use:

DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__') AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns WHERE NAME = N'__ColumnName__' AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
    EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

Delete a SQL Table Column give Constraint dependency error

You can use some dynamic SQL to drop the default. If it's an isolated script to just drop the column, then it's easier, something like:

DECLARE @sqlDF NVARCHAR(MAX);
SELECT @sqlDF = 'ALTER TABLE {$tableName} DROP CONSTRAINT ' + QUOTENAME(OBJECT_NAME([default_object_id])) + ';'
FROM sys.columns
WHERE [object_id] = OBJECT_ID('{$tableName}') AND [name] in ({$columns}) AND [default_object_id] <> 0;

IF @sqlDF IS NOT NULL
    EXEC(@sqlDF);

If you are working with a migrations tool, maybe you're gonna have to refactor this, so it doesn't try to redeclare the @sqlDF variable.

Dropping a table with all its dependencies (Microsoft SQL Server)

The best thing to do it is "Generate scripts for Drop"

Select Database -> Right Click -> Tasks -> Generate Scripts - will open wizard for generating scripts

Select the database -> next
Set option 'Script to create' to true (want to create)
Set option 'Script to Drop' to true (want to drop)
Set option 'Generate script for dependent object' to true -> Next
Select the Check box to select objects wish to create script
Select the choice to write script (File, New window, Clipboard)

Execute the script

This way we can customize our script i.e., we can do scripting for selected objects of a database.

I hope this will help you!

Best Wishes, JP

ALTER TABLE on dependent column

I believe that you will have to drop the foreign key constraints first. Then update all of the appropriate tables and remap them as they were.

ALTER TABLE [dbo.Details_tbl] DROP CONSTRAINT [FK_Details_tbl_User_tbl];
-- Perform more appropriate alters
ALTER TABLE [dbo.Details_tbl] ADD FOREIGN KEY (FK_Details_tbl_User_tbl) 
    REFERENCES User_tbl(appId);
-- Perform all appropriate alters to bring the key constraints back

However, unless memory is a really big issue, I would keep the identity as an INT. Unless you are 100% positive that your keys will never grow past the TINYINT restraints. Just a word of caution :)

How to change datatype whithout dropping dependencies

We found a way to do this. Although it probably isn't the best solution, it worked for us so if anyone has the same problem, try the following:

In SQL Server Management Studio go to Tools -> Options -> Designer and uncheck the box of "Prevent saving changes that require table re-creation"

Next rightclick the table you want to modify the column datatypes and click on "Design".

In the designer, edit the column datatype to the one you need.

Finally, right-click and choose "Generate Change Script".

What it does is the following:

Drop the constraints on the table
Create a temp table with the new datatype of the column
Readd constraints to the temp table
Set the IDENTITY_INSERT on the temp table to ON
Copy all data from the old table to the new temp table
Set the IDENTITY_INSERT on the temp table to OFF
Drop the old table
Rename the temp table to the name of the old table
Readd the primary key constraint
Recreate the indexes
Readd the foreign key constraints

Additionally you have to refresh all depending views.
You can generate the statements with this script:

SELECT DISTINCT 'EXEC sp_refreshview ''' + s.name + '.' + so.name + '''' AS 'dbo.TABLENAME'
FROM sys.objects AS so 
INNER JOIN sys.sql_expression_dependencies AS sed 
    ON so.object_id = sed.referencing_id 
INNER JOIN sys.schemas AS s
    ON so.schema_id = s.schema_id
WHERE so.type = 'V' AND sed.referenced_id = OBJECT_ID('dbo.TABLENAME');

Check the box of "Prevent saving changes that require table re-creation" in Tools -> Options -> Designer

Please be careful about this! See if it really does what you are looking for. Keep in mind that this drops the old table. Test this in a development environment!

How to drop column with constraint?

First you should drop the problematic DEFAULT constraint, after that you can drop the column

alter table tbloffers drop constraint [ConstraintName]
go

alter table tbloffers drop column checkin

But the error may appear from other reasons - for example the user defined function or view with SCHEMABINDING option set for them.

UPD:
Completely automated dropping of constraints script:

DECLARE @sql NVARCHAR(MAX)
WHILE 1=1
BEGIN
    SELECT TOP 1 @sql = N'alter table tbloffers drop constraint ['+dc.NAME+N']'
    from sys.default_constraints dc
    JOIN sys.columns c
        ON c.default_object_id = dc.object_id
    WHERE 
        dc.parent_object_id = OBJECT_ID('tbloffers')
    AND c.name = N'checkin'
    IF @@ROWCOUNT = 0 BREAK
    EXEC (@sql)
END

SQL Server 2008 R2 Add column into a specific location

You cannot. Column are always added at the end of the column list. The only way to change the order is to recreate the table from scratch.

That being said, it should never ever matter to you what is the actual physical order of the columns, nor the logical order of column definitions. If you have dependencies on column order, your code is broken. If you expect performance gains from column order, those are myths.