在查询分析器里用如下语句:
declare @mylen int,
@dropname char(100)
set @mylen=(SELECT length FROM syscolumns WHERE name = 'fwxzdh' AND id = (SELECT id FROM sysobjects WHERE name = 'tb_fwhd_wxz'))set @dropname=(select rtrim(name) as dropname from sysobjects where xtype='D' and parent_obj=(select id from sysobjects where name='tb_fwhd_wxz' and xtype='U') and id in (select cdefault from syscolumns where name='fwxzdh'))if len(@dropname)>0 and @mylen<>8
alter table tb_fwhd_wxz drop constraint @dropname
alter table tb_fwhd_wxz alter column fwxzdh char(8) not nullALTER TABLE [dbo].[tb_fwhd_wxz] WITH NOCHECK ADD CONSTRAINT [DF_tb_fwhd_wxz_fwxzdh_20070920] default(‘’) for [fwxzdh]主要是判断fwxzdh字段长度是否为8,如果不是先删除默认值限制,再修改字段长度,最后再加上默认值限制运行时出下面的错误:
服务器: 消息 170,级别 15,状态 1,行 8
第 8 行: '@dropname' 附近有语法错误。因为@dropname是变量,而直接在sql语句中不能识别变量,所以出错,请问各位如何解决非常感谢!!!
declare @mylen int,
@dropname char(100)
set @mylen=(SELECT length FROM syscolumns WHERE name = 'fwxzdh' AND id = (SELECT id FROM sysobjects WHERE name = 'tb_fwhd_wxz'))set @dropname=(select rtrim(name) as dropname from sysobjects where xtype='D' and parent_obj=(select id from sysobjects where name='tb_fwhd_wxz' and xtype='U') and id in (select cdefault from syscolumns where name='fwxzdh'))if len(@dropname)>0 and @mylen<>8
alter table tb_fwhd_wxz drop constraint @dropname
alter table tb_fwhd_wxz alter column fwxzdh char(8) not nullALTER TABLE [dbo].[tb_fwhd_wxz] WITH NOCHECK ADD CONSTRAINT [DF_tb_fwhd_wxz_fwxzdh_20070920] default(‘’) for [fwxzdh]主要是判断fwxzdh字段长度是否为8,如果不是先删除默认值限制,再修改字段长度,最后再加上默认值限制运行时出下面的错误:
服务器: 消息 170,级别 15,状态 1,行 8
第 8 行: '@dropname' 附近有语法错误。因为@dropname是变量,而直接在sql语句中不能识别变量,所以出错,请问各位如何解决非常感谢!!!
--@dropname char(100)
@dropname sysname--set @mylen=(SELECT length FROM syscolumns WHERE name = 'fwxzdh' AND id = (SELECT id FROM sysobjects WHERE name = 'tb_fwhd_wxz'))
SELECT @mylen=length FROM syscolumns WHERE name = 'fwxzdh' AND id = object_id('tb_fwhd_wxz')--这一句自己改
set @dropname=(select rtrim(name) as dropname from sysobjects where xtype='D' and parent_obj=(select id from sysobjects where name='tb_fwhd_wxz' and xtype='U') and id in (select cdefault from syscolumns where name='fwxzdh'))--if len(@dropname)>0 and @mylen<>8
if @dropname is not null and @mylen<>8
--alter table tb_fwhd_wxz drop constraint @dropname
exec ('alter table tb_fwhd_wxz drop constraint ' + @dropname)alter table tb_fwhd_wxz alter column fwxzdh char(8) not null
就是这句话有问题,删除时不能用变量
alter table tb_fwhd_wxz drop constraint @dropname
就是这句话有问题,删除时不能用变量
============================================================================
declare @sql nvarchar(2000)
select @sql = N'alter table tb_fwhd_wxz drop constraint ' + @dropname
exec sp_executesql @sql