SQL Server database specification--1, automation specification naming

One, nonsense:

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.

Second, understand the partial system table:

1. Get all database names:

SELECT NAME  from   MASTER.. sysdatabases

2. Get all user table names:

SELECT NAME  from    sysobjects WHERE  = ' U '

3. Get all field names:

SELECT NAME  from    syscolumns WHERE  = object_id ('TableName')

Third, use the cursor implementation to modify all table names and field names:

DECLARE @tablename VARCHAR( -) DECLARE @columnname VARCHAR( -) DECLARECur_tableCURSOR   for    SELECTNAME fromsysobjectsWHERETYPE= 'U'            andNAME<> 'Sysdiagrams'OPENCur_tableFETCH NEXT  fromCur_table into @tablename  while @ @fetch_status = 0BEGIN    -----------------------------------------    DECLARECur_columnCURSOR       for        SELECTNAME fromsyscolumnsWHEREId= object_id(@tablename)        OPENCur_columnFETCH NEXT  fromCur_column into @columnname      while @ @fetch_status = 0    BEGIN        DECLARE @columnnamefirstchar     CHAR(1),                @tablenamefirstchar      CHAR(1),                @ch                      VARCHAR( -),                @ch1                     VARCHAR( -),                @uppertablename          VARCHAR( -)                SET @columnnamefirstchar = SUBSTRING(@columnname,1,1)        SET @tablenamefirstchar = SUBSTRING(@tablename,1,1)        IF ASCII(@columnnamefirstchar)between ASCII('A') and ASCII('Z')        BEGIN            SET @ch = @tablename + '.' + @columnname               SET @ch1 = LOWER(@columnnamefirstchar)+ SUBSTRING(@columnname,2,LEN(@columnname)-1)               EXECsp_rename@ch,                 @ch1,                 'column'        END                IF ASCII(@tablenamefirstchar)between ASCII('a') and ASCII('Z')        BEGIN            SET @uppertablename = UPPER(@tablenamefirstchar)+ SUBSTRING(@tablename,2,LEN(@tablename)-1)               EXECsp_rename@tablename,                 @uppertablename        END                FETCH NEXT  fromCur_column into @columnname    END CLOSECur_columndeallocateCur_column-----------------------------------------     FETCH NEXT  fromCur_table into @tablenameEND CLOSECur_tabledeallocateCur_table

After execution, the entire database's table name becomes capitalized, and the first letter of the field names becomes lowercase.

SQL Server database specification--1, automation specification naming