USE MASTER
GO
IF OBJECT_ID('dbo.sp_unpivot') IS NULL
EXEC('CREATE PROCEDURE dbo.sp_unpivot AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_unpivot
@TableName SYSNAME = NULL,
@IncludeColumnId BIT = 0,
@IncludeColumnList VARCHAR(MAX) = NULL,
@ExcludeColumnList VARCHAR(MAX) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @OBJECTID INT,
@Object SYSNAME,
@schema SYSNAME,
@SQL VARCHAR(MAX)
SELECT @schema = s.name,
@Object = o.name,
@OBJECTID = o.OBJECT_ID
FROM sys.objects o (NOLOCK)
INNER JOIN sys.schemas s (NOLOCK) ON s.SCHEMA_ID = o.SCHEMA_ID
WHERE o.OBJECT_ID = OBJECT_ID(Util.dbo.TrimBothEnds(@TableName))
IF @OBJECTID IS NULL
BEGIN
EXEC master.dbo.sp_ExecTemplate
@ObjectName = 'sp_unpivot',
@PrintDefault = 0,
@DeclareReturn = 0,
@PrintReturnLine = 0,
@DoNotDeclareVar = 1
RETURN
END ;
WITH Incl
AS (SELECT Util.dbo.TrimBothEnds(PARSENAME(Field, 1)) AS ColumnName
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@IncludeColumnList, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''),
Excl
AS (SELECT Util.dbo.TrimBothEnds(PARSENAME(Field, 1)) AS ColumnName
FROM Util.dbo.ParseDelimited(REPLACE(REPLACE(REPLACE(@ExcludeColumnList, ' ', ','), ' ', ','), '
', ','), ',')
WHERE Field <> ''),
upv
AS (SELECT TOP 999999
co.name AS ColumnName,
cd.ColDef,
co.column_id
FROM sys.objects AS tb (NOLOCK)
INNER JOIN sys.columns AS co (NOLOCK) ON co.OBJECT_ID = tb.OBJECT_ID
INNER JOIN sys.types AS ts (NOLOCK) ON ts.user_type_id = co.user_type_id
CROSS APPLY (SELECT CAST(column_id AS VARCHAR) AS ColumnId,
CASE WHEN ts.name IN ('datetime2', 'datetime') THEN 'CONVERT(VARCHAR(30), up.' + co.name + ', 109)'
WHEN ts.name IN ('varchar', 'char', 'nvarchar', 'nchar') THEN 'up.' + co.name
WHEN ts.name LIKE '%binary%'
THEN 'CONVERT(varchar( ' + CAST(co.max_length * 2 + 2 AS VARCHAR) + '), up.' + co.name + ', 1)'
WHEN ts.name = 'XML' THEN 'CAST(up.' + co.name + ' AS VARCHAR(MAX))'
ELSE 'CAST(up.' + co.name + ' AS VARCHAR)'
END AS ColDef) cd
LEFT OUTER JOIN sys.indexes i (NOLOCK) ON i.OBJECT_ID = tb.OBJECT_ID
AND i.is_primary_key = 1
LEFT OUTER JOIN sys.index_columns ic (NOLOCK) ON ic.OBJECT_ID = i.OBJECT_ID
AND ic.index_id = i.index_id
AND ic.column_id = co.column_id
WHERE tb.OBJECT_ID = @OBJECTID
AND (NOT EXISTS ( SELECT *
FROM incl )
OR EXISTS ( SELECT *
FROM incl
WHERE columnname = co.NAME ))
AND (NOT EXISTS ( SELECT *
FROM Excl )
OR NOT EXISTS ( SELECT *
FROM Excl
WHERE columnname = co.NAME ))
ORDER BY ISNULL(ic.key_ordinal, 255),
co.name)
SELECT @SQL = 'SELECT
--''' + @schema + '.' + @Object + ''' as TableName,
' + CASE WHEN @IncludeColumnId = 1 THEN ' upv.ColumnId,
' ELSE ''
END + ' upv.ColumnName,
upv.ColumnValue
FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@Object) + ' up
CROSS APPLY(SELECT ' + CASE WHEN @IncludeColumnId = 1 THEN 'ColumnId, '
ELSE ''
END + 'ColumnName, ColumnValue
FROM (VALUES' + Util.dbo.StringConcat('(' + CASE WHEN @IncludeColumnId = 1 THEN CAST(column_id AS VARCHAR) + ', '
ELSE ''
END + '''' + ColumnName + ''', ' + ColDef + ')', ',
') + ')
AS dData(' + CASE WHEN @IncludeColumnId = 1 THEN 'ColumnId, '
ELSE ''
END + 'ColumnName, ColumnValue)) upv'
FROM upv
EXEC Util.dbo.PrintLargeText
@SQL
GO
EXEC sys.sp_ms_marksystemobject
sp_unpivot
GO