SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Drop a Column and its Constraints and Indexes


Drop a Column and its Constraints and Indexes

Author
Message
Andy B.
Andy B.
SSC-Enthusiastic
SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)SSC-Enthusiastic (190 reputation)

Group: General Forum Members
Points: 190 Visits: 67
Comments posted to this topic are about the item Drop a Column and its Constraints and Indexes


Enjoy!

A Brown

Manage all of your backups in one simple job using this script.
Tech_Newbie
Tech_Newbie
SSC Rookie
SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)SSC Rookie (37 reputation)

Group: General Forum Members
Points: 37 Visits: 119
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" exists
3. IF it does, delete it
4. if it doesnt, proceed to next table

Right 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)
BEGIN
DECLARE tableNamesCursor CURSOR
FOR
--***
--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.name


OPEN tableNamesCursor
DECLARE @alterTableSql nvarchar(400)
FETCH NEXT FROM tableNamesCursor INTO @alterTableSql
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
print @alterTableSql
exec sp_executesql @alterTableSql
END
FETCH NEXT FROM tableNamesCursor INTO @alterTableSql
END
CLOSE tableNamesCursor
DEALLOCATE tableNamesCursor
END
Lau-765114
Lau-765114
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 24
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
WHERE ((OBJECTPROPERTY(Cons.[id],'IsConstraint') = 1
OR (OBJECTPROPERTY(Cons.[id],'IsPrimaryKey') = 1))
AND Cons.info = Cols.colid)

AND Cons.parent_obj = @tableID


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:
INNER JOIN dbo.sysindexkeys SIK WITH (NOLOCK)
ON SIK.id = SC.id
AND SIK.colid = SC.colid
AND SIK.indid = SI.indid


otherwise the cursor will return duplicate rows.

3. The second line of the where criteria of the indexes cursor must be changed as follows:
WHERE SI.indid !=0
AND ISNULL(OBJECTPROPERTY(OBJECT_ID(SI.name),'IsConstraint'), 0) = 0
AND SC.id = @tableID
AND SC.name = @column


as many (valid) rows return a NULL value there

4. The statement that drops the index should be:
SELECT @sql = N'DROP INDEX '+@table+'.'+@index 



5. The commit/rollback block should be:
IF @@TRANCOUNT > 0
BEGIN
IF @rollback = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END


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 !
milvards
milvards
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 8
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 cur_constraints 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 Cons.info always contains 0 (zero). Without the AND Cons.info = Cols.colid, it returns all constraint rows for the table.

Does anyone out there have the skills to tell me how to fix this.

Thank you.
Lau-765114
Lau-765114
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 24
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.
milvards
milvards
Grasshopper
Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)Grasshopper (22 reputation)

Group: General Forum Members
Points: 22 Visits: 8
Hi Lau.

I made it work at last using some code I found on the internet (credit to a chap from India called chiragkhabaria).

Replace the code for cur_constraints 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
Lau-765114
Lau-765114
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 24
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).
Lau-765114
Lau-765114
SSC Rookie
SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)SSC Rookie (39 reputation)

Group: General Forum Members
Points: 39 Visits: 24
Thank you, Milvards, thank you, Chiragkhabaria Wink

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.
aoa-1120895
aoa-1120895
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 1
The code in the article did not work for me, I had to rewrite the stored procedure, the below works


CREATE PROCEDURE
DropColumnConstraints
@TableName varchar(256),
@ColumnName varchar(256)
AS
BEGIN

declare @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_cursor
end


go
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search