sql批量修改字段

通过存储过程来实现，方法说明：

1、建一个临时表用于存放表名和字段名；

2、在系统表中查看有该字段的所有表，存入临时表

3、在临时表建游标，逐条执行修改；

4、删除临时表。

CREATE PROCEDURE Rename

@databaseName varchar(500),

@oldName varchar(500),

@newName varchar(500)

AS

create table #temp(tablename varchar(200),columnName varchar(200))

declare @tableName_cursor varchar(200)

declare @colName_cursor varchar(200)

declare @objName varchar(200)

declare @tableName varchar(200),@colName varchar(200)

exec('declare tableName_cursor cursor for select name from '+@databaseName+'dbosysobjects where xtype=''u'' and status >= 0')

open tableName_cursor

fetch next from tableName_cursor into @tableName_cursor

while @@fetch_status = 0

begin

insert into #temp select @tableName_cursor,name from syscolumns where id = object_id(@tableName_cursor)

fetch next from tableName_cursor into @tableName_cursor

end

close tableName_cursor

deallocate tableName_cursor

declare col_cursor cursor for select columnName from #temp

open col_cursor

fetch next from col_cursor into @colName_cursor

while @@fetch_status = 0

begin

if @colName_cursor = @oldName

begin

select @tableName=tableName,@colName=columnName from #temp where columnName = @colName_cursor

set @objName = @tableName+''+@colName

exec sp_rename @objName,@newName,'Column'

end

fetch next from col_cursor into @colName_cursor

end

close col_cursor

deallocate col_cursor

select from #temp where columnName = 'isdeleted' or columnName = 'isdatadeleted'

drop table #temp

GO

exec rename @databaseName='test',@oldName='isdeleted',@newName = 'isdatadeleted'

drop PROCEDURE rename

--以下是我根据你的描述，做的测试，希望对你有帮助：\x0d\\x0d\-- 新建一张表kongxianji：\x0d\CREATE TABLE kongxianji(a VARCHAR2(200));\x0d\\x0d\-- 插入数据，然后提交：\x0d\INSERT INTO kongxianji (a) VALUES ('12345(6)789');\x0d\INSERT INTO kongxianji (a) VALUES ('12345(67)89');\x0d\INSERT INTO kongxianji (a) VALUES ('12345(678)9');\x0d\COMMIT;\x0d\SELECT FROM kongxianji;\x0d\\x0d\-- 把a中的括号去掉，然后提交，到这一步可以查看表中数据的变化情况：\x0d\UPDATE kongxianji SET a = replace(a,substr(a,instr(a,'('),(instr(a,')')-instr(a,'(')+1)),'');\x0d\COMMIT;\x0d\\x0d\SELECT FROM kongxianji;

　利用sql语句进行修改，举例说明：

1、ALTER TABLE `test` DEFAULT CHARACTER SET utf8;该命令用于将表test的编码方式改为utf8；

2、ALTER TABLE `test` CHANGE `name` `name` VARCHAR(36) CHARACTER SET utf8 NOT NULL; 该命令用于将表test中name字段的编码方式改为utf8

先把原表中ID字段删除。再导出数据，

再导入就OK了

给你一个PHP简单的导入代码,就是那个意思了

$query = $db->query("SELECT FROM 原字段");

while ($newTable = $db->fetch_array($query)) {

$db->query("INSERT INTO 新字段 (sNa) VALUES ($newTable['sNa'])");

}

use test --修改为你的数据库名称

declare tb_name cursor for select NAME,ID from sysobjects WHERE TYPE='U' --得到用户表的ID

declare @in_char varchar(20)

set @in_char='%<script>%'

---查找注入字符串

declare @tj varchar(5000)

set @tj=' where '

declare @i int

set @i=0

declare @sql varchar(200)

DECLARE @table_name varchar(50)

DECLARE @table_id varchar(50)

DECLARE @feild_name varchar(50)

DECLARE @fd_count int

open tb_name

FETCH next FROM tb_name into @table_name,@table_id

while

@@FETCH_STATUS=0

begin

set @i=0

declare fd_name cursor for SELECT NAME FROM syscolumns

where id=@table_id and type=39 group by name--得到用户表某一ID的所有字符类型字段名称

open fd_name

FETCH next FROM fd_name into @feild_name

print @fd_count

while

@@FETCH_STATUS=0

begin

set @i=@i+1

set @tj=@tj+@feild_name +' like '''+ @in_char +'''' +(case when (SELECT count(type) as fd_count FROM syscolumns

where id=@table_id and type=39)

>@i then ' or ' else ' 'end)

FETCH next FROM fd_name into @feild_name

end

-- print @tj

if @tj!=''

begin

SET @SQL='DELETE FROM '+@TABLE_NAME+@TJ

print '切记先运行代码看print 出来的SQL确认后再删除数据，即将运行的代码是 '

print '===================================================================='

print @SQL

print '===================================================================='

--切记先运行代码看print 出来的SQL确认后再删除数据

exec(@sql) --修改注释后删除数据

end

set @tj=''

CLOSE fd_name

deallocate fd_name

FETCH next FROM tb_name into @table_name,@table_id

end

close tb_name

deallocate tb_name

以上就是关于sql批量修改字段全部的内容，包括:sql批量修改字段、如何批量修改oracle数据库中的某一个字段、怎样批量修改mysql 中表和字段的编码方式等相关内容解答，如果想了解更多相关内容，可以关注我们，你们的支持是我们更新的动力！