IF exissrs (select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_deletefield]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_deletefield] Go
/ * - Delete all the relationships of a field and this field
When you delete a field, often because the constraint / foreign key / default value is defined on this field, this storage is the relationship between the deletion of the specified fields in the table.
Fixed an error in the deletion processing issued by 2004.05, and the original processing statement is a universal stored procedure.
Zou Jian 2004.07 - * /
/ * - Call the sample exec p_deletefield 'table name,' field name '- * / create proc p_deletefield @ tbname sysname, - To process the table name @fdname sysname, - To process the field name @delfield bit = 1 --0 Only delete relationship, 1 simultaneously delete field asDeclare hcForeCh Cursor Global for - Default Constrained Select SQL = 'ALTER TABLE [' B.Name '] Drop Constraint [' D.Name 'from syscolumns a Join sysobjects b on a.id = B.ID and a.name=@fdname and b.name=@tbname join syscomments c on a.cdefault = c.id Join Sysobjects D on C.ID = D.IDUNION - outside Key Quote Select S = 'ALTER TABLE [' C.Name '] Drop connectiont [' b.name ']' from sysforeignkeys a join sysobjects b on b.id = a.constid Join Sysobjects c on C.ID = A .fkeyid join syscolumns d on d.id = C.ID and a.fkey = d.colid and d.name=@fdname join sysobjects e on E.ID = a.rkeyid and e.name=@tbname join syscolumns f on F.ID = E.ID and A.RKEY = F.Colid Union - Programs / Unique Key / Index Select Case WHEN E. type in ('PK', 'UQ') Then 'ALTER TABLE [' C.Name 'Else' DROP INDEX [' C.Name '] 'Else' Drop Index [' C.Name '] 'endfrom sysindexes a join sysindexkeys b on A.ID = B.ID and a.indid = B.indid Join Sysobjects c on b. ID = C.ID and c.XTYPE = 'u' and c.name=@tbname join syscolumns d on b.id = D.ID and b.colid = D.Colid and D.Name = @ FDNAME LEFT JOIN SYSOBJECTS E ON E.ID = Object_ID (A.NAME) Where A.indid Not in (0,255) EXEC SP_MSFOREACH_WORKER '?'? '
IF @ delfield = 1 EXEC ('alter table [' @ TBNAME DROP COLUMN [' @ fdname '] ") GO
