USE master
GO
IF OBJECT_ID('sp_Profile') IS NULL EXEC ('CREATE PROCEDURE sp_Profile AS SELECT 1 AS ID')
GO
ALTER PROCEDURE dbo.sp_Profile
@ObjectSQL VARCHAR(256),
@PrintSQL BIT = 0
AS
SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DECLARE @Postfix VARCHAR(30) = LEFT(REPLACE(CAST(NEWID() AS VARCHAR(256)), '-', ''), 10)
DECLARE @TemporaryProfileTable VARCHAR(128) = 'Temporary..Profile_' + @Postfix,
@TemporaryColumnTable VARCHAR(128) = 'Temporary..ProfileColumn_' + @Postfix,
@TemporaryProfileSummary VARCHAR(128) = 'Temporary..ProfileSummary_' + @Postfix
DECLARE @SQL VARCHAR(MAX),
@ColumnListSQL VARCHAR(MAX)
EXEC dbo.sp_ProfileUnPivot
@ObjectSQL = @ObjectSQL,
@PrintSQL = 0,
@Execute = 0,
@IgnoreNulls = 0,
@ColumnListSQL = @ColumnListSQL OUTPUT,
@SQL = @SQL OUTPUT,
@WhereClause = NULL,
@IncludeColumnList = NULL,
@ExcludeColumnList = NULL
SET @SQL = 'SELECT * INTO ' + @TemporaryColumnTable + '
FROM (' + @ColumnListSQL + ') c
CREATE UNIQUE CLUSTERED INDEX ColumnId ON ' + @TemporaryColumnTable + ' (ColumnId)
CREATE UNIQUE NONCLUSTERED INDEX ColumnName ON ' + @TemporaryColumnTable + ' (ColumnName)
SELECT * INTO ' + @TemporaryProfileTable + '
FROM (' + @SQL + '
) k'
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
EXEC(@SQL)
SET @SQL = 'CREATE UNIQUE CLUSTERED INDEX TEMP ON ' + @TemporaryProfileTable
+ ' (ColumnId, ColumnValue) WITH (DATA_COMPRESSION = ROW)'
IF @PrintSQL = 1
PRINT @SQL
EXEC(@SQL)
SET @SQL = ';WITH STEP1
AS (SELECT a.ColumnId,
COUNT(*) AS DistinctValues,
SUM(Counter) AS Rows,
SUM(CASE WHEN LEN(ColumnValue) = 0 THEN Counter
ELSE 0
END) AS StringIsEmpty,
SUM(CASE WHEN ColumnValue LIKE '' %'' THEN 1
ELSE 0
END) AS StartWithSpaceVal,
SUM(CASE WHEN ColumnValue IS NULL THEN Counter
ELSE 0
END) AS NullCount,
SUM(CASE WHEN ColumnValue IS NOT NULL THEN Counter
ELSE 0
END) AS NonNullCount,
SUM(CASE WHEN ColumnValue = ''NULL'' THEN 1
ELSE 0
END) AS StringEqNull,
MAX(LEN(ColumnValue)) AS MaxLength,
SUM(CASE WHEN ColumnValue NOT IN ('''', ''NULL'')
AND DateTimeVal IS NULL THEN 1
ELSE 0
END) AS NonDateVal,
SUM(CASE WHEN ColumnValue NOT IN ('''', ''NULL'')
AND rd.Value IS NULL THEN 1
ELSE 0
END) AS NonNumericVal,
SUM(CASE WHEN rd.Value IS NOT NULL
AND ColumnValue LIKE ''0%'' THEN 1
ELSE 0
END) AS NumValStartWithZero,
SUM(CASE WHEN rd.Value = 0 THEN Counter ELSE 0 END) as ZeroCount,
MIN(rd.Value) AS MinNumeric,
MAX(rd.Value) AS MaxNumeric,
MAX(rd.DigitsLeft) AS MaxIntegerDigits,
MAX(rd.DigitsRight) AS MaxScaleDigits,
SUM(CASE WHEN rd.Type = 1 THEN 1
ELSE 0
END) AS BitCount,
SUM(CASE WHEN rd.Type = 2 THEN 1
ELSE 0
END) AS TinyintCount,
SUM(CASE WHEN rd.Type = 3 THEN 1
ELSE 0
END) AS SmallintCount,
SUM(CASE WHEN rd.Type = 4 THEN 1
ELSE 0
END) AS IntCount,
SUM(CASE WHEN rd.Type = 5 THEN 1
ELSE 0
END) AS BigIntCount,
SUM(CASE WHEN rd.Type = 6 THEN 1
ELSE 0
END) AS NumericCount,
MIN(DateTimeVal) AS MinDate,
MAX(DateTimeVal) AS MaxDate
FROM ' + @TemporaryProfileTable + ' a (NOLOCK)
OUTER APPLY (SELECT Value,
TypeName,
Type,
DigitsLeft,
DigitsRight
FROM Util.[dbo].[ResolveDecimal](ColumnValue)
WHERE ColumnValue <> '''') rd
CROSS APPLY (SELECT CASE WHEN ColumnValue <> '''' THEN Util.dbo.TryParseDateTime(ColumnValue)
END AS DateTimeVal) t
GROUP BY a.ColumnId)
SELECT CASE WHEN DistinctValues = Rows THEN ''YES''
ELSE ''''
END AS IsUnique,
c.ColumnName,
c.ColumnType,
c.Nullable,
Rows,
DistinctValues,
CAST(CASE WHEN Rows > 0 THEN DistinctValues * 100.0 / Rows
ELSE 0
END AS NUMERIC(6, 2)) AS [Distinct%],
NullCount,
NonNullCount,
CAST(CASE WHEN Rows > 0 THEN NonNullCount * 100.0 / Rows
ELSE 0
END AS NUMERIC(6, 2)) AS [NonNull%],
StringEqNull,
StringIsEmpty,
StartWithSpaceVal,
MaxLength,
NonDateVal,
NonNumericVal,
NumValStartWithZero,
ZeroCount,
MinNumeric,
MaxNumeric,
MaxIntegerDigits,
MaxScaleDigits,
(SELECT Util.dbo.StringConcat(Type + '': '' + CAST(Rows AS VARCHAR), '', '')
FROM (SELECT TOP 999
*
FROM (SELECT BitCount AS Rows,
''Bit'' AS Type
WHERE BitCount > 0
UNION ALL
SELECT TinyIntCount AS Rows,
''TinyInt'' AS Type
WHERE TinyIntCount > 0
UNION ALL
SELECT SmallIntCount AS Rows,
''SmallInt'' AS Type
WHERE SmallIntCount > 0
UNION ALL
SELECT IntCount AS Rows,
''Int'' AS Type
WHERE IntCount > 0
UNION ALL
SELECT BigIntCount AS Rows,
''BigInt'' AS Type
WHERE BigIntCount > 0
UNION ALL
SELECT NumericCount AS Rows,
''Numeric'' AS Type
WHERE NumericCount > 0) k
/*ORDER BY Rows DESC*/) k) AS NumericBreakDown,
MinDate,
MaxDate,
CASE WHEN DistinctValues < 20 THEN (SELECT Util.dbo.StringConcat(ColumnValue, ''|'')
FROM ' + @TemporaryProfileTable + ' a
WHERE a.ColumnId = b.ColumnId)
ELSE NULL
END AS ValueListUpToTwenty,
(SELECT TOP 1
a.ColumnValue
FROM ' + @TemporaryProfileTable + ' a
WHERE a.ColumnId = b.ColumnId
ORDER BY LEN(a.ColumnValue) DESC) AS LongestVal,
b.ColumnId
INTO ' + @TemporaryProfileSummary + '
FROM STEP1 b
INNER JOIN ' + @TemporaryColumnTable + ' c (NOLOCK) ON c.ColumnId = b.ColumnId
ORDER BY c.ColumnName'
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
EXEC(@SQL)
SELECT @SQL = '-- Profile Summary
SELECT * FROM ' + @TemporaryProfileSummary + '
-- Profile Detail
SELECT TOP 1000 b.ColumnName, a.ColumnValue, a.Counter
FROM ' + @TemporaryProfileTable + ' a
INNER JOIN ' + @TemporaryColumnTable + ' b ON a.ColumnId = b.ColumnId
ORDER BY b.ColumnName, ColumnValue'
SELECT Field AS '-- !!!! COPY THIS SQL TO ANALYZE FURTHER !!!! --'
FROM Util.dbo.ParseDelimited(@SQL, '
')
IF @PrintSQL = 1
EXEC Util.dbo.PrintLargeText
@SQL
EXEC(@SQL)
GO
EXEC sys.sp_ms_marksystemobject sp_Profile
GO