About Me

My photo
Mumbai, Maharastra, India
He has more than 7.6 years of experience in the software development. He has spent most of the times in web/desktop application development. He has sound knowledge in various database concepts. You can reach him at viki.keshari@gmail.com https://www.linkedin.com/in/vikrammahapatra/ https://twitter.com/VikramMahapatra http://www.facebook.com/viki.keshari

Search This Blog

Saturday, December 27, 2014

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

No comments:

Post a Comment