Utility generating DML statement taking just table name; smarter way of doing your work

DML statements are those which you need to start any kind of manipulation of your data; these are the statements which you use very often employ in your stored procedure to build up any Business Logic.

Just think about any table which has say 50 odd column which is very common, now writing any kind of DML statement say SELECT statement, how long it takes, probably 1 or 2 or say for if you r not taking help of sys.columns 3-4 minutes.

Now think about how many DML statements you cover in any business logic SP, yup a lot!!! Now imagine about an utility which in a second produce your required DML statement.

Fascinated about the idea, let start writing a routine which will make our daily work ease

IF OBJECT_ID('dbo.usp_getDMLText') IS NOT NULL

BEGIN 

       DROP PROCEDURE dbo.usp_getDMLText

END

GO

CREATE PROCEDURE dbo.usp_getDMLText (@TblName VARCHAR(50))

AS

DECLARE @designSelect VARCHAR(max)

DECLARE @designUpdate VARCHAR(max)

DECLARE @designDelete VARCHAR(max)

DECLARE @designDrop VARCHAR(max)

DECLARE @designTruncate VARCHAR(max)

DECLARE @schemaName VARCHAR(255)

BEGIN

       IF OBJECT_ID('tempdb.#TAB_DMLStatement') IS NOT NULL

              DROP TABLE #TAB_DMLStatement

       CREATE TABLE #TAB_DMLStatement (

              Id INT identity(1, 1)

              ,Operation VARCHAR(10)

              ,[Text] VARCHAR(max)

              )

       SELECT TOP 1 @schemaName = s.NAME

       FROM sys.tables t

       INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

       WHERE t.NAME LIKE 'Address'

       SET @schemaName = @schemaName + '.'

       SELECT @designSelect = substring((

                           SELECT (', ' + Column_Name + CHAR(10))

                           FROM INFORMATION_SCHEMA.COLUMNS

                           WHERE Table_Name = @TblName

                           FOR XML PATH('')

                           ), 3, 1000)

       SET @designSelect = 'SELECT ' + @designSelect + ' FROM ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'SELECT'

              ,@designSelect

              )

       SELECT @designUpdate = substring((

                           SELECT (', ' + Column_Name + '= @p' + Column_Name + CHAR(10))

                           FROM INFORMATION_SCHEMA.COLUMNS

                           WHERE Table_Name = 'Address'

                           FOR XML PATH('')

                           ), 3, 1000)

       SET @designDelete = @designUpdate

       SET @designUpdate = 'UPDATE ' + @schemaName + @TblName + CHAR(10) + 'SET ' + @designUpdate + CHAR(10) + 'WHERE ' + REPLACE(@designUpdate, ',', ' AND ')

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'UPDATE'

              ,@designUpdate

              )

       SET @designDelete = 'DELETE ' + @TblName + CHAR(10) + 'WHERE ' + REPLACE(@designDelete, ',', ' AND ')

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'DELETE'

              ,@designDelete

              )

       SET @designDrop = 'DROP TABLE ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'DROP'

              ,@designDrop

              )

       SET @designTruncate = 'TRUNCATE TABLE ' + @schemaName + @TblName

       INSERT INTO #TAB_DMLStatement

       VALUES (

              'TRUNCATE'

              ,@designTruncate

              )

       SELECT Id

              ,Operation

              ,TEXT

       FROM #TAB_DMLStatement

       DROP TABLE #TAB_DMLStatement

END

Now you have here a routine which will generate DML statement for you taking just table name.

Lets configure it to shortcut key

For this Go to your SSMS

TOOL à OPTION à Environment à Keyboard àQuery Shortut à in Ctrl + F1 text box write your routine name i.e usp_getDMLText

Now our shortcut is set.

Now just take any table name select it and press Ctrl + F1

Eg Address

That’s all J so simple, Guys if you have any other idea, do coin up, will try to implement :)

I am happy even if I don’t get food for my daily meal, till you are with me; my SQL Server  :)

 

Post Reference: Vikram Aristocratic Elfin Share