mysql删除大写的表格_数据库操作--批量修改数据库表名及字段名大小写转换及首字母大写+删除没有数据无用的表...

1、修改表名为小写

declare @sql varchar(300)--,@rowcount varchar(10),@dyncnum int

declare @tablename varchar(100)

declare cursor1 cursor for

select name from sysobjects where xtype = 'u' order by name

open cursor1

fetch next from cursor1 into @tablename

while @@fetch_status=0

begin

set @sql='sp_rename '''+@tablename+''','''+LOWER(@tablename)+''''

print @sql

exec(@sql)

fetch next from cursor1 into @tablename

end

close cursor1

deallocate cursor1

2、修改数据库中所用字段名为小写

declare @sql varchar(300)

declare @tablecolumnname varchar(100), @columnname varchar(100)

declare cursor1 cursor for

select b.name+'.['+a.name+']',a.name from syscolumns a ,sysobjects b where a.id = object_id(b.name) and b.xtype = 'u' and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36

open cursor1

fetch next from cursor1 into @tablecolumnname,@columnname

while @@fetch_status=0

begin

set @sql='sp_rename '''+@tablecolumnname+''','''+LOWER(@columnname)+''',''column'''

--print @sql

exec(@sql)

fetch next from cursor1 into @tablecolumnname,@columnname

end

close cursor1

deallocate cursor1

注：转为大写的表名及字段名将LOWER改为UPPER即可

3、修改表名及字段名为首字母大写

declare @tablename varchar(50)

declare @columnname varchar(50)

declare cur_table cursor for

select name from sysobjects where type='U'

open cur_table

fetch next from cur_table into @tablename

while @@fetch_status=0

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

while @@fetch_status=0

begin

declare @ch varchar(50),@ch1 varchar(50),@uppertablename varchar(50)

set @ch=@tablename+'.'+@columnname

set @ch1=upper(substring(@columnname,1,1))+substring(@columnname,2,len(@columnname)-1)

set @uppertablename=upper(substring(@tablename,1,1))+substring(@tablename,2,len(@tablename)-1)

exec sp_rename @tablename,@uppertablename

exec sp_rename @ch,@ch1,'column'

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

4、删除没有数据的表

--统计用户表

create table #t(name varchar(255), rows bigint, reserved varchar(20), data varchar(20), index_size varchar(20), unused varchar(20))

exec sp_MSforeachtable "insert into #t exec sp_spaceused '?'"

select * from #t

--drop table #t

--删除表

DECLARE @Table NVARCHAR(30)

DECLARE tmpCur CURSOR FOR

select name from #t WHERE rows=0

OPEN tmpCur

FETCH NEXT FROM tmpCur INTO @Table

WHILE @@FETCH_STATUS = 0

BEGIN

DECLARE @sql VARCHAR(100)

SELECT @sql = 'drop table ' + @Table

EXEC(@sql)

FETCH NEXT FROM tmpCur INTO @Table

END

CLOSE tmpCur

DEALLOCATE tmpCur