Delete SQL database column stored procedures include relationships

SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS on
GO
Create proc Spdropcolumn
@tbname sysname,--the name of the table to be processed
@fdname sysname,--The name of the field to be processed
@delfield bit=1-0 Delete a relationship, 1 delete a field at the same time
As
SET ansi_warnings OFF
Declare @t table (SQL nvarchar (2000))
INSERT INTO @t
--Default value constraint
Select sql= ' ALTER TABLE [' +b.name+ '] drop constraint [' +d.name+ ']
From Syscolumns A
Join sysobjects B on a.id=b.id and [email protected] and [email protected]
Join syscomments C on a.cdefault=c.id
Join sysobjects D on c.id=d.id
Union--FOREIGN key reference
Select s= ' ALTER TABLE [' +c.name+ '] drop constraint [' +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 [email protected]
Join sysobjects e on [email protected]
--join syscolumns F on f.id=e.id and A.rkey=f.colid
Union--PRIMARY key/Unique key/Index
Select Case when E.xtype in (' PK ', ' UQ ') then ' ALTER TABLE [' +c.name+ '] drop constraint [' +e.name+ '] '
Else ' if exists (SELECT * from sysindexes where id=object_id (' +c.name+ ') and Name=n ' +a.name+ ' "and root <>0) d ROP index [' +c.name+ ']. [' +a.name+ '] ' end
From 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 [email protected]
Join Syscolumns D on B.id=d.id and b.colid=d.colid and [email protected]
Left join sysobjects E on e.id=object_id (a.name)
where A.indid not in (0,255)
declare @sql nvarchar (2000)
Declare p cursor for select SQL from @t
Open P
Fetch NEXT from P into @sql
while (@ @fetch_status = 0)
Begin
Execute (@sql)
Fetch NEXT from P into @sql
End
Close P
DEALLOCATE p
If @delfield =1
if exists (SELECT * from syscolumns where ID in (select id from sysobjects where [email protected]) and [email protected])
EXEC (' ALTER TABLE [' [Email protected]+ '] drop column [' [Email protected]+ '] ')
SET ansi_warnings on
GO

Delete SQL database column stored procedures include relationships