sqlserver里批量修改表名、字段名为小写

SQL SERVER批量将修改表名为大、小写

sql里新建存储过程

USE [Soil_Rural_DB]
GO

/****** Object:  StoredProcedure [dbo].[addPublicTable]    Script Date: 2019-7-4 13:45:53 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[addPublicTable]
AS
BEGIN
declare @sql varchar(300)--,@rowcount varchar(10),@dyncnum int 
     declare @tablename varchar(100) 
     declare cursor1 cursor for         
     select name  from sysobjects  where xtype = 'u'  order by name               
     open cursor1                        
     fetch next from cursor1 into @tablename 
     while @@fetch_status=0            
     begin
         set @sql='sp_rename '''+@tablename+''','''+lower(@tablename)+'''' -- 此为修改为大写，如果修改为小写“lower” 
         --print @sql -- 首先打印出来 
         exec(@sql)              
         fetch next from cursor1 into @tablename 
     end
     close cursor1                    
     deallocate cursor1
END



GO

SQL Server 所有字段名转为小写

sql server 所有表字段改小写_csdn1积分

USE [Soil_Rural_DB]
GO

/****** Object:  StoredProcedure [dbo].[addPublicFeild]    Script Date: 2019-7-4 13:49:05 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
CREATE PROCEDURE [dbo].[addPublicFeild]
AS
BEGIN
    DECLARE @tablename VARCHAR(200);
	DECLARE @field1 INT;
	DECLARE @field2 INT;
	DECLARE @field3 INT;
	DECLARE @field4 INT;
	DECLARE @tablename2 VARCHAR(200);
	DECLARE @colname VARCHAR(510);
	DECLARE @newname VARCHAR(510);
	SET NOCOUNT ON;

     DECLARE vend_cursor CURSOR
                FOR
                     SELECT Name FROM SysObjects Where XType='U'  ORDER BY Name;  
                OPEN vend_cursor;  
                FETCH NEXT FROM vend_cursor INTO @tablename; 

				 WHILE ( @@fetch_status = 0 )
                    BEGIN  
					 select @field1=COUNT(a.id) FROM sysobjects a join syscolumns b ON a.id=b.id WHERE a.name=@tablename AND LOWER(b.name)='createtime';
					 IF(@field1=0)
					 BEGIN
					  EXEC('ALTER TABLE ' + @tablename+' ADD createTime DATETIME;');
					  END;

			 DECLARE vend_cursor2 CURSOR
                FOR
					 SELECT (a.[name] + '.' + b.[name]) AS tablename ,b.[name] colname  
					 FROM dbo.sysobjects a ,dbo.syscolumns b WHERE a.id = b.id  AND a.xtype= 'U'  AND a.name=@tablename;
					  OPEN vend_cursor2;  
                FETCH NEXT FROM vend_cursor2 INTO @tablename2,@colname;
				 WHILE ( @@fetch_status = 0 )
                    BEGIN 
					SET @newname='';
					IF(LEN(@colname)=2)
					BEGIN
					SET @newname=LOWER(@colname);
					END
                    ELSE
                    BEGIN
					SET @newname=STUFF(@colname,1,1,LOWER(SUBSTRING(@colname,1,1))) 
				    END;
					EXEC sp_rename @tablename2,@newname,[COLUMN] PRINT @colname PRINT @newname;

					 FETCH NEXT FROM vend_cursor2 INTO  @tablename2,@colname;
                    END;  
					CLOSE vend_cursor2;   --关闭游标  
                DEALLOCATE vend_cursor2; 


					 FETCH NEXT FROM vend_cursor INTO  @tablename; 
                    END;  
				CLOSE vend_cursor;   --关闭游标  
                DEALLOCATE vend_cursor; 
END


GO

解决