SQLServerCentral / Discuss content posted by Andy Brown / Article Discussions / Article Discussions by Author / Drop a Column and its Constraints and Indexes / Latest Posts

RE: Drop a Column and its Constraints and Indexes

aoa-1120895 — Fri, 26 Jun 2009 15:04:18 GMT

The code in the article did not work for me, I had to rewrite the stored procedure, the below worksCREATE PROCEDUREDropColumnConstraints@TableName varchar(256),@ColumnName varchar(256)ASBEGINdeclare @sql_value nvarchar(4000); declare @obj_name varchar(256); declare name_cursor cursor for select objs.name from dbo.sysconstraints const inner join dbo.syscolumns cols on const.colid = cols.colid and const.id = cols.id inner join dbo.sysobjects objs on objs.id = const.constid where cols.id = OBJECT_ID(@TableName) and cols.name = @ColumnName OPEN name_cursor FETCH NEXT FROM name_cursor INTO @obj_name WHILE @@FETCH_STATUS = 0 BEGIN set @sql_value = ('alter table ' + @TableName +' drop constraint '+@obj_name);print 'Dropping '+@obj_name;execute sp_executesql @sql_value; FETCH NEXT FROM name_cursor INTO @obj_name; END CLOSE name_cursor DEALLOCATE name_cursorendgo

RE: Drop a Column and its Constraints and Indexes

Lau-765114 — Thu, 28 Aug 2008 14:57:11 GMT

Thank you, Milvards, thank you, Chiragkhabaria ;)I got to the same conclusion: the script works fine for both SQL2000 and 2005 if you apply the change mentioned above by Milvards.The only case I found when it doesn't work is when the specified column is part of PK but I guess this is something we could live with.Thanks again to all for this, especially to Andy.

RE: Drop a Column and its Constraints and Indexes

Lau-765114 — Thu, 28 Aug 2008 08:59:27 GMT

Thanks, man, I hope it works (for both 2000 and 2005). I'll give it a try today.(I really don't care how it looks as long as it does the job properly).

RE: Drop a Column and its Constraints and Indexes

milvards — Thu, 28 Aug 2008 08:50:16 GMT

Hi Lau.I made it work at last using some code I found on the internet (credit to a chap from India called [b]chiragkhabaria[/b]). Replace the code for [b]cur_constraints[/b] with this:DECLARE cur_constraints CURSOR FOR SELECT Cons.XType, Cons.[Name] FROM SysObjects AS Cons INNER JOIN (Select [Name],[ID] From SysObjects Where XType = 'U) AS Tab ON Tab.[ID] = Cons.[Parent_Obj] INNER JOIN sysconstraints ON sysconstraints.Constid = Cons.[ID] INNER JOIN SysColumns Col ON Col.[ColID] = sysconstraints.[ColID] AND Col.[ID] = Tab.[ID] WHERE (Tab.[Name] = @table) AND (Col.[Name] = @column)It can probably be written in a better way but it looks like it does the job.M

RE: Drop a Column and its Constraints and Indexes

Lau-765114 — Thu, 28 Aug 2008 07:21:13 GMT

Milvards, you are right, the script doesn't work for SQL2005 and I need it, too, for both 2000 and 2005 versions.I had a couple of attempts on this but failed. I'm planning to give it one more try (hopefully, sooner than later) and, if I'll succeed, I'll post an updated script on this thread for your and everybody's use.

RE: Drop a Column and its Constraints and Indexes

milvards — Thu, 28 Aug 2008 02:54:01 GMT

Can anyone help on this.I try to run this on SQL Server 2005 and it don't work. Even after lau's changes.The code for the cursor [b]cur_constraints[/b] does not return anything. I don't have enough knowledge two find out whats wrong, but by running the select on it's own I can see that the [b]Cons.info[/b] always contains 0 (zero). Without the [b]AND Cons.info = Cols.colid[/b], it returns all constraint rows for the table.Does anyone out there have the skills to tell me how to fix this.Thank you.

RE: Drop a Column and its Constraints and Indexes

Lau-765114 — Fri, 30 May 2008 14:59:37 GMT

Andy's script is great but not so clean. I ran it against a SQL2000 database trying to drop a column along with all its constraints/indexes. Here are the issues I faced:1. The criteria of the constraints cursor should be changed to [code]WHERE [b]((OBJECTPROPERTY(Cons.[id],'IsConstraint') = 1 OR (OBJECTPROPERTY(Cons.[id],'IsPrimaryKey') = 1)) AND Cons.info = Cols.colid)[/b] AND Cons.parent_obj = @tableID[/code]as you don’t want to drop the primary key if the given column is not part of it.2. The join criteria for the sysindexes table in the indexes cursor should include a fourth line as follows: [code]INNER JOIN dbo.sysindexkeys SIK WITH (NOLOCK) ON SIK.id = SC.id AND SIK.colid = SC.colid [b]AND SIK.indid = SI.indid[/b][/code]otherwise the cursor will return duplicate rows.3. The second line of the where criteria of the indexes cursor must be changed as follows: [code]WHERE SI.indid !=0 [b]AND ISNULL(OBJECTPROPERTY(OBJECT_ID(SI.name),'IsConstraint'), 0) = 0[/b] AND SC.id = @tableID AND SC.name = @column[/code]as many (valid) rows return a NULL value there4. The statement that drops the index should be: [code][b]SELECT @sql = N'DROP INDEX '+@table+'.'+@index [/b][/code]5. The commit/rollback block should be: [code][b]IF @@TRANCOUNT > 0 BEGIN IF @rollback = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION END[/b][/code]as you want to commit/rollback only if a transaction has been started.These are all the things I had to change in order to have the script running properly, according to my testing. I hope this helps others, too, enjoying Andy’s great script !

RE: Drop a Column and its Constraints and Indexes

Tech_Newbie — Tue, 20 May 2008 09:37:36 GMT

PLEASE HELP: I AM TRYING TO USE THE FOLLOWING SCRIPT TO :1. FOR EACH TABLE IN A GIVEN DATABASE (I am using a cursor for this)2. FIND IF A COLUMN OF NAME "row_id" exists3. IF it does, delete it 4. if it doesnt, proceed to next tableRight now I am getting error messages when it comes to a table where the column does not exist, it simply stops because the drop statement fails - what am I doing wrong ???????????????????????????code:--ALTER TABLES SCRIPT--change the line marked *** to suit your update needs....IF EXISTS (SELECT * FROM sysobjects o WITH(NOLOCK), syscolumns b WITH(NOLOCK) WHERE o.[id] = b.[id] AND b.name = 'row_id' AND O.name = ''+o.name)BEGINDECLARE tableNamesCursor CURSORFOR --*** --select 'ALTER TABLE ['+ o.name+ '] ADD row_id as '+o.name+'+'ID' -- CONSTRAINT AddDateDflt'+ cast(o.id as varchar(10))+ ' DEFAULT getdate() WITH VALUES' -- removes the TimeStamp column, and constraint from all tables select 'ALTER TABLE ['+ o.name+ '] DROP COLUMN row_id' from sysobjects o, sysindexes i where i.id = o.id and indid in(0,1) and o.name not like 'sys%' and o.name not like 'dt%' order by o.nameOPEN tableNamesCursorDECLARE @alterTableSql nvarchar(400)FETCH NEXT FROM tableNamesCursor INTO @alterTableSqlWHILE (@@FETCH_STATUS <> -1)BEGIN IF (@@FETCH_STATUS <> -2) BEGIN print @alterTableSql exec sp_executesql @alterTableSql END FETCH NEXT FROM tableNamesCursor INTO @alterTableSqlENDCLOSE tableNamesCursorDEALLOCATE tableNamesCursorEND

Drop a Column and its Constraints and Indexes

Andy B. — Sat, 29 Sep 2007 14:49:15 GMT

Comments posted to this topic are about the item [B]Drop a Column and its Constraints and Indexes[/B]