USE MASTER
GO
IF OBJECT_ID('dbo.sp_ProfileUnPivot') IS NULL
EXEC ('CREATE PROCEDURE dbo.sp_ProfileUnPivot AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_ProfileUnPivot
@ObjectSQL VARCHAR(MAX),
@PrintSQL BIT = 1,
@Execute BIT = 1,
@IgnoreNulls BIT = 0,
@ColumnListSQL VARCHAR(MAX) = NULL OUTPUT,
@SQL VARCHAR(MAX) = NULL OUTPUT,
@WhereClause VARCHAR(MAX) = NULL,
@IncludeColumnList VARCHAR(MAX) = NULL,
@ExcludeColumnList VARCHAR(MAX) = NULL,
@FilePath VARCHAR(1000) = NULL
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @ObjectName SYSNAME = CASE WHEN OBJECT_ID(@ObjectSQL) IS NOT NULL THEN PARSENAME(@ObjectSQL, 1)
END
DECLARE @Database SYSNAME = CASE WHEN @ObjectName IS NULL THEN 'tempdb'
ELSE ISNULL(PARSENAME(@ObjectSQL, 3), DB_NAME())
END
IF OBJECT_ID('TEMPDB..#COLS') IS NOT NULL
DROP TABLE #COLS
CREATE TABLE #COLS (SchemaName SYSNAME NOT NULL,
ObjectName SYSNAME NOT NULL,
ColumnId SMALLINT NOT NULL,
ColumnName SYSNAME NULL,
TypeName SYSNAME NOT NULL,
ColumnType VARCHAR(256) NULL,
is_nullable BIT NOT NULL,
max_length SMALLINT NOT NULL,
PRECISION TINYINT NOT NULL,
scale TINYINT NOT NULL,
COLLATION VARCHAR(128))
SET @SQL = CASE WHEN @ObjectName IS NULL THEN 'SELECT * INTO #temp FROM
(' + @ObjectSQL + ') k
WHERE 1 = 2
' ELSE ''
END + 'INSERT #COLS
SELECT
sc.Name AS SchemaName,
tb.Name AS ObjectName,
co.column_id AS ColumnId,
co.name AS ColumnName,
ts.name AS TypeName,
ct.ColumnType,
co.is_nullable,
co.max_length,
co.precision,
co.scale,
CASE WHEN co.collation_name <> db.collation_name THEN '' COLLATE '' + db.collation_name ELSE NULL END AS Collation
FROM ' + @Database + '.sys.schemas AS sc (NOLOCK)
INNER JOIN ' + @Database + '.sys.objects AS tb (NOLOCK) ON sc.schema_id = tb.schema_id
INNER JOIN ' + @Database + '.sys.columns AS co (NOLOCK) ON co.object_id = tb.object_id
INNER JOIN ' + @Database + '.sys.types AS ts (NOLOCK) ON ts.user_type_id = co.user_type_id
INNER JOIN ' + @Database + '.sys.databases AS db (NOLOCK) ON db.name = ''' + @Database + '''
CROSS APPLY Util.dbo.GetColumnType(ts.name, co.max_length, co.precision, co.scale, co.collation_name,
db.collation_name) ct
WHERE tb.object_id = OBJECT_ID(''' + CASE WHEN @ObjectName IS NULL THEN 'tempdb..#temp'
ELSE @ObjectSQL
END + ''')'
-- EXEC Util.dbo.PrintLargeText @sql
EXEC(@SQL)
SELECT @ColumnListSQL = 'SELECT CAST(ColumnId AS smallint) as ColumnId, ColumnName, CAST(ColumnType AS VARCHAR(128)) AS ColumnType, CAST(Nullable AS BIT) AS Nullable FROM (VALUES'
+ Util.dbo.StringConcat('(' + CAST(ColumnId AS VARCHAR) + ',''' + REPLACE(ColumnName, '''', '''''')
+ ''',' + '''' + ColumnType + ''',' + CAST(is_nullable AS VARCHAR) + ')', ',')
+ ')
AS Columns (ColumnId, ColumnName, ColumnType, Nullable)'
FROM #COLS ;
WITH Columns
AS (SELECT SchemaName,
ObjectName,
ColumnId,
ColumnName,
TypeName,
COLLATION
FROM #COLS
WHERE (ISNULL(@IncludeColumnList, '') = ''
OR EXISTS ( SELECT *
FROM Util.dbo.ParseDelimited(@IncludeColumnList, ',') b
WHERE b.Field = ColumnName ))
AND (ISNULL(@ExcludeColumnList, '') = ''
OR NOT EXISTS ( SELECT *
FROM Util.dbo.ParseDelimited(@ExcludeColumnList, ',') b
WHERE b.Field = ColumnName )))
SELECT @SQL = 'SELECT ct.ColumnId, ct.ColumnValue, COUNT(*) AS [Counter]
FROM ' + CASE WHEN @ObjectName IS NULL THEN '(' + @ObjectSQL + ') k'
ELSE @ObjectSQL + ' (NOLOCK)'
END + '
CROSS APPLY(SELECT ColumnId, ColumnValue
FROM (VALUES' + Util.dbo.StringConcat('(CAST(' + CAST(ColumnId AS VARCHAR) + ' AS smallint), '
+ CASE WHEN TypeName LIKE '%char' THEN '[' + ColumnName + ']'
ELSE 'CAST([' + ColumnName + '] AS VARCHAR)'
END + ISNULL(COLLATION, '') + ')', ',
') + ') AS ddata (ColumnId, ColumnValue)) ct' + ISNULL('
' + CASE WHEN @WhereClause NOT LIKE '%WHERE%' THEN 'WHERE ' + @WhereClause
ELSE @WhereClause
END, '') + '
GROUP BY ct.ColumnId, ct.ColumnValue
/* ORDER BY ct.ColumnId, ct.ColumnValue */'
FROM Columns
GROUP BY SchemaName,
ObjectName
IF @PrintSQL = 1
BEGIN
EXEC Util.dbo.PrintLargeText
@ColumnListSQL
PRINT '--------------------------------------'
EXEC Util.dbo.PrintLargeText
@SQL
END
IF @Execute = 1
EXEC(@SQL)
IF @FilePath <> ''
SELECT ReturnVal,
MESSAGE,
'Saved to ' + @FilePath AS Info
FROM Util.FS.AppendAllTextToFile(@FilePath, @SQL, 1)
GO
EXEC sys.sp_ms_marksystemobject
sp_ProfileUnPivot
GO