Capitalize the first letter of all tables and fields in the database

Source: Internet
Author: User

As the size of the database becomes larger, the database tables are hundreds of thousands, and if you need to do operations on database table names and field names, a single good statement is done, but if you want to do all the table and field names for the entire library, it's a bit of a hassle. Therefore, we need to use SQL statements to do Batch table name field name modification operations.

First, let's look at the functions in the database that read the name of a table field:
1. Get all database names:
Select Name from Master. sysdatabases

2. Get all table names:
Select name from sysobjects where type= ' U '
Xtype= ' U ': represents all user tables;
Xtype= ' S ': denotes all system tables;

3. Get all field names:
Select Name from syscolumns Where id=object_id (' TableName ')

You can then use cursors to implement all the indicated and field names:

Declare@tablenamevarchar( -)Declare@columnnamevarchar( -)DeclareCur_tablecursor  for SelectName fromsysobjectswhereType=' U ' OpenCur_tableFetch Next  fromCur_table into@tablename while @ @fetch_status =0 begin -----------------------------------------   DeclareCur_columncursor  for SelectName fromsyscolumnswhereID=OBJECT_ID (@tablename)OpenCur_columnFetch Next  fromCur_column into@columnname while @ @fetch_status =0 begin   Declare@chvarchar( -), @ch1varchar( -), @uppertablenamevarchar( -)Set@[email protected]+'. '[Email protected]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)execSp_rename @tablename, @uppertablenameexecSp_rename @ch, @ch1,' column ' Fetch Next  fromCur_column into@columnnameEnd CloseCur_columndeallocateCur_column----------------------------------------- Fetch Next  fromCur_table into@tablenameEnd CloseCur_tabledeallocateCur_table

After execution, the table and field names of the entire database become capitalized in the first letter.

Capitalize the first letter of all tables and fields in the database

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.