SQL Server and Oracle Batch table name and field name capitalization

Source: Internet
Author: User

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

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.