自动生成存储过程的脚本

如果一个表的字段非常的多的话，平时写存储过程，是不是很耗费时间呢，盯着眼睛痛，有了自动生成代码程序，只需F5一下就好了<(￣︶￣)>

 

--自动生成存储过程Update

--要使用的数据库名
declare @DatabaseName varchar(100)
set @DatabaseName='Test'

--要使用的表名
declare @TableName varchar(100)
set @TableName='Profit'

--选出表的所有列名，列的数据类型，数据大小
declare TableCol Cursor For
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
      ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DatabaseName
    AND t.TABLE_TYPE = 'BASE TABLE' and t.TABLE_NAME=@TableName
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

DECLARE @TableSchema varchar(100), @TableNameTemp varchar(100)
DECLARE @ColumnName varchar(100), @DataType varchar(30)
DECLARE @CharLength int

declare @ColumnNameCleaned varchar(100)
declare @IDCount int
set @IDCount=1

declare @UpdateUpperPart varchar(4000)
declare @UpdateLowerPart varchar(4000)


open TableCol
fetch next from TableCol into @TableSchema, @TableNameTemp,
 @ColumnName, @DataType, @CharLength
while @@FETCH_STATUS = 0
 BEGIN
    set @ColumnNameCleaned = Replace(@ColumnName, ' ', '')
    --遍历到第一列字段的时候
    if(@IDCount=1)
    begin
        set @UpdateUpperPart = 'create procedure [dbo].[up_'+@TableNameTemp+'_update]'+CHAR(13)+
            '@p_'+@ColumnNameCleaned+'  '+@DataType
        if @DataType  IN ('varchar', 'nvarchar', 'char', 'nchar')
        begin
            set @UpdateUpperPart=@UpdateUpperPart+'('+CAST(@CharLength as varchar(12))+')'                
        end
        set @UpdateUpperPart= @UpdateUpperPart+CHAR(13)
        
        set @UpdateLowerPart='as'+CHAR(13)+
            'update [dbo].['+@TableNameTemp+']'+CHAR(13)+
            'set ['+@ColumnNameCleaned+']=@p_'+    @ColumnNameCleaned+CHAR(13)                
    end
    else
    begin
        set  @UpdateUpperPart=@UpdateUpperPart+',@p_'+@ColumnNameCleaned+'  '+
                    @DataType
        if @DataType  IN ('varchar', 'nvarchar', 'char', 'nchar')
        begin
            set @UpdateUpperPart=@UpdateUpperPart+'('+CAST(@CharLength as varchar(12))+')'                
        end
        set @UpdateUpperPart= @UpdateUpperPart+CHAR(13)
        
        
        set  @UpdateLowerPart=@UpdateLowerPart+',['+@ColumnNameCleaned+']=@p_'+
                    @ColumnNameCleaned+CHAR(13)
    end
    
    set @IDCount=@IDCount+1
    fetch next from TableCol into @TableSchema, @TableNameTemp,
        @ColumnName, @DataType, @CharLength
end
close TableCol
DEALLOCATE TableCol

print @UpdateUpperPart+@UpdateLowerPart

 

--自动生成存储过程Insert

--要使用的数据库名
declare @DatabaseName varchar(100)
set @DatabaseName='Test'

--要使用的表名
declare @TableName varchar(100)
set @TableName='Profit'

--选出表的所有列名，列的数据类型，数据大小
declare TableCol Cursor For
SELECT c.TABLE_SCHEMA, c.TABLE_NAME, c.COLUMN_NAME, c.DATA_TYPE, c.CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.Columns c
INNER JOIN INFORMATION_SCHEMA.Tables t
      ON c.TABLE_NAME = t.TABLE_NAME
WHERE t.Table_Catalog = @DatabaseName
    AND t.TABLE_TYPE = 'BASE TABLE' and t.TABLE_NAME=@TableName
ORDER BY c.TABLE_NAME, c.ORDINAL_POSITION

DECLARE @TableSchema varchar(100), @TableNameTemp varchar(100)
DECLARE @ColumnName varchar(100), @DataType varchar(30)
DECLARE @CharLength int

declare @ColumnNameCleaned varchar(100)
declare @IDCount int
set @IDCount=1

declare @InsertUpperPart varchar(4000)    --前半部分 create+参数
declare @InsertMiddlePart varchar(4000)    --中间部分 as+insert
declare @InsertLowerPart varchar(4000)    --后半部分 values


open TableCol
fetch next from TableCol into @TableSchema, @TableNameTemp,
 @ColumnName, @DataType, @CharLength
while @@FETCH_STATUS = 0
 BEGIN
    set @ColumnNameCleaned = Replace(@ColumnName, ' ', '')
    --遍历到第一列字段的时候
    if(@IDCount=1)
    begin
        set @InsertUpperPart = 'create procedure [dbo].[up_'+@TableNameTemp+'_Insert]'+CHAR(13)+
            '@p_'+@ColumnNameCleaned+'  '+@DataType
        if @DataType  IN ('varchar', 'nvarchar', 'char', 'nchar')
        begin
            set @InsertUpperPart=@InsertUpperPart+'('+CAST(@CharLength as varchar(12))+')'                
        end
        set @InsertUpperPart= @InsertUpperPart+CHAR(13)
        
        set @InsertMiddlePart = 'as'+CHAR(13)+
            'insert into [dbo].['+@TableNameTemp+']'+CHAR(13)+
            '(['+@ColumnNameCleaned+']'+CHAR(13)
        
        set @InsertLowerPart='values'+CHAR(13)+
            '(@p_'+@ColumnNameCleaned+CHAR(13)                
    end
    else
    begin
        set  @InsertUpperPart=@InsertUpperPart+',@p_'+@ColumnNameCleaned+'  '+
                    @DataType
        if @DataType  IN ('varchar', 'nvarchar', 'char', 'nchar')
        begin
            set @InsertUpperPart=@InsertUpperPart+'('+CAST(@CharLength as varchar(12))+')'                
        end
        set @InsertUpperPart= @InsertUpperPart+CHAR(13)
        
        set @InsertMiddlePart=@InsertMiddlePart+',['+@ColumnNameCleaned+']'+CHAR(13)
        
        set    @InsertLowerPart=@InsertLowerPart+',@p_'+@ColumnNameCleaned+CHAR(13)                
    end
    
    set @IDCount=@IDCount+1
    fetch next from TableCol into @TableSchema, @TableNameTemp,
        @ColumnName, @DataType, @CharLength
end
close TableCol
DEALLOCATE TableCol

set @InsertMiddlePart=@InsertMiddlePart+')'+CHAR(13)
set @InsertLowerPart=@InsertLowerPart+')'

print @InsertUpperPart+@InsertMiddlePart+@InsertLowerPart

 

 

 

转载于:https://www.cnblogs.com/meiwlongblog/archive/2013/02/22/2922356.html