- 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)
SQL——指定表名和列名,删除对应的列
最新推荐文章于 2022-04-10 16:51:34 发布