赞
踩
- declare @sql varchar(300)
- declare @tablecolumnname varchar(100), @columnname varchar(100)
- declare cursor1 cursor for
- SELECT '表名' 'tablecolumnname',a.name columnname FROM syscolumns a
- inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
- where d.name='表名' and a.colorder>1 and a.colorder<38 --仅修改第2至第37个字段
-
- open cursor1
- fetch next from cursor1 into @tablecolumnname,@columnname
- while @@fetch_status=0
- begin
- set @sql='sp_rename '''+@tablecolumnname+'.'+@columnname+''','''+lower( SUBSTRING(@columnname,1,1))+SUBSTRING(@columnname,2,LEN(@columnname)) +''',''column''' -- 此为修改为大写,如果修改为小写“lower”
- print @sql
- exec(@sql)
- fetch next from cursor1 into @tablecolumnname,@columnname
- end
- close cursor1
- deallocate cursor1
获取表格需要修改的字段:
SELECT '表名' 'tablecolumnname',a.name columnname FROM syscolumns a
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
where d.name='表名' and a.colorder>1 and a.colorder<38 --仅修改第2至第37个字段
批量清理表数据
- declare @sql varchar(300)
- declare @tablecolumnname varchar(100)
- declare cursor1 cursor for
- Select Name FROM esldatamanager..SysObjects Where XType='U' orDER BY Name
- open cursor1
- fetch next from cursor1 into @tablecolumnname
- while @@fetch_status=0
- begin
- set @sql='truncate table '+@tablecolumnname --清理表语句
- print @sql
- exec(@sql)
- fetch next from cursor1 into @tablecolumnname
- end
- close cursor1
- deallocate cursor1
Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。