删除SQL表中的字段

declare @default sysname, @sql nvarchar(max)

BEGIN

    IF  EXISTS(SELECT 1 FROM SYSCOLUMNS A,SYSOBJECTS B

      WHERE A.id=B.id AND B.name='TableName' AND B.xtype='u' AND A.name='ColumnName')

            BEGIN

                  SELECT @default = name

                  from sys.default_constraints

                  WHERE parent_object_id = object_id('TableName')

                  AND type = 'D'

                  AND parent_column_id = (

                        select column_id

                        from sys.columns

                        where object_id = object_id('TableName')

                        and name = 'Accounts'

                        )

 

                  set @sql = N'alter table [TableName] drop constraint ' + @default

                  exec sp_executesql @sql

 

                  alter table [TableName] drop column [ColumnName]

            END

END

posted @ 2012-10-08 16:35  Areas  阅读(495)  评论(0编辑  收藏  举报