将另一个字段EID的数据类型由char变为varchar,且长度由15变为18
Tks!
7 个解决方案
#1
declare @sql nvarchar(4000)
set @sql=N'if exists(select 1 from syscolumns where id=object_id(?) and name= 'declare @sql varchar(4000)
declare @c varchar(100)
set @c='EmpNo'
set @sql= 'if exists(select 1 from dbo.syscolumns where id=object_id(''?'') and name='''+@c+''')
alter table ? alter column '+ @c +' varchar(10) not null'
exec sp_MSforeachtable @sql,'?'
字段EID的数据类型由char变为varchar,且长度由15变为18同样道理
关于sp_MSforeachtable内容 见http://www.cnblogs.com/piaoqingsong/archive/2007/06/12/780290.html
set @sql=N'if exists(select 1 from syscolumns where id=object_id(?) and name= 'declare @sql varchar(4000)
declare @c varchar(100)
set @c='EmpNo'
set @sql= 'if exists(select 1 from dbo.syscolumns where id=object_id(''?'') and name='''+@c+''')
alter table ? alter column '+ @c +' varchar(10) not null'
exec sp_MSforeachtable @sql,'?'
字段EID的数据类型由char变为varchar,且长度由15变为18同样道理
关于sp_MSforeachtable内容 见http://www.cnblogs.com/piaoqingsong/archive/2007/06/12/780290.html
#2
declare @sql nvarchar(4000)
declare @c varchar(100)
set @c='EmpNo'
set @sql= 'if exists(select 1 from dbo.syscolumns where id=object_id(''?'') and name='''+@c+''')
alter table ? alter column '+ @c +' varchar(10) not null'
exec sp_MSforeachtable @sql,'?'
字段EID的数据类型由char变为varchar,且长度由15变为18同样道理
关于sp_MSforeachtable内容 见http://www.cnblogs.com/piaoqingsong/archive/2007/06/12/780290.html
#3
declare @sqlstr varchar(4000),
@FieldName sysname
select @Sqlstr = ' ', @FieldName = 'f1'
SELECT @SQLStr = @Sqlstr + ' alter table ' + b.name + ' alter column '+@Fieldname + ' varchar(20) '
from syscolumns a
left join sysobjects b on a.id = b.id
where a.name = @FieldName
print(@Sqlstr)
exec(@Sqlstr)
@FieldName sysname
select @Sqlstr = ' ', @FieldName = 'f1'
SELECT @SQLStr = @Sqlstr + ' alter table ' + b.name + ' alter column '+@Fieldname + ' varchar(20) '
from syscolumns a
left join sysobjects b on a.id = b.id
where a.name = @FieldName
print(@Sqlstr)
exec(@Sqlstr)
#4
declare @name varchar(50)
declare @sql varchar(100)
declare y CURSOR
for select name from sysobjects where xtype='u' and name<>'dtproperties'
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name='EmpNo')
Begin
set @sql='alter table '+ @name+' alter column EmpNo char(10)'
exec(@sql)
End
if exists(select * from syscolumns where id=object_id(@name) and name='EID')
Begin
set @sql='alter table '+ @name+' alter EID column varchar(15)'
exec(@sql)
End
FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y
declare @sql varchar(100)
declare y CURSOR
for select name from sysobjects where xtype='u' and name<>'dtproperties'
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name='EmpNo')
Begin
set @sql='alter table '+ @name+' alter column EmpNo char(10)'
exec(@sql)
End
if exists(select * from syscolumns where id=object_id(@name) and name='EID')
Begin
set @sql='alter table '+ @name+' alter EID column varchar(15)'
exec(@sql)
End
FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y
#5
楼上的因为EMPNo为主键,所以有以下错误,请问怎么修改?
ALTER TABLE ALTER COLUMN EMPNO failed because one or more objects access this column.
ALTER TABLE ALTER COLUMN EMPNO failed because one or more objects access this column.
#6
up!
who can help me on it?
Many Thanks!
who can help me on it?
Many Thanks!
#7
declare @name varchar(50)
declare @sql varchar(100)
declare @key varchar(200)
set @key=''
declare y CURSOR
for select name from sysobjects where xtype='u' and name<>'dtproperties'
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name='EmpNo')
Begin
--查找主键
set @key =(SELECT pkey.name FROM sysobjects AS pkey INNER JOIN sysobjects ON
pkey.parent_obj = sysobjects.id
WHERE (sysobjects.name =@name) and pkey.xtype='PK')
if @key<>'' --删除主键
begin
exec('alter table '+@name+' drop constraint '+@key)
end
--修改 字段长度
set @sql='alter table '+ @name+' alter column EmpNo char(10)'
exec(@sql)
--重建主键
if @key<>''
begin
exec('alter table '+@name+' add constraint '+ @key +' PRIMARY KEY CLUSTERED (EmpNo)')
end
End
if exists(select * from syscolumns where id=object_id(@name) and name='EID')
Begin
set @sql='alter table '+ @name+' alter EID column varchar(15)'
exec(@sql)
End
FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y
declare @sql varchar(100)
declare @key varchar(200)
set @key=''
declare y CURSOR
for select name from sysobjects where xtype='u' and name<>'dtproperties'
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name='EmpNo')
Begin
--查找主键
set @key =(SELECT pkey.name FROM sysobjects AS pkey INNER JOIN sysobjects ON
pkey.parent_obj = sysobjects.id
WHERE (sysobjects.name =@name) and pkey.xtype='PK')
if @key<>'' --删除主键
begin
exec('alter table '+@name+' drop constraint '+@key)
end
--修改 字段长度
set @sql='alter table '+ @name+' alter column EmpNo char(10)'
exec(@sql)
--重建主键
if @key<>''
begin
exec('alter table '+@name+' add constraint '+ @key +' PRIMARY KEY CLUSTERED (EmpNo)')
end
End
if exists(select * from syscolumns where id=object_id(@name) and name='EID')
Begin
set @sql='alter table '+ @name+' alter EID column varchar(15)'
exec(@sql)
End
FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y
#1
declare @sql nvarchar(4000)
set @sql=N'if exists(select 1 from syscolumns where id=object_id(?) and name= 'declare @sql varchar(4000)
declare @c varchar(100)
set @c='EmpNo'
set @sql= 'if exists(select 1 from dbo.syscolumns where id=object_id(''?'') and name='''+@c+''')
alter table ? alter column '+ @c +' varchar(10) not null'
exec sp_MSforeachtable @sql,'?'
字段EID的数据类型由char变为varchar,且长度由15变为18同样道理
关于sp_MSforeachtable内容 见http://www.cnblogs.com/piaoqingsong/archive/2007/06/12/780290.html
set @sql=N'if exists(select 1 from syscolumns where id=object_id(?) and name= 'declare @sql varchar(4000)
declare @c varchar(100)
set @c='EmpNo'
set @sql= 'if exists(select 1 from dbo.syscolumns where id=object_id(''?'') and name='''+@c+''')
alter table ? alter column '+ @c +' varchar(10) not null'
exec sp_MSforeachtable @sql,'?'
字段EID的数据类型由char变为varchar,且长度由15变为18同样道理
关于sp_MSforeachtable内容 见http://www.cnblogs.com/piaoqingsong/archive/2007/06/12/780290.html
#2
declare @sql nvarchar(4000)
declare @c varchar(100)
set @c='EmpNo'
set @sql= 'if exists(select 1 from dbo.syscolumns where id=object_id(''?'') and name='''+@c+''')
alter table ? alter column '+ @c +' varchar(10) not null'
exec sp_MSforeachtable @sql,'?'
字段EID的数据类型由char变为varchar,且长度由15变为18同样道理
关于sp_MSforeachtable内容 见http://www.cnblogs.com/piaoqingsong/archive/2007/06/12/780290.html
#3
declare @sqlstr varchar(4000),
@FieldName sysname
select @Sqlstr = ' ', @FieldName = 'f1'
SELECT @SQLStr = @Sqlstr + ' alter table ' + b.name + ' alter column '+@Fieldname + ' varchar(20) '
from syscolumns a
left join sysobjects b on a.id = b.id
where a.name = @FieldName
print(@Sqlstr)
exec(@Sqlstr)
@FieldName sysname
select @Sqlstr = ' ', @FieldName = 'f1'
SELECT @SQLStr = @Sqlstr + ' alter table ' + b.name + ' alter column '+@Fieldname + ' varchar(20) '
from syscolumns a
left join sysobjects b on a.id = b.id
where a.name = @FieldName
print(@Sqlstr)
exec(@Sqlstr)
#4
declare @name varchar(50)
declare @sql varchar(100)
declare y CURSOR
for select name from sysobjects where xtype='u' and name<>'dtproperties'
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name='EmpNo')
Begin
set @sql='alter table '+ @name+' alter column EmpNo char(10)'
exec(@sql)
End
if exists(select * from syscolumns where id=object_id(@name) and name='EID')
Begin
set @sql='alter table '+ @name+' alter EID column varchar(15)'
exec(@sql)
End
FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y
declare @sql varchar(100)
declare y CURSOR
for select name from sysobjects where xtype='u' and name<>'dtproperties'
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name='EmpNo')
Begin
set @sql='alter table '+ @name+' alter column EmpNo char(10)'
exec(@sql)
End
if exists(select * from syscolumns where id=object_id(@name) and name='EID')
Begin
set @sql='alter table '+ @name+' alter EID column varchar(15)'
exec(@sql)
End
FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y
#5
楼上的因为EMPNo为主键,所以有以下错误,请问怎么修改?
ALTER TABLE ALTER COLUMN EMPNO failed because one or more objects access this column.
ALTER TABLE ALTER COLUMN EMPNO failed because one or more objects access this column.
#6
up!
who can help me on it?
Many Thanks!
who can help me on it?
Many Thanks!
#7
declare @name varchar(50)
declare @sql varchar(100)
declare @key varchar(200)
set @key=''
declare y CURSOR
for select name from sysobjects where xtype='u' and name<>'dtproperties'
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name='EmpNo')
Begin
--查找主键
set @key =(SELECT pkey.name FROM sysobjects AS pkey INNER JOIN sysobjects ON
pkey.parent_obj = sysobjects.id
WHERE (sysobjects.name =@name) and pkey.xtype='PK')
if @key<>'' --删除主键
begin
exec('alter table '+@name+' drop constraint '+@key)
end
--修改 字段长度
set @sql='alter table '+ @name+' alter column EmpNo char(10)'
exec(@sql)
--重建主键
if @key<>''
begin
exec('alter table '+@name+' add constraint '+ @key +' PRIMARY KEY CLUSTERED (EmpNo)')
end
End
if exists(select * from syscolumns where id=object_id(@name) and name='EID')
Begin
set @sql='alter table '+ @name+' alter EID column varchar(15)'
exec(@sql)
End
FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y
declare @sql varchar(100)
declare @key varchar(200)
set @key=''
declare y CURSOR
for select name from sysobjects where xtype='u' and name<>'dtproperties'
OPEN y
FETCH NEXT FROM y INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
if exists(select * from syscolumns where id=object_id(@name) and name='EmpNo')
Begin
--查找主键
set @key =(SELECT pkey.name FROM sysobjects AS pkey INNER JOIN sysobjects ON
pkey.parent_obj = sysobjects.id
WHERE (sysobjects.name =@name) and pkey.xtype='PK')
if @key<>'' --删除主键
begin
exec('alter table '+@name+' drop constraint '+@key)
end
--修改 字段长度
set @sql='alter table '+ @name+' alter column EmpNo char(10)'
exec(@sql)
--重建主键
if @key<>''
begin
exec('alter table '+@name+' add constraint '+ @key +' PRIMARY KEY CLUSTERED (EmpNo)')
end
End
if exists(select * from syscolumns where id=object_id(@name) and name='EID')
Begin
set @sql='alter table '+ @name+' alter EID column varchar(15)'
exec(@sql)
End
FETCH NEXT FROM y INTO @name
END
CLOSE y
DEALLOCATE y