Create PROCEDURE [dbo].[proc_dropColumn]
@tablename VARCHAR (30),
@columnname VARCHAR (30)
AS
/*
功能：删除字段，同时删除约束
*/
IF NOT EXISTS
(SELECT *
FROM syscolumns a INNER JOIN sysobjects b ON a.id = b.id
WHERE b.name = @tablename AND a.name = @columnname)
RETURN 0
/*查找约束*/
DECLARE @contraint VARCHAR (50)
SET @contraint = ''
SELECT @contraint = b.name
FROM sysobjects a
INNER JOIN sysobjects b
ON a.id = b.parent_obj
INNER JOIN syscolumns c
ON b.id = c.cdefault AND c.name = @columnname
WHERE a.name = @tablename
/*执行删除*/
DECLARE @sqltext VARCHAR (200)
IF @contraint <> ''
BEGIN
SET @sqltext = ' ALTER TABLE ' + @tablename + ' DROP CONSTRAINT ' + @contraint
EXEC (@sqltext)
END
SET @sqltext = ' ALTER TABLE ' + @tablename + ' DROP COLUMN ' + @columnname
EXEC (@sqltext)
