![]() This could be changed to drop indexes and primary keys that are only on the one field. 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. Mine is not as thorough since it doesnt drop foriegn keys, which I think would be a good thing. Raiserror('ERROR : Column %s does not exist in %s : Table %s does not Exist',16,1, TRAN Raiserror('ERROR : Failed to Drop column %s from %s 'Column ' + + ' removed from ' + + ' table' SET = 'ALTER TABLE ' + + ' DROP COLUMN ' + 0 Raiserror('ERROR : Failed to Drop Index %s from %s 'Index ' + + ' removed from ' + + ' table'įETCH NEXT FROM IX_Cursor INTO IX_cursor IF Exists(SELECT * FROM sysobjects WHERE name = 'ALTER TABLE ' + + ' DROP Constraint ' = 'DROP INDEX ' + + '.' + 0 If the Index Name is there in sysobjects then it's a Unique Constraint Print 'Dropping Index ' + + ' from ' + + ' Table' WHERE sysobjects.name = syscolumns.name = IX_Cursor WHERE sysobjects.name = syscolumns.name = IX_Cursor CURSOR FOR Check to see if the column has a Unique Index defined on it.įROM syscolumns INNER JOIN sysobjectsON syscolumns.id =ĪND sysindexkeys.indid = sysindexes.indid Raiserror('ERROR : Failed to Drop constraint %s From %s 'PK ' + + ' Dropped from ' + + ' Table.' SET = 'ALTER TABLE ' + + ' DROP Constraint ' 'Dropping FK constraint ' + 0įETCH NEXT FROM PKFK_Cursor INTO WHILE = 0*/ĪND syscolumns.name = 'ALTER TABLE ' + + ' DROP Constraint ' + 'Dropping PK constraint ' + 0 WHERE syscolumns.name = AND sysobjects.name = PKFK_Cursor INNER join sysobjects ON sysreferences.rkeyid = sysobjects.id WHERE syscolumns.name = sysobjects.name = PKFK_Cursor CURSOR FOR This even takes care of Unique Constraints defined on the column.ĪND sysindexes.indid = sysindexkeys.indidĪND lid = lidĪND syscolumns.name = Check to see if the PK is being Referenced by a FK Check to see if the column has a PK constraint defined on it. Raiserror('ERROR : Failed to Drop constraint %s From %s 'FK ' + + ' Dropped from ' + + ' Table.'įETCH NEXT FROM FK_Cursor INTO WHILE = 0*/ SET = 'ALTER TABLE ' + + ' DROP Constraint ' + 'Dropping FK constraint ' + 0 WHERE sysobjects.name = AND syscolumns.name = FK_Cursor INNER JOIN sysobjects so ON nstid = so.id INNER JOIN sysreferences ON syscolumns.id = sysreferences.fkeyid SELECT so.name FROM syscolumns INNER JOIN sysobjects ON syscolumns.id = WHERE sysobjects.name = syscolumns.name = FK_Cursor CURSOR FOR IF Exists (SELECT so.name FROM syscolumns INNER JOIN sysobjects ![]() Check to see if the column has a FK constraint defined on it. Raiserror('ERROR : Failed to Drop constraint %s From %s 'Default ' + + ' Dropped from ' + + ' Table.' SET = 'ALTER TABLE ' + + ' DROP Constraint ' + 'Dropping Default constraint ' + 0 INNER JOIN sysobjects so ON syscolumns.cdefault = so.id ![]() WHERE sysobjects.name = syscolumns.name = syscolumns.cdefault 0įROM syscolumns INNER JOIN sysobjects ON syscolumns.id = sysobjects.id IF Exists (SELECT syscolumns.* FROM syscolumns INNER JOIN sysobjects It cannot beĭropped',16,1, to see if the column has a Default constraint defined on it. Raiserror('ERROR : %s is the only column in the table %s. ![]() IF (SELECT Count(*) FROM syscolumns sc INNER JOIN sysobjects so Check to see if the column count in the table is more than 1. Check to see that the column already exist If exists (select * from sysobjects where id = and NOTE : Only Table Owner or a dbo can Alter the Table. ![]() CREATE PROCEDURE usp_DropcolumnTYPE: stored procedure (SQL 7)CREATION DATE: 08/01/01Įxec usp_DropColumn To Drop a Column from a Table irrespective of the constraintsĬheck if this is the only column in the tableĬheck existence of Default constraint on the columnĬheck existence of Multiple Foreign Key constraints on the columnĬheck existence of a Primary Key constraint on the columnĬheck existence of Multiple Unique Constraint/Indexs on the column ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |