Monday 10 February 2014

Generate CRUD Procedures for any given SQL Table

Hi SQL Geeks,


There may be times when you need to create Basic Procedures like Insert, Update, Select Some/All Tables in your Database. And you have hundreds of Tables. Right.

I also came across such situations Many times.
Thats the reason, i wrote a Script which will generate these procedures for any given Table.
These types of scripts are already available over net, but i tried to write my own.
And this worked for me.

I Hope this will be helpful for you also


I'll change this script and add new features in it soon.
Below is the Script.Enjoy.


You can also download this script here

Please share your Comments.

/*

    Name    :    CRUDforSQL_V1.0
    Author    :    Parth Malhan
    Written on    :    10th Feb 2014 10:02 PM
    Purpose    :    This Script will Create Basic Procedures For any Given Table(insert, Select, Update)
    Usage    :    1.Create Proc Given Below in the database for which you want to generate CRUD Operations
                    2.Exec Procedure by passing the Schema Name and Table Name
                    3.Procedure will Return 3 Columns for Select, Insert, Update.
                    4.Copy these scripts one by one, Verify/Change generated Scripts and execute accordingly.

*/


CREATE PROCEDURE usp_DBA_Show_CRUD_Script_V1_0
(
    @SchemaName NVARCHAR(128),        --dbo
    @TableName NVARCHAR(128)        --test
)
AS
BEGIN
    /*    Session Variables    */
    DECLARE  
                @FullTableName NVARCHAR(256),
                @ColumnNames NVARCHAR(max),
                @TableID BIGINT,
                @SelectFilterColumn NVARCHAR(256),
                @InsertProcParams NVARCHAR(max),
                @InsertQueryParams NVARCHAR(max),
                @UpdateQueryParams NVARCHAR(max),
                @SelectProcParams NVARCHAR(max)
                ;

    SET @FullTableName='['+@SchemaName+'].['+@TableName+']';
    SET @ColumnNames='';
    SET @SelectProcParams='';
    SET @InsertProcParams='';
    SET @InsertQueryParams=''
    SET @UpdateQueryParams=''
    SET @SelectFilterColumn=''

    SET @TableID=OBJECT_ID(@FullTableName);

    ;WITH cte0 AS (
        SELECT
            '['+TABLE_SCHEMA+'].['+TABLE_NAME+']' AS TABLE_NAME,COLUMN_NAME,ORDINAL_POSITION,
            '['+DATA_TYPE+']' AS DATA_TYPE,CHARACTER_MAXIMUM_LENGTH,a.IS_NULLABLE,
            b.is_identity
        FROM INFORMATION_SCHEMA.COLUMNS a
        LEFT JOIN sys.identity_columns b ON a.COLUMN_NAME=b.name AND b.object_id=@TableID
        WHERE '['+TABLE_SCHEMA+'].['+TABLE_NAME+']'=@FullTableName
    )
    SELECT

        /*    Insert Proc    */
        @InsertProcParams=@InsertProcParams+
        CASE WHEN is_identity=1 THEN '' ELSE
            CHAR(13)+'@'+COLUMN_NAME+' AS '+DATA_TYPE+
            COALESCE('('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20))+')','')+ ','
        END,

        /*    Insert Query Params    */
        @InsertQueryParams=@InsertQueryParams+
        CASE WHEN is_identity=1 THEN '' ELSE
            CHAR(13)+'@'+COLUMN_NAME+','
        END,

        /*    Select Proc    */
        @SelectProcParams=@SelectProcParams+
        CASE WHEN is_identity=1 THEN
            COLUMN_NAME+' AS '+DATA_TYPE+
            COALESCE('('+CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20))+')','')+ CHAR(13)+',' ELSE
        ''
        END,

        /*    Update Qurey Params    */
        @UpdateQueryParams=@UpdateQueryParams+
        CASE WHEN is_identity=1 THEN ''
        ELSE    COLUMN_NAME+'=@'+COLUMN_NAME+CHAR(13)+',' END,

  
        /*    Identity column For select and update    */
        @SelectFilterColumn=CASE WHEN is_identity=1 THEN COLUMN_NAME ELSE @SelectFilterColumn END
    FROM cte0 ORDER BY TABLE_NAME,ORDINAL_POSITION

    SET @InsertProcParams=LEFT(@InsertProcParams,LEN(@InsertProcParams)-1)
    SET @InsertQueryParams=LEFT(@InsertQueryParams,LEN(@InsertQueryParams)-1)

    SET @SelectProcParams=LEFT(@SelectProcParams,LEN(@SelectProcParams)-1)
    SET @UpdateQueryParams=LEFT(@UpdateQueryParams,LEN(@UpdateQueryParams)-1)


    SELECT
            'Create Procedure '+'['+@SchemaName+'].[usp_sel_'+@TableName+']'+CHAR(13)+'(@'+@SelectProcParams+CHAR(13)+')'+CHAR(13)+'AS'+
            CHAR(13)+'Begin'+CHAR(13)+' Select * FROM '+@FullTableName+' Where ['+@SelectFilterColumn+']=@'+@SelectFilterColumn+';'+CHAR(13)+'End'+CHAR(13)+CHAR(13)+CHAR(13)
            AS Select_Script
            ,

            'Create Procedure '+'['+@SchemaName+'].[usp_ins_'+@TableName+']'+CHAR(13)+'('+@InsertProcParams+CHAR(13)+')'+CHAR(13)+'AS'+
            CHAR(13)+'Begin'+CHAR(13)+' insert into '+@FullTableName+' Values('+@InsertQueryParams+');'+CHAR(13)+'End'+CHAR(13)+CHAR(13)+CHAR(13)
            AS Insert_Script
            ,

            'Create Procedure '+'['+@SchemaName+'].[usp_upd_'+@TableName+']'+CHAR(13)+'(@'+@SelectProcParams+','+@InsertProcParams+CHAR(13)+')'+CHAR(13)+'AS'+
            CHAR(13)+'Begin'+CHAR(13)+' UPDATE '+@FullTableName+' Set    '+CHAR(13)+@UpdateQueryParams+CHAR(13)+' Where ['+@SelectFilterColumn+']=@'+@SelectFilterColumn+';'+CHAR(13)+'End'+CHAR(13)+CHAR(13)+CHAR(13)
            AS Update_Script;
END
GO
CREATE TABLE dbo.Test(Id INT IDENTITY, NAME VARCHAR(50),phoneNo VARCHAR(15))
EXEC usp_DBA_Show_CRUD_Script_V1_0 'dbo','Test'
DROP TABLE dbo.Test
DROP PROC usp_DBA_Show_CRUD_Script_V1_0

1 comment: