SQL Server and Oracle Batch table name and field name capitalization

In SQL porting to Oracle, there is a problem with table name and field name capitalization, because the default table name and field names in Oracle are uppercase, although they can be manipulated by using double quotes, such as SELECT * from "TestTable" but in many cases This is expensive, because many of the program's code is already written well, and to modify the Oracle system configuration is not secure, a more secure way is to bulk modify the table name and file name.

First of all, the method of modifying table names and field names in sql: Although the case of table names and field names is not distinguished by default in SQL, when importing data from SQL DTS, if the table name and field name are lowercase, the resulting code is lowercase and vice versa. Therefore, when the Oracle data table is generated by this method, the first batch of SQL table names and field names will be changed to uppercase, the corresponding code is given below.

Batch Modify table name:

DECLARE @sql varchar ( -)--, @rowcount varchar (Ten), @dyncnumintdeclare @tablename varchar ( -) DECLARE cursor1 cursor for             SelectName fromsysobjectswhereXtype ='u'ORDER BY name Open Cursor1 fetch next fromCursor1 into @tablename while@ @fetch_status =0beginSet@sql ='sp_rename" "[Email protected]+" "," "+upper (@tablename) +" '"Print @sql--EXEC (@sql) fetch next fromCursor1 into @tablename end close Cursor1 deallocate cursor1

Bulk Modify field name code:

DECLARE @sql varchar ( -) declare @tablecolumnname varchar ( -), @columnname varchar ( -) DECLARE cursor1 cursor for             Selectb.name+'. ['+a.name+']', A.name fromSyscolumns A, sysobjects bwherea.ID = object_id (b.name) and B.xtype ='u'<> A.xtype189<> A.xtype the<> A.xtype *<> A.xtype $Open Cursor1 Fetch next fromCursor1 into @tablecolumnname, @columnname while@ @fetch_status =0beginSet@sql ='sp_rename" "[Email protected]+" "," "+upper (@columnname) +" ","'column" "--print @sql exec (@sql) fetch next fromCursor1 into @tablecolumnname, @columnname end close Cursor1 deallocate cursor1

Another case is that the table has been imported into Oracle, but the table name and field name are found to be lowercase, and you need to modify the table and field names in Oracle.

This time, we need to use a way to help us automatically generate modified statements, and then copy out to execute it.

Modify Table Name:

1 Select ' ALTER TABLE " '| | table_name| | '  '| | UPPER (table_name) | | ' ; '  from where table_name<>upper (table_name);

Modify Field Name:

1 Select '  '| | table_name| | ' Rename column " '| | column_name | | '  '| | Upper (column_name) | | ' ; ' 2  from where column_name<>upper (column_name);

At this point, we can generate the corresponding ALTER statement in a client tool such as SQL Plus or Toad, and then copy it out and execute it OK.

By extension, if you need to empty the data in Oracle or delete all the tables, you can use the same method as above

-- Clear Data Select'| | table_name | | ' ; '  from  -- Delete table select'| | table_name | | ' ; '  from

Original link: http://www.cnblogs.com/tippoint/archive/2012/11/07/2758855.html

SQL Server and Oracle Batch table name and field name capitalization