如何将字段XINGMING所在的表中该字段的长度同意修改为20

[crazy_boom]

数据库中有近100个表 其中都有xingming 这个字段 现在这个字段类型是 char(10)
现在要统一把100个表中的该字段都修改为varchar(20) 这样的类型如何一次性修改完成那
其中的一些表 这个字段存在索引字段

[chuanzhang5687]

alter table tableName alter column columnName varchar(20)

[--小F--]

SELECT
    'alter table '+a.name+' alter columns '+b.name+'varchar(20)'
FROM
    sys.objects AS a
INNER JOIN sys.columns AS b ON a.object_id=b.object_id
WHERE
    b.name='xingming' AND a.type='u'

把得到的结果到查询分析器 执行。

[crazy_boom]

引用 2 楼 fredrickhu 的回复:

SELECT
    'alter table '+a.name+' alter columns '+b.name+'varchar(20)'
FROM
    sys.objects AS a
INNER JOIN sys.columns AS b ON a.object_id=b.object_id
WHERE
    b.name='xingming' AND a.type='u'

把得到的结果到查询分析器 执行。

用这种方法 打印出来的结果 执行当这个字段有索引或为主键的时候 就执行不过去了。这如何处理i啊

[shinger126]

SELECT 'alter table '+a.name+' alter columns '+b.name+'varchar(20)' FROM sys.objects AS a INNER JOIN sys.columns AS b ON a.object_id=b.object_id WHERE b.name='xingming' AND a.type='u' and exists(select 1 from sys.indexes c,sys.index_columns d where c.object_id=d.object_id and c.object_id=a.object_id and d.column_id=b.column_id)

[shinger126]

引用 4 楼 shinger126 的回复:

SELECT 'alter table '+a.name+' alter columns '+b.name+'varchar(20)' FROM sys.objects AS a INNER JOIN sys.columns AS b ON a.object_id=b.object_id WHERE b.name='xingming' AND a.type='u' and exists(select 1 from sys.indexes c,sys.index_columns d where c.object_id=d.object_id and c.object_id=a.object_id and d.column_id=b.column_id)

错了，应该是not exists

[crazy_boom]

这样操作的结果就是 没有索引的 表会更新 有索引的表就不更新了是吗？ 第一 这个字段在所有表都有索引 第二 这个字段绝大多数表中还是 主键

[crazy_boom]

通过循环可以实现吧 如果分少 可以加分！

[發糞塗牆]

你这个要考虑的东西太多了，约束、触发器、外键、主键、索引、默认值等。不好写

[crazy_boom]

明白了 其实只要将该字段的 主键 drop 掉 然后再 alter column xingming 然后再将主键增加上即可 实现俺的要求 我估计需要用游标可以实现这个问题 有提供具体方案的吗

[發糞塗牆]

维护性质的功能用游标很合适，不过我暂时没现成脚本

[crazy_boom]

我写的游标 执行时有好多错误

create proc ch_zdsx  @col varchar(10)
as
begin
   Create Table #index([index_name] sysname,[index_description] varchar(255),[index_keys] varchar(255))

    declare @tb varchar(100)
    Declare MyCur Cursor Local Read_Only Fast_Forward For  
       select a.name  from sysobjects a,syscolumns b where a.id=b.id and b.name=@col and a.xtype='u'  
    Open  MyCur  
      Fetch Next From MyCur Into @tb  
      While @@Fetch_Status = 0   
        Begin  
           declare @index varchar(50)

           insert #index(index_name,index_description,index_keys) exec sp_helpindex @tb

           select @index=index_name from #index a join sysobjects b on a.index_name=b.name and b.xtype='PK'
    
           if isnull(@index,'')<>'' 
            begin
               exec ('alter table '+ @tb + 'drop CONSTRAINT ' + @index )
            end

            exec ('alter table '+ @tb+' add ' + @col + ' varchar(14) default('''')  not null')

           if isnull(@index,'')<>''
            begin            
             exec ('alter table '+ @tb + ' add CONSTRAINT ' + @index + ' PRIMARY KEY (xlh)' )
            end
           Delete from #index

           Fetch Next From MyCur Into @tb  
      End  
   Close MyCur  
  Deallocate MyCur  

end

exec   ch_zdsx   'xlh' 
--报错如下：
/*------------------------------------------------------------
服务器: 消息 156，级别 15，状态 1，行 1
在关键字 'CONSTRAINT' 附近有语法错误。

已在表 't_ffdgh' 上定义了主键。
服务器: 消息 1750，级别 16，状态 1，行 1
未能创建约束。请参阅前面的错误信息。

服务器: 消息 2705，级别 16，状态 4，行 1
各表中的列名必须唯一。在表 'thxlf' 中多次指定了列名 'xlh'。

（所影响的行数为 0 行）

（所影响的行数为 1 行）

服务器: 消息 2705，级别 16，状态 4，行 1
各表中的列名必须唯一。在表 'thxfl' 中多次指定了列名 'xlh'。
*/

提示好多这样的错误 有谁能帮我修改一下这个 游标吗

[發糞塗牆]

你先print出来看看结果嘛

[crazy_boom]

print 结果后发现了个个问题 处理后 仍然提示这个错误

服务器: 消息 2705，级别 16，状态 4，行 1各表中的列名必须唯一。在表 'thxlf' 中多次指定了列名 'xlh'。 （所影响的行数为 0 行） （所影响的行数为 1 行） 服务器: 消息 2705，级别 16，状态 4，行 1各表中的列名必须唯一。在表 'thxfl' 中多次指定了列名 'xlh'。

[crazy_boom]

如何print ?

[發糞塗牆]

回复引用 create proc ch_zdsx @col varchar(10) as begin Create Table #index([index_name] sysname,[index_description] varchar(255),[index_keys] varchar(255)) declare @tb varchar(100) Declare MyCur Cursor Local Read_Only Fast_Forward For select a.name from sysobjects a,syscolumns b where a.id=b.id and b.name=@col and a.xtype='u' Open MyCur Fetch Next From MyCur Into @tb While @@Fetch_Status = 0 Begin declare @index varchar(50) insert #index(index_name,index_description,index_keys) exec sp_helpindex @tb select @index=index_name from #index a join sysobjects b on a.index_name=b.name and b.xtype='PK' if isnull(@index,'')<>'' begin exec ('alter table '+ @tb + 'drop CONSTRAINT ' + @index ) end exec ('alter table '+ @tb+' add ' + @col + ' varchar(14) default('''') not null') if isnull(@index,'')<>'' begin exec ('alter table '+ @tb + ' add CONSTRAINT ' + @index + ' PRIMARY KEY (xlh)' ) --这里的EXEC 改成print end Delete from #index Fetch Next From MyCur Into @tb End Close MyCur Deallocate MyCur end

[yoan2014]

引用 15 楼 crazy_boom 的回复:

print 结果后发现了个个问题 处理后 仍然提示这个错误

服务器: 消息 2705，级别 16，状态 4，行 1各表中的列名必须唯一。在表 'thxlf' 中多次指定了列名 'xlh'。 （所影响的行数为 0 行） （所影响的行数为 1 行） 服务器: 消息 2705，级别 16，状态 4，行 1各表中的列名必须唯一。在表 'thxfl' 中多次指定了列名 'xlh'。

你是要修改字段的类型为什么要用alter table tablename add columnname varchar(14) 不是应该为

alter table tablename alter column columnname varchar(14)

吗？

[crazy_boom]

谢谢 yoan2014 修改ok了

            exec ('alter table '+ @tb+' alter column ' + @col + ' varchar(14) not null')  

但是执行的仍然报 服务器: 消息 5074，级别 16，状态 8，行 1 索引 'index_abc' 依赖于 列 'xlh'。 服务器: 消息 4922，级别 16，状态 1，行 1 ALTER TABLE ALTER COLUMN xlh 失败，因为有一个或多个对象访问此列。 我差 跟原来执行打印出来的 语句一样 索引没有被删除 先将索引备份 然后 索引能够删除 alter 字段属性 后 再将索引自动恢复回来 这是我想要的结果 表太多了 手动一个一个表改 太麻烦了。麻烦各位帮忙看看