Introduction
This tip shows the examples of automating such tasks as views creation, query generation, and consolidated tables creation by using Dynamic SQL.
Background
It's no news that native IntelliSense support for SSMS was first implemented in SQL Server 2008. With the release of SQL Server 2012 native IntelliSense became somewhat more functional however its performance was still not perfect - that was mainly due to the specific of my work which required a lot of T-SQL coding.
On the one hand, third-party tools which offered expanded IntelliSense functionality helped me out a lot and allowed to make T-SQL coding more efficient.
For example, SQL Prompt and SQL Complete can automatically complete the syntax of ALTER
, INSERT
, EXEC
statements and can expand columns for SELECT
statements.
To execute the expansion of a selected statement, it is enough to press the TAB key when the hint comes up.
However, in my case, there was the necessity for automatic code generation for more specific statements.
Let's take a closer look at several examples of the actual tasks, which I managed to automate by using Dynamic SQL.
Using the Code
1. Automatic Views Creation
Let's start with a plain sailing, for example, try to implement automatic generation of view definition according to the table name:
DECLARE
@SQL NVARCHAR(MAX)
, @table_name SYSNAME
, @view_name SYSNAME
SELECT
@table_name = '[dbo].[BlogUsers]'
, @view_name = '[dbo].[vw_BlogUsers]'
SELECT @SQL =
CASE WHEN OBJECT_ID(@view_name, 'V') IS NOT NULL
THEN 'ALTER'
ELSE 'CREATE'
END + ' VIEW ' + @view_name + '
AS
SELECT ' + STUFF((
SELECT CHAR(13) + ' , t.[' + c.name + ']'
FROM sys.columns c WITH(NOWAIT)
WHERE c.[object_id] = OBJECT_ID(@table_name, 'U')
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 6, '') + '
FROM ' + @table_name + ' t'
PRINT @SQL
EXEC sys.sp_executesql @SQL
After the script execution, the view will be created automatically:
CREATE VIEW [dbo].[vw_BlogUsers]
AS
SELECT t.[UserID]
, t.[GoldBadges]
, t.[SilverBadges]
, t.[BronzeBadges]
, t.[TotalBadges]
FROM [dbo].[BlogUsers] t
2. Automatic Query Generation
Let's move on and write a script which will generate UNPIVOT
query. Note that this approach is not a universal solution. That is why it has certain restrictions – unpivoted columns should have compatible data-types between them. Granting this restriction, let's create a sample table:
IF OBJECT_ID ('dbo.BlogUsers', 'U') IS NOT NULL
DROP TABLE dbo.BlogUsers
CREATE TABLE dbo.BlogUsers
(
UserID BIGINT PRIMARY KEY
, GoldBadges INT NOT NULL DEFAULT 0
, SilverBadges INT NOT NULL DEFAULT 0
, BronzeBadges INT NOT NULL DEFAULT 0
, TotalBadges AS GoldBadges + SilverBadges + BronzeBadges
)
INSERT INTO dbo.BlogUsers (UserID, GoldBadges, SilverBadges, BronzeBadges)
VALUES
(1, 5, 4, 0),
(2, 0, 9, 1),
(3, 2, 4, 10)
Let's create a dynamic query for the table by selecting columns which are not included in the primary key and are not identity and computed:
DECLARE
@SQL NVARCHAR(MAX)
, @object_name SYSNAME
SELECT @object_name = '[dbo].[BlogUsers]'
SELECT @SQL = '
SELECT *
FROM ' + @object_name + '
UNPIVOT (
Value FOR Code IN (' + STUFF((
SELECT ', [' + c.name + ']'
FROM sys.columns c WITH(NOWAIT)
WHERE c.[object_id] = OBJECT_ID(@object_name)
AND INDEX_COL(@object_name, 1, c.column_id) IS NULL
AND c.is_computed = 0
AND c.is_identity = 0
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') + ')
) unpiv'
PRINT @SQL
EXEC sys.sp_executesql @SQL
The execution results – the UNPIVOT
query:
SELECT *
FROM [dbo].[BlogUsers]
UNPIVOT (
Value FOR Code IN ([GoldBadges], [SilverBadges], [BronzeBadges])
) unpiv
3. Creation of Consolidated Tables
The process of creating tables with a large count of similar columns is quite a tiring task which can be easily automated with the help of Dynamic SQL.
The following script is an example:
DECLARE
@SQL NVARCHAR(MAX)
, @object_name SYSNAME
, @key_column SYSNAME
SELECT
@object_name = '[dbo].[ManufactureByYears]'
, @key_column = 'ManufactureID'
SELECT @SQL = '
IF OBJECT_ID (''' + @object_name + ''', ''U'') IS NOT NULL
DROP TABLE ' + @object_name + '
CREATE TABLE ' + @object_name + ' (
[' + @key_column + '] INT PRIMARY KEY' + (
SELECT CHAR(13) + ' , [Year' + CAST(2000 + sv.number AS VARCHAR(4)) + '] INT'
FROM [master].dbo.spt_values sv
WHERE sv.[type] = 'p'
AND sv.number BETWEEN 1 AND 13
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)') + '
)'
PRINT @SQL
EXEC sys.sp_executesql @SQL
The result of its execution is the following query:
IF OBJECT_ID ('[dbo].[ManufactureByYears]', 'U') IS NOT NULL
DROP TABLE [dbo].[ManufactureByYears]
CREATE TABLE [dbo].[ManufactureByYears] (
[ManufactureID] INT PRIMARY KEY
, [Year2001] INT
, [Year2002] INT
, [Year2003] INT
, [Year2004] INT
, [Year2005] INT
, [Year2006] INT
, [Year2007] INT
, [Year2008] INT
, [Year2009] INT
, [Year2010] INT
, [Year2011] INT
, [Year2012] INT
, [Year2013] INT
)
Over time, the amount of such scripts has increased and the process of finding each of them for using again became more time consuming. That prompted the solution to add the most frequently used ones as snippets.
As a conclusion, I would like to highlight the idea that the abilities of dynamic SQL are numerous and are not limited to the above mentioned examples. I really hope that the described possibilities of Dynamic SQL will help readers to optimize and facilitate the most routine operations.