一、废话:
随着数据库的规模越来越大,数据库的表也有成百上千,如果需要对数据库表名及字段名做操作,单个还好,直接一条语句搞定了,但如果要对整个库的所有表和字段名做操作,那就显得有点麻烦了。因此,我们需要利用sql语句做批量表名字段名修改操作。
二、了解部分系统表:
1、获取所有数据库名:
SELECT NAME FROM MASTER..SysDatabases
2、获取所有用户表名:
SELECT NAME FROM sysobjects WHERE TYPE = 'U'
3、获取所有字段名:
SELECT NAME FROM SysColumns WHERE id = OBJECT_ID('TableName')
三、用游标实现修改所有表名和字段名:
) ) DECLARE cur_table CURSOR FOR SELECT NAME FROM sysobjects WHERE TYPE = 'U' AND NAME <> 'sysdiagrams' OPEN cur_table FETCH NEXT FROM cur_table INTO @tablename BEGIN ----------------------------------------- DECLARE cur_column CURSOR FOR SELECT NAME FROM syscolumns WHERE id = OBJECT_ID(@tablename) OPEN cur_column FETCH NEXT FROM cur_column INTO @columnname BEGIN ), ), ), ), ) , 1) , 1) IF ASCII(@columnnamefirstchar) BETWEEN ASCII('A') AND ASCII('Z') BEGIN SET @ch = @tablename + '.' + @columnname , ) EXEC sp_rename @ch, @ch1, 'column' END IF ASCII(@tablenamefirstchar) BETWEEN ASCII('a') AND ASCII('z') BEGIN , ) EXEC sp_rename @tablename, @uppertablename END FETCH NEXT FROM cur_column INTO @columnname END CLOSE cur_column DEALLOCATE cur_column ----------------------------------------- FETCH NEXT FROM cur_table INTO @tablename END CLOSE cur_table DEALLOCATE cur_table
执行后整个数据库的表名都会变成首字母大写,字段名首字母都会变成小写。