Where's the Metadata Part 2 - Relational Database Management Systems
Updated on July 07, 2026.
Reading time:
67 minutes
This article is a continuation of "Where's the Metadata?", focusing on obtaining Metadata from a Relational Database Management Systems (RDMS). I will cover:
I've included sample scripts for collecting basic Metadata from a MS SQL Server database. (Other RDMS, like PostgreSQL or MySQL, will be similar for the INFORMATION_SCHEMA tables, but the use of MS_Description field or sys tables will be specific to MS SQL Server).
Table Information
This script will get you all the details about eachTable, including where it is a View, the MS_Description, a count of all Rows in the table, and the number of Columns in the table.
Pro Tip: If you plan to log the Row count on a regular basis to monitor growth of a certain dataset, you will want to capture the date of this script was ran.
/** Retrieve information for all Tables in Schema **/
DECLARE @SchemaName VARCHAR(50) = 'dbo';
SELECT
t.TABLE_CATALOG as [Database],
t.TABLE_SCHEMA as [Schema],
t.TABLE_NAME as [Table],
CAST(CASE
WHEN TABLE_TYPE = 'VIEW' then 1
ELSE 0
END AS BIT) as [IsView],
ex.value [Description],
p.rows [RowCount],
count(c.COLUMN_NAME) [Columns],
GETUTCDATE() [DateCapturedUtc]
FROM
INFORMATION_SCHEMA.TABLES as t
LEFT JOIN
sys.extended_properties as ex
ON ex.major_id = OBJECT_ID(t.TABLE_SCHEMA+'.'+t.TABLE_NAME) AND minor_id = 0 AND ex.name = 'MS_Description'
LEFT JOIN
sys.partitions as p on p.object_id= OBJECT_ID(t.TABLE_SCHEMA+'.'+t.TABLE_NAME) and p.index_id = 1
LEFT JOIN
INFORMATION_SCHEMA.COLUMNS as c on t.TABLE_NAME = c.TABLE_NAME and t.TABLE_SCHEMA = t.TABLE_SCHEMA and t.TABLE_CATALOG = c.TABLE_CATALOG
WHERE
t.TABLE_SCHEMA = @SchemaName
GROUP BY
t.TABLE_CATALOG,
t.TABLE_SCHEMA,
t.TABLE_NAME,
t.TABLE_TYPE,
ex.value,
p.rows
ORDER BY
[Database], [Schema], [Table]
GO| Database | Schema | Table | IsView | Description |
RowCount | Columns | DateCapturedUtc |
|---|---|---|---|---|---|---|---|
| BI | dbo | Products | 0 | Catalog of Products | 96 | 6 | 2023-11-06 22:08:01.043 |
| BI | dbo | Orders | 0 | Order Transactions | 2536 | 8 | 2023-11-06 22:08:01.043 |
| BI | dbo | Customers | 0 | Catalog of Customers | 120 | 12 | 2023-11-06 22:08:01.043 |
| BI | dbo | Territories | 0 | List of Sales Territories | 5 | 3 | 2023-11-06 22:08:01.043 |
| BI | dbo | vw_CustomerOrders_NW | 1 | View of Customer Orders in the North West Sales Territory | 585 | 17 | 2023-11-06 22:08:01.043 |
Table Column information
This script will get you all the details about each Column in a Table, including the Data Type, Precision, Default Value, whether it allows NULLS, and the MS_Description
DECLARE @SchemaName VARCHAR(50) = 'dbo';
DECLARE @TableName VARCHAR(50) = 'table_name';
SELECT
c.TABLE_SCHEMA as [Schema],
c.TABLE_NAME as [Table],
c.COLUMN_NAME as [Column],
CASE
WHEN c.DATA_TYPE in ('int','datetime','date','time','money','bit','float','uniqueidentifier') THEN UPPER(c.DATA_TYPE)
WHEN c.DATA_TYPE in ('numeric','decimal') THEN CONCAT(UPPER(c.DATA_TYPE), '(',c.NUMERIC_PRECISION,',',c.NUMERIC_SCALE,')')
WHEN c.DATA_TYPE in ('datetime2') THEN CONCAT(UPPER(c.DATA_TYPE), '(',c.DATETIME_PRECISION,')')
ELSE CONCAT(UPPER(c.DATA_TYPE), '(',iif(c.CHARACTER_MAXIMUM_LENGTH = -1,'max',CAST(CHARACTER_MAXIMUM_LENGTH AS VARCHAR)),')')
END [ColumnType],
REPLACE(REPLACE(
c.COLUMN_DEFAULT
,'(',''),')','')
[DefaultValue],
c.IS_NULLABLE [AllowNulls],
p.value [Description]
FROM
INFORMATION_SCHEMA.COLUMNS as c
LEFT JOIN
sys.extended_properties as p
ON p.major_id = OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME) AND p.minor_id = c.ORDINAL_POSITION AND p.name = 'MS_Description'
WHERE
OBJECTPROPERTY(OBJECT_ID(c.TABLE_SCHEMA+'.'+c.TABLE_NAME), 'IsMsShipped')=0
AND
c.TABLE_NAME = @TableName
AND c.TABLE_SCHEMA = @SchemaName
ORDER BY
[Schema], [Table], c.ORDINAL_POSITION| Schema | Table | Column | DataType | DefaultValue | AllowNulls | Description |
|---|---|---|---|---|---|---|
| dbo | table_name | Id | INT | NULL | NO | The unique ID for the Entity |
| dbo | table_name | Name | NVARCHAR(200) | NULL | NO | The Full Name of the Entity |
| dbo | table_name | Type | VARCHAR(50) | NULL | YES | The Type of the Entity. Values are "Place", "Person", "Thing" |
| dbo | table_name | Active | BIT | 0 | NO | Whether or not the Entity is currently Active. |
Data Profiling
Data Profiling will give you a glimpse into how well your SQL schema fits the data (to determine if any optimization is needed), and what is the quality of your data. For example, you might want to know the min/max value, the average value, the number of NULLs (completeness), number of distinct values, etc.
The query below was originally based on Data Profiling with T-SQL (SQL Server Central), with some additional features I've added. Along with your data profile, it will also provide actionable recommendations! For example:
- Seems empty - is it required?
- There is a large percentage of NULLs - attention may be required
- Few distinct elements - potential for reference/lookup table (contains NULLs)
- Few distinct elements - potential for Foreign Key.
- Possibly could be SMALLINT type.
- Could benefit from sparse columns.
- Possibly could be DATE type
- Possibly could be one of the NUMERIC types.
- All non-null values are boolean-like - possible BIT type
I have added parameters the query to allow sampling for large tables.
[ui-tabs position="top-left" active="0" theme="lite"] [ui-tab title="MS SQL"]
-----------------------------------------------------------------------
-- User-defined variables
-----------------------------------------------------------------------
USE Products -- Your database here
GO
DECLARE @TABLE_SCHEMATABLE_SCHEMA NVARCHAR(128) = 'dbo' -- Your schema here
DECLARE @TABLE_NAME NVARCHAR(128) = 'Products' -- Your table here
-- Sampling options: set @UseSample = 1 to profile only a sample of rows
DECLARE @UseSample BIT = 0 -- 1 = use sampling, 0 = profile whole table
DECLARE @SampleRows BIGINT = 200000 -- Number of rows to sample when @UseSample = 1
DECLARE @SkipRows BIGINT = 0 -- Number of rows to skip before sampling (useful for paging)
DECLARE @DataSource NVARCHAR(400) = '' -- Will contain either '#SampleData' or the fully qualified table name + WITH (NOLOCK)
DECLARE @ColumnListIN NVARCHAR(4000) = '' -- Enter a comma-separated list of specific columns to profile, or leave blank for all
DECLARE @TextCol BIT = 1 -- Analyse all text (char/varchar/nvarchar) data type columns
DECLARE @NumCol BIT = 1 -- Analyse all numeric data type columns
DECLARE @DateCol BIT = 1 -- Analyse all date data type data type columns
DECLARE @LobCol BIT = 1 -- Analyse all VAR(char/nchar/binary) MAX data type columns (potentially time-consuming)
DECLARE @AdvancedAnalysis BIT = 0 -- Perform advanced analysis (threshold counts/domain analysis) (potentially time-consuming)
DECLARE @DistinctValuesMinimum INT = 200 -- Minimum number of distinct values to suggest a reference table and/or perform domain analysis
DECLARE @BoundaryPercent NUMERIC(3,2) = 0.57 -- Percent of records at upper/lower threshold to suggest a possible anomaly
DECLARE @NullBoundaryPercent NUMERIC(5,2) = 90.00 -- Percent of NULLs to suggest a possible anomaly
DECLARE @DataTypePercentage INT = 2 -- Percentage variance allowed when suggesting another data type for a column
-----------------------------------------------------------------------
-- Process variables
-----------------------------------------------------------------------
DECLARE @DATA_TYPE VARCHAR(128) = ''
DECLARE @FULLSQL VARCHAR(MAX) = ''
DECLARE @SQLMETADATA VARCHAR(MAX) = ''
DECLARE @NUMSQL VARCHAR(MAX) = ''
DECLARE @DATESQL VARCHAR(MAX) = ''
DECLARE @LOBSQL VARCHAR(MAX) = ''
DECLARE @COLUMN_NAME VARCHAR(128)
DECLARE @CHARACTER_MAXIMUM_LENGTH INT
DECLARE @ROWCOUNT BIGINT = 0
DECLARE @ColumnList VARCHAR(4000) = ' '
DECLARE @TableCheck TINYINT
DECLARE @ColumnCheck SMALLINT
DECLARE @DataTypeVariance INT
SET NOCOUNT ON;
-----------------------------------------------------------------------
-- Start the process:
BEGIN TRY
-- Test that the schema and table exist
SELECT @TableCheck = COUNT (*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = @TABLE_SCHEMA AND TABLE_NAME = @TABLE_NAME
IF @TableCheck <> 1
BEGIN
RAISERROR ('The table does not exist',16,1)
RETURN
END
-----------------------------------------------------------------------
-- Parse list of columns to process / get list of columns according to types required
-----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#ColumnList') IS NOT NULL
DROP TABLE tempdb..#ColumnList;
CREATE TABLE #ColumnList (
COLUMN_NAME VARCHAR(128) COLLATE DATABASE_DEFAULT,
DATA_TYPE VARCHAR(128) COLLATE DATABASE_DEFAULT,
CHARACTER_MAXIMUM_LENGTH INT
) -- Used to hold list of columns to process
IF @ColumnListIN <> '' -- See if there is a list of columns to process
BEGIN
-- Process list
SET @ColumnList = @ColumnListIN + ','
DECLARE @CharPosition int
WHILE CHARINDEX(',', @ColumnList) > 0
BEGIN
SET @CharPosition = CHARINDEX(',', @ColumnList)
INSERT INTO #ColumnList (COLUMN_NAME) VALUES (LTRIM(RTRIM(LEFT(@ColumnList, @CharPosition - 1))))
SET @ColumnList = STUFF(@ColumnList, 1, @CharPosition, '')
END -- While loop
-- update with datatype and length
UPDATE CL
SET CL.CHARACTER_MAXIMUM_LENGTH = ISNULL(ISC.CHARACTER_MAXIMUM_LENGTH,0)
,CL.DATA_TYPE = ISC.DATA_TYPE
FROM #ColumnList CL
INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
ON CL.COLUMN_NAME = ISC.COLUMN_NAME
WHERE ISC.TABLE_NAME = @TABLE_NAME
AND ISC.TABLE_SCHEMA = @TABLE_SCHEMA
END -- If test for list of column names
ELSE
BEGIN -- Use all column names, to avoid filtering
IF @TextCol = 1
BEGIN
INSERT INTO #ColumnList (COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH)
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar', 'binary')
AND TABLE_NAME = @TABLE_NAME
AND TABLE_SCHEMA = @TABLE_SCHEMA
AND CHARACTER_MAXIMUM_LENGTH > 0
END
IF @NumCol = 1
BEGIN
INSERT INTO #ColumnList (COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH)
SELECT COLUMN_NAME, DATA_TYPE, ISNULL(CHARACTER_MAXIMUM_LENGTH,0) FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('numeric', 'int', 'bigint', 'tinyint', 'smallint', 'decimal', 'money', 'smallmoney', 'float','real')
AND TABLE_NAME = @TABLE_NAME
AND TABLE_SCHEMA = @TABLE_SCHEMA
END
IF @DateCol = 1
BEGIN
INSERT INTO #ColumnList (COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH)
SELECT COLUMN_NAME, DATA_TYPE, ISNULL(CHARACTER_MAXIMUM_LENGTH,0) FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('Date', 'DateTime', 'SmallDateTime', 'DateTime2', 'time')
AND TABLE_NAME = @TABLE_NAME
AND TABLE_SCHEMA = @TABLE_SCHEMA
END
IF @LOBCol = 1
BEGIN
INSERT INTO #ColumnList (COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH)
SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ('varchar', 'nvarchar', 'varbinary', 'xml')
AND TABLE_NAME = @TABLE_NAME
AND TABLE_SCHEMA = @TABLE_SCHEMA
AND CHARACTER_MAXIMUM_LENGTH = -1
END
END -- Else test to get all column names
-----------------------------------------------------------------------
-- Test that there are columns to analyse
SELECT @ColumnCheck = COUNT (*) FROM #ColumnList WHERE DATA_TYPE IS NOT NULL
IF @ColumnCheck = 0
BEGIN
RAISERROR ('The columns do not exist in the selected database or no columns are selected',16,1)
RETURN
END
-----------------------------------------------------------------------
-- Create Temp table used to hold profiling data
-----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#ProfileData') IS NOT NULL
DROP TABLE tempdb..#ProfileData;
CREATE TABLE #ProfileData
(
TABLE_SCHEMA NVARCHAR(128) COLLATE DATABASE_DEFAULT
,TABLE_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
,COLUMN_NAME NVARCHAR(128) COLLATE DATABASE_DEFAULT
,ColumnDataLength INT
,DataType VARCHAR(128) COLLATE DATABASE_DEFAULT
,MinDataLength BIGINT
,MaxDataLength BIGINT
,AvgDataLength BIGINT
,MinDate SQL_VARIANT
,MaxDate SQL_VARIANT
,MinNumeric SQL_VARIANT NULL
,MaxNumeric SQL_VARIANT NULL
,NoDistinct BIGINT
,NoNulls BIGINT
,NoZeroLength BIGINT
,PercentageNulls NUMERIC(9,4)
,PercentageZeroLength NUMERIC(9,4)
,NoDateWithHourminuteSecond BIGINT NULL
,NoDateWithSecond BIGINT NULL
,NoIsNumeric BIGINT NULL
,NoIsDate BIGINT NULL
,NoAtLimit BIGINT NULL
,IsFK BIT NULL DEFAULT 0
,DataTypeComments NVARCHAR(1500)
);
-- Get row count
DECLARE @ROWCOUNTTEXT NVARCHAR(1000) = ''
DECLARE @ROWCOUNTPARAM NVARCHAR(50) = ''
SET @ROWCOUNTTEXT = 'SELECT @ROWCOUNTOUT = COUNT (*) FROM ' + QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME) + ' WITH (NOLOCK)'
SET @ROWCOUNTPARAM = '@ROWCOUNTOUT INT OUTPUT'
EXECUTE sp_executesql @ROWCOUNTTEXT, @ROWCOUNTPARAM, @ROWCOUNTOUT = @ROWCOUNT OUTPUT
-----------------------------------------------------------------------
-- Test that there are records to analyse
IF @ROWCOUNT = 0
BEGIN
RAISERROR ('There is no data in the table to analyse',16,1)
RETURN
END
-- Prepare sampling data source if requested
IF @UseSample = 1
BEGIN
IF OBJECT_ID('tempdb..##SampleData') IS NOT NULL
DROP TABLE tempdb..##SampleData;
-- Try to use TABLESAMPLE (SYSTEM_ROWS) for a fast sample when supported. Fall back to TOP ... ORDER BY NEWID() if not.
-- Build column lists and create sample table without preserving IDENTITY property
DECLARE @CreateColsList NVARCHAR(MAX) = ''
DECLARE @SelectColsList NVARCHAR(MAX) = ''
-- Build select column list
SELECT @SelectColsList = STUFF((
SELECT ',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@TABLE_SCHEMA + '.' + @TABLE_NAME)
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,1,'')
-- Build create column expression list, casting identity columns so identity property is not preserved
SELECT @CreateColsList = STUFF((
SELECT ',' +
CASE WHEN c.is_identity = 1 THEN
'CAST(' + QUOTENAME(c.name) + ' AS ' + t.name +
CASE WHEN t.name IN ('varchar','char','varbinary') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length AS VARCHAR(10)) END + ')'
WHEN t.name IN ('nvarchar','nchar') THEN '(' + CASE WHEN c.max_length = -1 THEN 'MAX' ELSE CAST(c.max_length/2 AS VARCHAR(10)) END + ')'
WHEN t.name IN ('decimal','numeric') THEN '(' + CAST(c.precision AS VARCHAR(10)) + ',' + CAST(c.scale AS VARCHAR(10)) + ')'
ELSE '' END + ') AS ' + QUOTENAME(c.name)
ELSE QUOTENAME(c.name) END
FROM sys.columns c
JOIN sys.types t ON c.user_type_id = t.user_type_id
WHERE c.object_id = OBJECT_ID(@TABLE_SCHEMA + '.' + @TABLE_NAME)
ORDER BY c.column_id
FOR XML PATH(''), TYPE).value('.','nvarchar(max)'),1,1,'')
IF @CreateColsList IS NULL OR @SelectColsList IS NULL
BEGIN
RAISERROR('Unable to build column lists for sampling table',16,1)
RETURN
END
DECLARE @CreateSampleSQL NVARCHAR(MAX) = 'SELECT TOP 0 ' + @CreateColsList + ' INTO ##SampleData FROM ' + QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME) + ' WITH (NOLOCK)'
EXEC (@CreateSampleSQL)
-- Detect an ordering column to support skipping rows (prefer PK, then identity, then first column)
DECLARE @OrderColumn NVARCHAR(128) = NULL
SELECT TOP 1 @OrderColumn = c.name
FROM sys.indexes i
JOIN sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE i.object_id = OBJECT_ID(@TABLE_SCHEMA + '.' + @TABLE_NAME) AND i.is_primary_key = 1
IF @OrderColumn IS NULL
BEGIN
SELECT TOP 1 @OrderColumn = name FROM sys.columns WHERE object_id = OBJECT_ID(@TABLE_SCHEMA + '.' + @TABLE_NAME) AND is_identity = 1
END
IF @OrderColumn IS NULL
BEGIN
SELECT TOP 1 @OrderColumn = name FROM sys.columns WHERE object_id = OBJECT_ID(@TABLE_SCHEMA + '.' + @TABLE_NAME) ORDER BY column_id
END
IF @SkipRows > 0
BEGIN
-- Use deterministic ROW_NUMBER() sampling when skipping rows is requested
DECLARE @RowSampleSQL NVARCHAR(MAX) = 'INSERT INTO ##SampleData (' + @SelectColsList + ') '
+ 'SELECT ' + @SelectColsList + ' FROM (SELECT ' + @SelectColsList + ', ROW_NUMBER() OVER (ORDER BY ' + QUOTENAME(@OrderColumn) + ') AS rn FROM ' + QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME) + ' WITH (NOLOCK)) t WHERE rn > ' + CAST(@SkipRows AS VARCHAR(20)) + ' AND rn <= ' + CAST((@SkipRows + @SampleRows) AS VARCHAR(20))
EXEC (@RowSampleSQL)
END
ELSE
BEGIN
BEGIN TRY
-- Try TABLESAMPLE by rows (may not be supported for all table types); this is non-deterministic but fast
DECLARE @InsertSampleSQL NVARCHAR(MAX) = 'INSERT INTO ##SampleData (' + @SelectColsList + ') SELECT ' + @SelectColsList + ' FROM ' + QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME) + ' TABLESAMPLE ( ' + CAST(@SampleRows AS VARCHAR(20)) + ' ROWS)'
EXEC (@InsertSampleSQL)
-- If TABLESAMPLE returned zero rows (rare), fall back
IF (SELECT COUNT(*) FROM ##SampleData) = 0
BEGIN
DELETE FROM ##SampleData
DECLARE @InsertFallback1 NVARCHAR(MAX) = 'INSERT INTO ##SampleData (' + @SelectColsList + ') SELECT TOP (' + CAST(@SampleRows AS VARCHAR(20)) + ') ' + @SelectColsList + ' FROM ' + QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME) + ' WITH (NOLOCK) ORDER BY NEWID()'
EXEC(@InsertFallback1)
END
END TRY
BEGIN CATCH
-- TABLESAMPLE may not be available; fall back to random TOP sampling
DELETE FROM ##SampleData
DECLARE @FallbackSQL NVARCHAR(MAX) = 'INSERT INTO ##SampleData (' + @SelectColsList + ') SELECT TOP (' + CAST(@SampleRows AS VARCHAR(20)) + ') ' + @SelectColsList + ' FROM ' + QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME) + ' WITH (NOLOCK) ORDER BY NEWID()'
EXEC (@FallbackSQL)
END CATCH
END
-- Use the sample as data source for profiling
SET @DataSource = '##SampleData'
-- Adjust row count to be the sampled row count for percentage calculations
SET @ROWCOUNT = (SELECT COUNT(*) FROM ##SampleData)
END
ELSE
BEGIN
-- Use full table as data source
SET @DataSource = QUOTENAME(@TABLE_SCHEMA) + '.' + QUOTENAME(@TABLE_NAME) + ' WITH (NOLOCK)'
END
-----------------------------------------------------------------------
-- Define the dynamic SQL used for each column to analyse
-----------------------------------------------------------------------
SET @SQLMETADATA = 'INSERT INTO #ProfileData (ColumnDataLength,COLUMN_NAME,TABLE_SCHEMA,TABLE_NAME,DataType,MaxDataLength,MinDataLength,AvgDataLength,MaxDate,MinDate,MinNumeric,MaxNumeric,NoDateWithHourminuteSecond,NoDateWithSecond,NoIsNumeric,NoIsDate,NoNulls,NoZeroLength,NoDistinct)'
DECLARE SQLMETADATA_CUR CURSOR LOCAL FAST_FORWARD FOR
SELECT COLUMN_NAME, CHARACTER_MAXIMUM_LENGTH, DATA_TYPE FROM #ColumnList
OPEN SQLMETADATA_CUR
FETCH NEXT FROM SQLMETADATA_CUR INTO @COLUMN_NAME, @CHARACTER_MAXIMUM_LENGTH, @DATA_TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQLMETADATA = @SQLMETADATA +'
SELECT TOP 100 PERCENT
' + CAST(@CHARACTER_MAXIMUM_LENGTH AS VARCHAR(20)) + '
,''' + QUOTENAME(@COLUMN_NAME) + '''
,''' + QUOTENAME(@TABLE_SCHEMA) + '''
,''' + QUOTENAME(@TABLE_NAME) + '''
,''' + @DATA_TYPE + ''''
+ CASE
WHEN @DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar') AND @CHARACTER_MAXIMUM_LENGTH >= 0 THEN + '
,MAX(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,MIN(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,AVG(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,(SELECT MAX(TRY_CONVERT(DATETIME, ' + QUOTENAME(@COLUMN_NAME) + ')) FROM ' + @DataSource + ')
,(SELECT MIN(TRY_CONVERT(DATETIME, ' + QUOTENAME(@COLUMN_NAME) + ')) FROM ' + @DataSource + ')
,(SELECT MIN(TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ')) FROM ' + @DataSource + ' WHERE TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ') IS NOT NULL)
,(SELECT MAX(TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ')) FROM ' + @DataSource + ' WHERE TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ') IS NOT NULL)
,NULL
,NULL
,(SELECT COUNT (*) from ' + @DataSource + ' WHERE TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ') IS NOT NULL)
,(SELECT COUNT (*) from ' + @DataSource + ' WHERE TRY_CONVERT(DATETIME, ' + QUOTENAME(@COLUMN_NAME) + ') IS NOT NULL) '
WHEN @DATA_TYPE IN ('numeric', 'int', 'bigint', 'tinyint', 'smallint', 'decimal', 'money', 'smallmoney', 'float','real') THEN + '
,MAX(' + QUOTENAME(@COLUMN_NAME) + ')
,MIN(' + QUOTENAME(@COLUMN_NAME) + ')
,AVG(CAST(' + QUOTENAME(@COLUMN_NAME) + ' AS NUMERIC(36,2)))
,NULL
,NULL
,MIN(' + QUOTENAME(@COLUMN_NAME) + ')
,MAX(' + QUOTENAME(@COLUMN_NAME) + ')
,NULL
,NULL
,NULL
,NULL '
WHEN @DATA_TYPE IN ('DateTime', 'SmallDateTime') THEN + '
,NULL
,NULL
,NULL
,MAX(' + QUOTENAME(@COLUMN_NAME) + ')
,MIN(' + QUOTENAME(@COLUMN_NAME) + ')
,NULL
,NULL
,(SELECT COUNT (*) from ' + @DataSource + ' WHERE (CONVERT(NUMERIC(20,12), ' + QUOTENAME(@COLUMN_NAME) + ' ) - FLOOR(CONVERT(NUMERIC(20,12), ' + QUOTENAME(@COLUMN_NAME) + ')) <> 0))
,(SELECT COUNT (*) from ' + @DataSource + ' WHERE DATEPART(ss,' + QUOTENAME(@COLUMN_NAME) + ') <> 0 OR DATEPART(mcs,' + QUOTENAME(@COLUMN_NAME) + ') <> 0)
,NULL
,NULL '
WHEN @DATA_TYPE IN ('DateTime2') THEN + '
,NULL
,NULL
,NULL
,MAX(' + QUOTENAME(@COLUMN_NAME) + ')
,MIN(' + QUOTENAME(@COLUMN_NAME) + ')
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL '
WHEN @DATA_TYPE IN ('Date') THEN + '
,NULL
,NULL
,NULL
,MAX(' + QUOTENAME(@COLUMN_NAME) + ')
,MIN(' + QUOTENAME(@COLUMN_NAME) + ')
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL '
WHEN @DATA_TYPE IN ('xml') THEN + '
,MAX(LEN(CAST(' + QUOTENAME(@COLUMN_NAME) + ' AS NVARCHAR(MAX))))
,MIN(LEN(CAST(' + QUOTENAME(@COLUMN_NAME) + ' AS NVARCHAR(MAX))))
,AVG(LEN(CAST(' + QUOTENAME(@COLUMN_NAME) + ' AS NVARCHAR(MAX))))
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL '
WHEN @DATA_TYPE IN ('varchar','nvarchar') AND @CHARACTER_MAXIMUM_LENGTH = -1 THEN + '
,MAX(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,MIN(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,AVG(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,(SELECT MAX(TRY_CONVERT(DATETIME, ' + QUOTENAME(@COLUMN_NAME) + ')) FROM ' + @DataSource + ')
,(SELECT MIN(TRY_CONVERT(DATETIME, ' + QUOTENAME(@COLUMN_NAME) + ')) FROM ' + @DataSource + ')
,(SELECT MIN(TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ')) FROM ' + @DataSource + ' WHERE TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ') IS NOT NULL)
,(SELECT MAX(TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ')) FROM ' + @DataSource + ' WHERE TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ') IS NOT NULL)
,NULL
,NULL
,(SELECT COUNT (*) from ' + @DataSource + ' WHERE TRY_CONVERT(FLOAT, ' + QUOTENAME(@COLUMN_NAME) + ') IS NOT NULL)
,(SELECT COUNT (*) from ' + @DataSource + ' WHERE TRY_CONVERT(DATETIME, ' + QUOTENAME(@COLUMN_NAME) + ') IS NOT NULL) '
WHEN @DATA_TYPE IN ('varbinary') AND @CHARACTER_MAXIMUM_LENGTH = -1 THEN + '
,MAX(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,MIN(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,AVG(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL '
WHEN @DATA_TYPE IN ('binary') THEN + '
,MAX(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,MIN(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,AVG(LEN(' + QUOTENAME(@COLUMN_NAME) + '))
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL '
WHEN @DATA_TYPE IN ('time') THEN + '
,NULL
,NULL
,NULL
,MAX(' + QUOTENAME(@COLUMN_NAME) + ')
,MIN(' + QUOTENAME(@COLUMN_NAME) + ')
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL '
ELSE + '
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL
,NULL '
END + '
,(SELECT COUNT(*) FROM ' + @DataSource + ' WHERE ' + QUOTENAME(@COLUMN_NAME) + ' IS NULL)'
+ CASE
WHEN @DATA_TYPE IN ('varchar', 'nvarchar', 'char', 'nchar') THEN + '
,(SELECT COUNT(*) FROM ' + @DataSource + ' WHERE LEN(LTRIM(RTRIM(' + QUOTENAME(@COLUMN_NAME) + '))) = '''')'
ELSE + '
,NULL'
END + '
,(SELECT COUNT(DISTINCT ' + QUOTENAME(@COLUMN_NAME) + ') FROM ' + @DataSource + ' WHERE ' + QUOTENAME(@COLUMN_NAME) + ' IS NOT NULL )
FROM ' + @DataSource + '
UNION'
FETCH NEXT FROM SQLMETADATA_CUR INTO @COLUMN_NAME, @CHARACTER_MAXIMUM_LENGTH, @DATA_TYPE
END
CLOSE SQLMETADATA_CUR
DEALLOCATE SQLMETADATA_CUR
SET @SQLMETADATA = LEFT(@SQLMETADATA, LEN(@SQLMETADATA) -5)
EXEC (@SQLMETADATA)
-----------------------------------------------------------------------
-- Final Calculations
-----------------------------------------------------------------------
-- Indicate Foreign Keys
;WITH FK_CTE (FKColumnName)
AS
(
SELECT DISTINCT CU.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU
ON TC.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
AND TC.TABLE_SCHEMA = CU.TABLE_SCHEMA
AND TC.TABLE_NAME = CU.TABLE_NAME
AND TC.TABLE_SCHEMA = @TABLE_SCHEMA
AND TC.TABLE_NAME = @TABLE_NAME
AND CONSTRAINT_TYPE = 'FOREIGN KEY'
)
UPDATE P
SET P.IsFK = 1
FROM #ProfileData P
INNER JOIN FK_CTE CTE
ON P.COLUMN_NAME COLLATE DATABASE_DEFAULT = CTE.FKColumnName COLLATE DATABASE_DEFAULT
-- Calculate percentages
UPDATE #ProfileData
SET PercentageNulls = (NoNulls / @ROWCOUNT) * 100
,PercentageZeroLength = (NoZeroLength / @ROWCOUNT) * 100
-- Add any comments
-- Datatype suggestions
-- First get number of records where a variation could be an anomaly
SET @DataTypeVariance = ROUND((@ROWCOUNT * @DataTypePercentage) / 100, 0)
UPDATE #ProfileData
SET DataTypeComments = 'Possibly could be one of the DATE types. '
WHERE NoIsDate BETWEEN (@ROWCOUNT -@DataTypeVariance) AND (@ROWCOUNT + @DataTypeVariance)
AND DataType IN ('varchar', 'nvarchar', 'char', 'nchar')
UPDATE #ProfileData
SET DataTypeComments = 'Possibly could be one of the NUMERIC types. '
WHERE NoIsNumeric BETWEEN (@ROWCOUNT -@DataTypeVariance) AND (@ROWCOUNT + @DataTypeVariance)
AND DataType IN ('varchar', 'nvarchar', 'char', 'nchar')
UPDATE #ProfileData
SET DataTypeComments = 'Possibly could be INT type. '
WHERE MinDataLength >= -2147483648
AND MaxDataLength <= 2147483648
AND DataType IN ('bigint')
UPDATE #ProfileData
SET DataTypeComments = 'Possibly could be SMALLINT type. '
WHERE MinDataLength >= -32768
AND MaxDataLength <= 32767
AND DataType IN ('bigint','int')
UPDATE #ProfileData
SET DataTypeComments = 'Possibly could be TINYINT type. '
WHERE MinDataLength >= 0
AND MaxDataLength <= 255
AND DataType IN ('bigint','int','smallint')
UPDATE #ProfileData
SET DataTypeComments = 'Possibly could be SMALLDATE type. '
WHERE NoDateWithSecond = 0
AND MinDate >= '19000101'
AND MaxDate <= '20790606'
AND DataType IN ('datetime','datetime2')
UPDATE #ProfileData
SET DataTypeComments = 'Possibly could be DATE type (SQL Server 2008 only). '
WHERE NoDateWithHourminuteSecond = 0
AND DataType IN ('datetime','datetime2')
UPDATE #ProfileData
SET DataTypeComments = 'Possibly could be DATETIME type. '
WHERE MinDate >= '17530101'
AND MaxDate <= '99991231'
AND DataType IN ('datetime2')
-- Empty column suggestions
UPDATE #ProfileData
SET DataTypeComments = ISNULL(DataTypeComments,'') + 'Seems empty - is it required? '
WHERE (PercentageNulls = 100 OR PercentageZeroLength = 100)
AND IsFK = 0
-- Null column suggestions
UPDATE #ProfileData
SET DataTypeComments = ISNULL(DataTypeComments,'') + 'There is a large percentage of NULLs - attention may be required. '
WHERE PercentageNulls >= @NullBoundaryPercent
-- Distinct value suggestions
UPDATE #ProfileData
SET DataTypeComments = ISNULL(DataTypeComments,'') + 'Few distinct elements - potential for reference/lookup table (contains NULLs).'
WHERE NoDistinct < @DistinctValuesMinimum
AND @ROWCOUNT > @DistinctValuesMinimum
AND IsFK = 0
AND PercentageNulls <> 100
AND NoNulls <> 0
-- FK suggestions
UPDATE #ProfileData
SET DataTypeComments = ISNULL(DataTypeComments,'') + 'Few distinct elements - potential for Foreign Key.'
WHERE NoDistinct < @DistinctValuesMinimum
AND @ROWCOUNT > @DistinctValuesMinimum
AND IsFK = 0
AND NoNulls = 0
AND DataType NOT LIKE '%Date%'
AND DataType <> 'Time'
-- Filestream suggestions
UPDATE #ProfileData
SET DataTypeComments = 'Possibly a good candidate for FILESTREAM (SQL Server 2008 only).'
WHERE AvgDataLength >= 1000000
AND DataType IN ('varbinary')
AND ColumnDataLength = -1
UPDATE #ProfileData
SET DataTypeComments = 'Possibly not a good candidate for FILESTREAM (SQL Server 2008 only).'
WHERE AvgDataLength < 1000000
AND DataType IN ('varbinary')
AND ColumnDataLength = -1
-- Sparse Column Suggestions
IF OBJECT_ID('tempdb..#SparseThresholds') IS NOT NULL
DROP TABLE #SparseThresholds;
CREATE TABLE #SparseThresholds (DataType VARCHAR(128) COLLATE DATABASE_DEFAULT, Threshold NUMERIC(9,4))
INSERT INTO #SparseThresholds (DataType, Threshold)
VALUES ('tinyint',86),
('smallint',76),
('int',64),
('bigint',52),
('real',64),
('float',52),
('money',64),
('smallmoney',64),
('smalldatetime',52),
('datetime',52),
('uniqueidentifier',43),
('date',69),
('datetime2',52),
('decimal',42),
('nuumeric',42),
('char',60),
('varchar',60),
('nchar',60),
('nvarchar',60),
('binary',60),
('varbinary',60),
('xml',60)
;
WITH Sparse_CTE (COLUMN_NAME, SparseComment)
AS
(
SELECT P.COLUMN_NAME
,CASE
WHEN P.PercentageNulls >= T.Threshold THEN 'Could benefit from sparse columns. '
ELSE ''
END AS SparseComment
FROM #ProfileData P
INNER JOIN #SparseThresholds T
ON P.DataType = T.DataType
)
UPDATE PT
SET PT.DataTypeComments =
CASE WHEN PT.DataTypeComments IS NULL THEN CTE.SparseComment
ELSE ISNULL(PT.DataTypeComments,'') + CTE.SparseComment + '. '
END
FROM #ProfileData PT
INNER JOIN Sparse_CTE CTE
ON PT.COLUMN_NAME = CTE.COLUMN_NAME
-----------------------------------------------------------------------
-- Optional advanced analysis
-----------------------------------------------------------------------
IF @AdvancedAnalysis = 1
BEGIN
-----------------------------------------------------------------------
-- Data at data boundaries
-----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#LimitTest') IS NOT NULL
DROP TABLE tempdb..#LimitTest;
CREATE TABLE #LimitTest (COLUMN_NAME VARCHAR(128) COLLATE DATABASE_DEFAULT, NoAtLimit BIGINT);
DECLARE @advancedtestSQL VARCHAR(MAX) = 'INSERT INTO #LimitTest (COLUMN_NAME, NoAtLimit)' + CHAR(13)
SELECT @advancedtestSQL = @advancedtestSQL + 'SELECT '''+ COLUMN_NAME + ''', COUNT('+ COLUMN_NAME + ') FROM ' + @DataSource +
CASE
WHEN DataType IN ('numeric', 'int', 'bigint', 'tinyint', 'smallint', 'decimal', 'money', 'smallmoney', 'float','real') THEN ' WHERE '+ COLUMN_NAME + ' = ' + CAST(ISNULL(MaxDataLength,0) AS VARCHAR(40)) + ' OR '+ COLUMN_NAME + ' = ' + CAST(ISNULL(MinDataLength,0) AS VARCHAR(40)) + CHAR(13) + ' UNION' + CHAR(13)
ELSE ' WHERE LEN('+ COLUMN_NAME + ') = ' + CAST(ISNULL(MaxDataLength,0) AS VARCHAR(40)) + ' OR LEN('+ COLUMN_NAME + ') = ' + CAST(ISNULL(MinDataLength,0) AS VARCHAR(40)) + CHAR(13) + ' UNION' + CHAR(13)
END
FROM #ProfileData
WHERE DataType IN ('numeric', 'int', 'bigint', 'tinyint', 'smallint', 'decimal', 'money', 'smallmoney', 'float','real','varchar', 'nvarchar', 'char', 'nchar', 'binary')
SET @advancedtestSQL = LEFT(@advancedtestSQL,LEN(@advancedtestSQL) -6)
EXEC (@advancedtestSQL)
UPDATE M
SET M.NoAtLimit = T.NoAtLimit
,M.DataTypeComments =
CASE
WHEN CAST(T.NoAtLimit AS NUMERIC(36,2)) / CAST(@ROWCOUNT AS NUMERIC(36,2)) >= @BoundaryPercent THEN ISNULL(M.DataTypeComments,'') + 'Large numbers of data elements at the max/minvalues. '
ELSE M.DataTypeComments
END
FROM #ProfileData M
INNER JOIN #LimitTest T
ON M.COLUMN_NAME = T.COLUMN_NAME
-----------------------------------------------------------------------
-- Domain analysis
-----------------------------------------------------------------------
IF OBJECT_ID('tempdb..#DomainAnalysis') IS NOT NULL
DROP TABLE tempdb..#DomainAnalysis;
CREATE TABLE #DomainAnalysis
(
DomainName NVARCHAR(128) COLLATE DATABASE_DEFAULT
,DomainElement NVARCHAR(4000) COLLATE DATABASE_DEFAULT
,DomainCounter BIGINT
,DomainPercent NUMERIC(7,4)
);
DECLARE @DOMAINSQL VARCHAR(MAX) = 'INSERT INTO #DomainAnalysis (DomainName, DomainElement, DomainCounter) '
DECLARE SQLDOMAIN_CUR CURSOR LOCAL FAST_FORWARD FOR
SELECT COLUMN_NAME, DataType FROM #ProfileData WHERE NoDistinct < @DistinctValuesMinimum
OPEN SQLDOMAIN_CUR
FETCH NEXT FROM SQLDOMAIN_CUR INTO @COLUMN_NAME, @DATA_TYPE
WHILE @@FETCH_STATUS = 0
BEGIN
SET @DOMAINSQL = @DOMAINSQL + 'SELECT ''' + @COLUMN_NAME + ''' AS DomainName, CAST( '+ @COLUMN_NAME + ' AS VARCHAR(4000)) AS DomainElement, COUNT(ISNULL(CAST(' + @COLUMN_NAME + ' AS NVARCHAR(MAX)),'''')) AS DomainCounter FROM ' + @DataSource + ' GROUP BY ' + @COLUMN_NAME + ''
+ ' UNION '
FETCH NEXT FROM SQLDOMAIN_CUR INTO @COLUMN_NAME, @DATA_TYPE
END
CLOSE SQLDOMAIN_CUR
DEALLOCATE SQLDOMAIN_CUR
SET @DOMAINSQL = LEFT(@DOMAINSQL, LEN(@DOMAINSQL) -5) + ' ORDER BY DomainName ASC, DomainCounter DESC '
EXEC (@DOMAINSQL)
-- Now calculate percentages (this appraoch is faster than doing it when performing the domain analysis)
;
WITH DomainCounter_CTE (DomainName, DomainCounterTotal)
AS
(
SELECT DomainName, SUM(ISNULL(DomainCounter,0)) AS DomainCounterTotal
FROM #DomainAnalysis
GROUP BY DomainName
)
UPDATE D
SET D.DomainPercent = (CAST(D.DomainCounter AS NUMERIC(36,4)) / CAST(CTE.DomainCounterTotal AS NUMERIC(36,4))) * 100
FROM #DomainAnalysis D
INNER JOIN DomainCounter_CTE CTE
ON D.DomainName = CTE.DomainName
WHERE D.DomainCounter <> 0
END -- Advanced analysis
-----------------------------------------------------------------------
-- Output results from the profile and domain data tables
-----------------------------------------------------------------------
select * from #ProfileData
IF @AdvancedAnalysis = 1
BEGIN
select * from #DomainAnalysis
END
-- Cleanup global sample table if it was used
IF @UseSample = 1
BEGIN
IF OBJECT_ID('tempdb..##SampleData') IS NOT NULL
DROP TABLE tempdb..##SampleData;
END
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
END CATCH[/ui-tab] [ui-tab title="PostgreSQL"] Here is a similar Data Profiling query, rewritten for PostgreSQL:
Dropped/changed features:
- NOLOCK hints (not applicable in PostgreSQL's MVCC model)
- SQL_VARIANT type (replaced with TEXT for min/max date/numeric storage)
- SPARSE column suggestions (SQL Server-specific feature)
- FILESTREAM suggestions (SQL Server-specific feature)
- QUOTENAME() replaced with quote_ident()
- ISNUMERIC()/ISDATE() replaced with regex and TRY-based casting via functions
- sys.columns/sys.indexes replaced with information_schema / pg_catalog
- TABLESAMPLE ROWS replaced with TABLESAMPLE BERNOULLI (PostgreSQL syntax)
- Cursors replaced with DO $$ ... $$ PL/pgSQL blocks with FOR loops
- Global temp tables (##) replaced with regular temp tables (PostgreSQL session-scoped)
- sp_executesql replaced with EXECUTE ... USING
- RAISERROR replaced with RAISE EXCEPTION
- TRY/CATCH replaced with BEGIN/EXCEPTION blocks
- STUFF/FOR XML PATH replaced with string_agg / array_to_string
- SmallDateTime type (no PG equivalent, handled under timestamp)
- money/smallmoney mapped to numeric
- nvarchar/nchar/varchar(max) mapped to text/varchar
DO $$
DECLARE
-- -------------------------------------------------------
-- Configuration
-- -------------------------------------------------------
v_table_schema TEXT := 'staging';
v_table_name TEXT := '45MinAuction';
v_use_sample BOOLEAN := FALSE;
v_sample_rows BIGINT := 200000;
v_skip_rows BIGINT := 5000000;
v_column_list_in TEXT := ''; -- Comma-separated column names, or '' for all
v_text_col BOOLEAN := TRUE;
v_num_col BOOLEAN := TRUE;
v_date_col BOOLEAN := TRUE;
v_lob_col BOOLEAN := TRUE;
v_advanced_analysis BOOLEAN := FALSE;
v_distinct_values_min INT := 200;
v_boundary_percent NUMERIC(5,4) := 0.0057; -- e.g. 0.57% expressed as 0.0057
v_null_boundary_percent NUMERIC(5,2) := 90.00;
v_datatype_percentage INT := 2;
-- -------------------------------------------------------
-- Process variables
-- -------------------------------------------------------
v_table_check INT;
v_column_check INT;
v_rowcount BIGINT := 0;
v_data_source TEXT := ''; -- Holds the table/sample name used in dynamic SQL
v_col_name TEXT;
v_data_type TEXT;
v_char_max_length INT;
v_sql TEXT;
v_datatype_variance INT;
v_column_list TEXT := '';
v_col_part TEXT;
v_cols TEXT[];
v_select_cols_list TEXT;
v_create_cols_list TEXT;
BEGIN
-- ===================================================
-- 1. Validate schema + table exist
-- ===================================================
SELECT COUNT(*) INTO v_table_check
FROM information_schema.tables
WHERE table_schema = v_table_schema
AND table_name = v_table_name;
IF v_table_check <> 1 THEN
RAISE EXCEPTION 'The table %.% does not exist', v_table_schema, v_table_name;
END IF;
-- ===================================================
-- 2. Build column list temp table
-- ===================================================
DROP TABLE IF EXISTS _profile_column_list;
CREATE TEMP TABLE _profile_column_list (
column_name TEXT,
data_type TEXT,
character_maximum_length INT
);
IF v_column_list_in <> '' THEN
-- Parse the comma-separated list the user provided
v_cols := string_to_array(v_column_list_in, ',');
FOREACH v_col_part IN ARRAY v_cols LOOP
INSERT INTO _profile_column_list (column_name)
VALUES (trim(v_col_part));
END LOOP;
-- Enrich with data type info from information_schema
UPDATE _profile_column_list cl
SET data_type = isc.data_type,
character_maximum_length = COALESCE(isc.character_maximum_length, 0)
FROM information_schema.columns isc
WHERE cl.column_name = isc.column_name
AND isc.table_schema = v_table_schema
AND isc.table_name = v_table_name;
ELSE
-- Auto-populate based on type flags
-- Text columns
IF v_text_col THEN
INSERT INTO _profile_column_list (column_name, data_type, character_maximum_length)
SELECT column_name,
data_type,
COALESCE(character_maximum_length, 0)
FROM information_schema.columns
WHERE table_schema = v_table_schema
AND table_name = v_table_name
AND data_type IN ('character varying', 'character', 'text', 'varchar', 'char')
AND (character_maximum_length IS NULL OR character_maximum_length > 0);
END IF;
-- Numeric columns
IF v_num_col THEN
INSERT INTO _profile_column_list (column_name, data_type, character_maximum_length)
SELECT column_name,
data_type,
COALESCE(character_maximum_length, 0)
FROM information_schema.columns
WHERE table_schema = v_table_schema
AND table_name = v_table_name
AND data_type IN (
'numeric', 'integer', 'bigint', 'smallint',
'decimal', 'real', 'double precision',
'money'
);
END IF;
-- Date/time columns
IF v_date_col THEN
INSERT INTO _profile_column_list (column_name, data_type, character_maximum_length)
SELECT column_name,
data_type,
COALESCE(character_maximum_length, 0)
FROM information_schema.columns
WHERE table_schema = v_table_schema
AND table_name = v_table_name
AND data_type IN (
'date', 'timestamp without time zone',
'timestamp with time zone', 'time without time zone',
'time with time zone'
);
END IF;
-- LOB columns (text / large varchar / bytea)
IF v_lob_col THEN
INSERT INTO _profile_column_list (column_name, data_type, character_maximum_length)
SELECT column_name,
data_type,
COALESCE(character_maximum_length, 0)
FROM information_schema.columns
WHERE table_schema = v_table_schema
AND table_name = v_table_name
AND data_type IN ('text', 'bytea', 'xml', 'json', 'jsonb')
AND column_name NOT IN (SELECT column_name FROM _profile_column_list);
-- Avoid duplicates if text was already captured by v_text_col
END IF;
END IF;
-- ===================================================
-- 3. Validate at least one column was found
-- ===================================================
SELECT COUNT(*) INTO v_column_check
FROM _profile_column_list
WHERE data_type IS NOT NULL;
IF v_column_check = 0 THEN
RAISE EXCEPTION 'No columns found to analyse in %.%', v_table_schema, v_table_name;
END IF;
-- ===================================================
-- 4. Create profile results temp table
-- ===================================================
DROP TABLE IF EXISTS _profile_data;
CREATE TEMP TABLE _profile_data (
table_schema TEXT,
table_name TEXT,
column_name TEXT,
column_data_length INT,
data_type TEXT,
min_data_length BIGINT,
max_data_length BIGINT,
avg_data_length BIGINT,
min_date TEXT, -- stored as text (SQL_VARIANT not available)
max_date TEXT,
min_numeric TEXT, -- stored as text
max_numeric TEXT,
no_distinct BIGINT,
no_nulls BIGINT,
no_zero_length BIGINT,
percentage_nulls NUMERIC(9,4),
percentage_zero_length NUMERIC(9,4),
no_date_with_hms BIGINT, -- date rows that have a non-zero time component
no_date_with_second BIGINT, -- date rows that have a non-zero second component
no_is_numeric BIGINT,
no_is_date BIGINT,
no_is_boolean BIGINT,
no_at_limit BIGINT,
is_fk BOOLEAN DEFAULT FALSE,
data_type_comments TEXT
);
-- ===================================================
-- 5. Get row count
-- ===================================================
EXECUTE format('SELECT COUNT(*) FROM %I.%I', v_table_schema, v_table_name)
INTO v_rowcount;
IF v_rowcount = 0 THEN
RAISE EXCEPTION 'There is no data in the table %.% to analyse', v_table_schema, v_table_name;
END IF;
-- ===================================================
-- 6. Sampling (optional)
-- ===================================================
IF v_use_sample THEN
DROP TABLE IF EXISTS _profile_sample_data;
-- Create a structurally identical empty copy
EXECUTE format(
'CREATE TEMP TABLE _profile_sample_data AS SELECT * FROM %I.%I LIMIT 0',
v_table_schema, v_table_name
);
IF v_skip_rows > 0 THEN
-- Deterministic window-based sampling (skip N rows then take M)
-- Use ctid ordering as a cheap surrogate when no PK is available.
-- For large tables a proper indexed column is recommended.
EXECUTE format(
'INSERT INTO _profile_sample_data
SELECT * FROM (
SELECT *, row_number() OVER (ORDER BY ctid) AS _rn
FROM %I.%I
) t
WHERE _rn > %s AND _rn <= %s',
v_table_schema, v_table_name,
v_skip_rows,
v_skip_rows + v_sample_rows
);
ELSE
BEGIN
-- TABLESAMPLE BERNOULLI is page-level random; use system for speed
-- Estimate percentage needed based on row count
DECLARE
v_pct NUMERIC;
BEGIN
v_pct := LEAST(100.0, (v_sample_rows::NUMERIC / v_rowcount::NUMERIC) * 100.0);
EXECUTE format(
'INSERT INTO _profile_sample_data SELECT * FROM %I.%I TABLESAMPLE BERNOULLI (%s)',
v_table_schema, v_table_name, v_pct
);
END;
EXCEPTION WHEN OTHERS THEN
-- Fallback: random ORDER BY with LIMIT
EXECUTE format(
'INSERT INTO _profile_sample_data SELECT * FROM %I.%I ORDER BY random() LIMIT %s',
v_table_schema, v_table_name, v_sample_rows
);
END;
END IF;
v_data_source := '_profile_sample_data';
EXECUTE 'SELECT COUNT(*) FROM _profile_sample_data' INTO v_rowcount;
ELSE
v_data_source := format('%I.%I', v_table_schema, v_table_name);
END IF;
-- ===================================================
-- 7. Per-column profiling (dynamic SQL loop)
-- ===================================================
FOR v_col_name, v_char_max_length, v_data_type IN
SELECT column_name,
COALESCE(character_maximum_length, 0),
data_type
FROM _profile_column_list
WHERE data_type IS NOT NULL
LOOP
-- Build the per-column INSERT dynamically based on data type group
IF v_data_type IN ('character varying', 'varchar', 'character', 'char')
AND v_char_max_length > 0
THEN
-- ---- Regular text columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
MAX(length(%I)),
MIN(length(%I)),
AVG(length(%I))::BIGINT,
(SELECT MAX(%I::TEXT) FROM %s WHERE %I ~ '^\d{4}-\d{2}-\d{2}'),
(SELECT MIN(%I::TEXT) FROM %s WHERE %I ~ '^\d{4}-\d{2}-\d{2}'),
(SELECT MIN(%I::TEXT) FROM %s WHERE %I ~ '^-?\d+(\.\d+)?$'),
(SELECT MAX(%I::TEXT) FROM %s WHERE %I ~ '^-?\d+(\.\d+)?$'),
NULL,
NULL,
(SELECT COUNT(*) FROM %s WHERE %I ~ '^-?\d+(\.\d+)?$'),
(SELECT COUNT(*) FROM %s WHERE %I ~ '^\d{4}-\d{2}-\d{2}'),
(SELECT COUNT(*) FROM %s WHERE lower(trim(%I)) IN ('1','0','y','n','yes','no','true','false','t','f')),
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
(SELECT COUNT(*) FROM %s WHERE length(trim(%I)) = 0),
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
-- scalar literals
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
-- MAX/MIN/AVG length
v_col_name, v_col_name, v_col_name,
-- max_date subquery
v_col_name, v_data_source, v_col_name,
-- min_date subquery
v_col_name, v_data_source, v_col_name,
-- min_numeric subquery
v_col_name, v_data_source, v_col_name,
-- max_numeric subquery
v_col_name, v_data_source, v_col_name,
-- no_is_numeric
v_data_source, v_col_name,
-- no_is_date
v_data_source, v_col_name,
-- no_is_boolean
v_data_source, v_col_name,
-- no_nulls
v_data_source, v_col_name,
-- no_zero_length
v_data_source, v_col_name,
-- no_distinct + FROM
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type IN ('numeric', 'integer', 'bigint', 'smallint',
'decimal', 'real', 'double precision', 'money')
THEN
-- ---- Numeric columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
MAX(%I)::BIGINT,
MIN(%I)::BIGINT,
AVG(%I::NUMERIC(36,2))::BIGINT,
NULL, NULL,
MIN(%I)::TEXT,
MAX(%I)::TEXT,
NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name, v_col_name,
v_col_name, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type IN ('timestamp without time zone',
'timestamp with time zone')
THEN
-- ---- Timestamp columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
NULL, NULL, NULL,
MAX(%I)::TEXT,
MIN(%I)::TEXT,
NULL, NULL,
(SELECT COUNT(*) FROM %s
WHERE EXTRACT(HOUR FROM %I) <> 0
OR EXTRACT(MINUTE FROM %I) <> 0
OR EXTRACT(SECOND FROM %I) <> 0),
(SELECT COUNT(*) FROM %s
WHERE EXTRACT(SECOND FROM %I) <> 0),
NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name,
-- no_date_with_hms
v_data_source, v_col_name, v_col_name, v_col_name,
-- no_date_with_second
v_data_source, v_col_name,
-- no_nulls
v_data_source, v_col_name,
-- no_distinct
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type = 'date' THEN
-- ---- Date-only columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
NULL, NULL, NULL,
MAX(%I)::TEXT,
MIN(%I)::TEXT,
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type IN ('time without time zone', 'time with time zone') THEN
-- ---- Time columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
NULL, NULL, NULL,
MAX(%I)::TEXT,
MIN(%I)::TEXT,
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type IN ('text', 'xml', 'json', 'jsonb')
OR (v_data_type IN ('character varying', 'varchar')
AND v_char_max_length = 0) -- unbounded varchar maps like text
THEN
-- ---- LOB / large text columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
MAX(length(%I::TEXT)),
MIN(length(%I::TEXT)),
AVG(length(%I::TEXT))::BIGINT,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
(SELECT COUNT(*) FROM %s WHERE length(trim(%I::TEXT)) = 0),
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name, v_col_name,
v_data_source, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type = 'bytea' THEN
-- ---- Binary / bytea columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
MAX(length(%I)),
MIN(length(%I)),
AVG(length(%I))::BIGINT,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSE
-- ---- Fallback for any other type ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
END IF;
EXECUTE v_sql;
END LOOP;
-- ===================================================
-- 8. Flag Foreign Key columns
-- ===================================================
UPDATE _profile_data pd
SET is_fk = TRUE
FROM (
SELECT DISTINCT cu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage cu
ON tc.constraint_name = cu.constraint_name
AND tc.table_schema = cu.table_schema
AND tc.table_name = cu.table_name
WHERE tc.table_schema = v_table_schema
AND tc.table_name = v_table_name
AND tc.constraint_type = 'FOREIGN KEY'
) fk
WHERE pd.column_name = fk.column_name;
-- ===================================================
-- 9. Calculate null / zero-length percentages
-- ===================================================
UPDATE _profile_data
SET percentage_nulls = (no_nulls::NUMERIC / v_rowcount) * 100,
percentage_zero_length = (no_zero_length::NUMERIC / v_rowcount) * 100
WHERE no_nulls IS NOT NULL;
-- ===================================================
-- 10. Data type commentary / suggestions
-- ===================================================
v_datatype_variance := ROUND((v_rowcount * v_datatype_percentage) / 100.0, 0);
-- Possible date type for text columns
UPDATE _profile_data
SET data_type_comments = 'Possibly could be one of the DATE types. '
WHERE no_is_date BETWEEN (v_rowcount - v_datatype_variance)
AND (v_rowcount + v_datatype_variance)
AND data_type IN ('character varying', 'varchar', 'character', 'char', 'text');
-- Possible numeric type for text columns
UPDATE _profile_data
SET data_type_comments = 'Possibly could be one of the NUMERIC types. '
WHERE no_is_numeric BETWEEN (v_rowcount - v_datatype_variance)
AND (v_rowcount + v_datatype_variance)
AND data_type IN ('character varying', 'varchar', 'character', 'char', 'text');
-- Possible boolean
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'All non-null values are boolean-like - possible BOOLEAN type. '
WHERE no_is_boolean = (v_rowcount - no_nulls)
AND no_is_boolean > 0
AND data_type IN ('character varying', 'varchar', 'character', 'char', 'text')
AND is_fk = FALSE;
-- bigint -> integer downcast
UPDATE _profile_data
SET data_type_comments = 'Possibly could be INTEGER type. '
WHERE min_numeric::BIGINT >= -2147483648
AND max_numeric::BIGINT <= 2147483647
AND data_type = 'bigint';
-- bigint/integer -> smallint
UPDATE _profile_data
SET data_type_comments = 'Possibly could be SMALLINT type. '
WHERE min_numeric::BIGINT >= -32768
AND max_numeric::BIGINT <= 32767
AND data_type IN ('bigint', 'integer');
-- Timestamp -> date (no time component present)
UPDATE _profile_data
SET data_type_comments = 'Possibly could be DATE type. '
WHERE no_date_with_hms = 0
AND data_type IN ('timestamp without time zone', 'timestamp with time zone');
-- Empty column
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'Seems empty - is it required? '
WHERE (percentage_nulls = 100 OR percentage_zero_length = 100)
AND is_fk = FALSE;
-- High NULL percentage
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'There is a large percentage of NULLs - attention may be required. '
WHERE percentage_nulls >= v_null_boundary_percent;
-- Low-cardinality with NULLs -> potential lookup table
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'Few distinct elements - potential for reference/lookup table (contains NULLs). '
WHERE no_distinct < v_distinct_values_min
AND v_rowcount > v_distinct_values_min
AND is_fk = FALSE
AND percentage_nulls <> 100
AND no_nulls <> 0;
-- Low-cardinality with no NULLs -> potential FK
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'Few distinct elements - potential for Foreign Key. '
WHERE no_distinct < v_distinct_values_min
AND v_rowcount > v_distinct_values_min
AND is_fk = FALSE
AND no_nulls = 0
AND data_type NOT ILIKE '%date%'
AND data_type NOT ILIKE '%time%';
-- ===================================================
-- 11. Optional advanced analysis
-- ===================================================
IF v_advanced_analysis THEN
-- ----- 11a. Boundary / limit analysis -----
DROP TABLE IF EXISTS _profile_limit_test;
CREATE TEMP TABLE _profile_limit_test (
column_name TEXT,
no_at_limit BIGINT
);
FOR v_col_name, v_data_type IN
SELECT column_name, data_type
FROM _profile_data
WHERE data_type IN (
'numeric', 'integer', 'bigint', 'smallint',
'decimal', 'real', 'double precision', 'money',
'character varying', 'varchar', 'character', 'char', 'text', 'bytea'
)
LOOP
DECLARE
v_min_val TEXT;
v_max_val TEXT;
BEGIN
SELECT min_data_length::TEXT, max_data_length::TEXT
INTO v_min_val, v_max_val
FROM _profile_data
WHERE column_name = v_col_name;
IF v_data_type IN ('numeric', 'integer', 'bigint', 'smallint',
'decimal', 'real', 'double precision', 'money')
THEN
EXECUTE format(
$q$
INSERT INTO _profile_limit_test (column_name, no_at_limit)
SELECT %L, COUNT(%I)
FROM %s
WHERE %I = %s OR %I = %s
$q$,
v_col_name, v_col_name,
v_data_source,
v_col_name, COALESCE(v_max_val,'0'),
v_col_name, COALESCE(v_min_val,'0')
);
ELSE
EXECUTE format(
$q$
INSERT INTO _profile_limit_test (column_name, no_at_limit)
SELECT %L, COUNT(%I)
FROM %s
WHERE length(%I::TEXT) = %s
OR length(%I::TEXT) = %s
$q$,
v_col_name, v_col_name,
v_data_source,
v_col_name, COALESCE(v_max_val,'0'),
v_col_name, COALESCE(v_min_val,'0')
);
END IF;
END;
END LOOP;
UPDATE _profile_data m
SET no_at_limit = t.no_at_limit,
data_type_comments =
CASE
WHEN t.no_at_limit::NUMERIC / v_rowcount::NUMERIC >= v_boundary_percent
THEN COALESCE(m.data_type_comments,'') ||
'Large number of data elements at the max/min values. '
ELSE m.data_type_comments
END
FROM _profile_limit_test t
WHERE m.column_name = t.column_name;
-- ----- 11b. Domain analysis -----
DROP TABLE IF EXISTS _profile_domain_analysis;
CREATE TEMP TABLE _profile_domain_analysis (
domain_name TEXT,
domain_element TEXT,
domain_counter BIGINT,
domain_percent NUMERIC(7,4)
);
FOR v_col_name, v_data_type IN
SELECT column_name, data_type
FROM _profile_data
WHERE no_distinct < v_distinct_values_min
LOOP
EXECUTE format(
$q$
INSERT INTO _profile_domain_analysis (domain_name, domain_element, domain_counter)
SELECT %L,
%I::TEXT,
COUNT(*)
FROM %s
GROUP BY %I
ORDER BY COUNT(*) DESC
$q$,
v_col_name,
v_col_name,
v_data_source,
v_col_name
);
END LOOP;
-- Calculate domain percentages
UPDATE _profile_domain_analysis d
SET domain_percent =
(d.domain_counter::NUMERIC(36,4) /
totals.total::NUMERIC(36,4)) * 100
FROM (
SELECT domain_name, SUM(COALESCE(domain_counter, 0)) AS total
FROM _profile_domain_analysis
GROUP BY domain_name
) totals
WHERE d.domain_name = totals.domain_name
AND d.domain_counter <> 0;
DO $$
DECLARE
-- -------------------------------------------------------
-- Configuration
-- -------------------------------------------------------
v_table_schema TEXT := 'staging';
v_table_name TEXT := '45MinAuction';
v_use_sample BOOLEAN := FALSE;
v_sample_rows BIGINT := 200000;
v_skip_rows BIGINT := 5000000;
v_column_list_in TEXT := ''; -- Comma-separated column names, or '' for all
v_text_col BOOLEAN := TRUE;
v_num_col BOOLEAN := TRUE;
v_date_col BOOLEAN := TRUE;
v_lob_col BOOLEAN := TRUE;
v_advanced_analysis BOOLEAN := FALSE;
v_distinct_values_min INT := 200;
v_boundary_percent NUMERIC(5,4) := 0.0057; -- e.g. 0.57% expressed as 0.0057
v_null_boundary_percent NUMERIC(5,2) := 90.00;
v_datatype_percentage INT := 2;
-- -------------------------------------------------------
-- Process variables
-- -------------------------------------------------------
v_table_check INT;
v_column_check INT;
v_rowcount BIGINT := 0;
v_data_source TEXT := ''; -- Holds the table/sample name used in dynamic SQL
v_col_name TEXT;
v_data_type TEXT;
v_char_max_length INT;
v_sql TEXT;
v_datatype_variance INT;
v_column_list TEXT := '';
v_col_part TEXT;
v_cols TEXT[];
v_select_cols_list TEXT;
v_create_cols_list TEXT;
BEGIN
-- 1. Validate schema + table exist
SELECT COUNT(*) INTO v_table_check
FROM information_schema.tables
WHERE table_schema = v_table_schema
AND table_name = v_table_name;
IF v_table_check <> 1 THEN
RAISE EXCEPTION 'The table %.% does not exist', v_table_schema, v_table_name;
END IF;
-- 2. Build column list temp table
DROP TABLE IF EXISTS _profile_column_list;
CREATE TEMP TABLE _profile_column_list (
column_name TEXT,
data_type TEXT,
character_maximum_length INT
);
IF v_column_list_in <> '' THEN
-- Parse the comma-separated list the user provided
v_cols := string_to_array(v_column_list_in, ',');
FOREACH v_col_part IN ARRAY v_cols LOOP
INSERT INTO _profile_column_list (column_name)
VALUES (trim(v_col_part));
END LOOP;
-- Enrich with data type info from information_schema
UPDATE _profile_column_list cl
SET data_type = isc.data_type,
character_maximum_length = COALESCE(isc.character_maximum_length, 0)
FROM information_schema.columns isc
WHERE cl.column_name = isc.column_name
AND isc.table_schema = v_table_schema
AND isc.table_name = v_table_name;
ELSE
-- Auto-populate based on type flags
-- Text columns
IF v_text_col THEN
INSERT INTO _profile_column_list (column_name, data_type, character_maximum_length)
SELECT column_name,
data_type,
COALESCE(character_maximum_length, 0)
FROM information_schema.columns
WHERE table_schema = v_table_schema
AND table_name = v_table_name
AND data_type IN ('character varying', 'character', 'text', 'varchar', 'char')
AND (character_maximum_length IS NULL OR character_maximum_length > 0);
END IF;
-- Numeric columns
IF v_num_col THEN
INSERT INTO _profile_column_list (column_name, data_type, character_maximum_length)
SELECT column_name,
data_type,
COALESCE(character_maximum_length, 0)
FROM information_schema.columns
WHERE table_schema = v_table_schema
AND table_name = v_table_name
AND data_type IN (
'numeric', 'integer', 'bigint', 'smallint',
'decimal', 'real', 'double precision',
'money'
);
END IF;
-- Date/time columns
IF v_date_col THEN
INSERT INTO _profile_column_list (column_name, data_type, character_maximum_length)
SELECT column_name,
data_type,
COALESCE(character_maximum_length, 0)
FROM information_schema.columns
WHERE table_schema = v_table_schema
AND table_name = v_table_name
AND data_type IN (
'date', 'timestamp without time zone',
'timestamp with time zone', 'time without time zone',
'time with time zone'
);
END IF;
-- LOB columns (text / large varchar / bytea)
IF v_lob_col THEN
INSERT INTO _profile_column_list (column_name, data_type, character_maximum_length)
SELECT column_name,
data_type,
COALESCE(character_maximum_length, 0)
FROM information_schema.columns
WHERE table_schema = v_table_schema
AND table_name = v_table_name
AND data_type IN ('text', 'bytea', 'xml', 'json', 'jsonb')
AND column_name NOT IN (SELECT column_name FROM _profile_column_list);
-- Avoid duplicates if text was already captured by v_text_col
END IF;
END IF;
-- 3. Validate at least one column was found
SELECT COUNT(*) INTO v_column_check
FROM _profile_column_list
WHERE data_type IS NOT NULL;
IF v_column_check = 0 THEN
RAISE EXCEPTION 'No columns found to analyse in %.%', v_table_schema, v_table_name;
END IF;
-- 4. Create profile results temp table
DROP TABLE IF EXISTS _profile_data;
CREATE TEMP TABLE _profile_data (
table_schema TEXT,
table_name TEXT,
column_name TEXT,
column_data_length INT,
data_type TEXT,
min_data_length BIGINT,
max_data_length BIGINT,
avg_data_length BIGINT,
min_date TEXT, -- stored as text (SQL_VARIANT not available)
max_date TEXT,
min_numeric TEXT, -- stored as text
max_numeric TEXT,
no_distinct BIGINT,
no_nulls BIGINT,
no_zero_length BIGINT,
percentage_nulls NUMERIC(9,4),
percentage_zero_length NUMERIC(9,4),
no_date_with_hms BIGINT, -- date rows that have a non-zero time component
no_date_with_second BIGINT, -- date rows that have a non-zero second component
no_is_numeric BIGINT,
no_is_date BIGINT,
no_is_boolean BIGINT,
no_at_limit BIGINT,
is_fk BOOLEAN DEFAULT FALSE,
data_type_comments TEXT
);
-- 5. Get row count
EXECUTE format('SELECT COUNT(*) FROM %I.%I', v_table_schema, v_table_name)
INTO v_rowcount;
IF v_rowcount = 0 THEN
RAISE EXCEPTION 'There is no data in the table %.% to analyse', v_table_schema, v_table_name;
END IF;
-- 6. Sampling (optional)
IF v_use_sample THEN
DROP TABLE IF EXISTS _profile_sample_data;
-- Create a structurally identical empty copy
EXECUTE format(
'CREATE TEMP TABLE _profile_sample_data AS SELECT * FROM %I.%I LIMIT 0',
v_table_schema, v_table_name
);
IF v_skip_rows > 0 THEN
-- Deterministic window-based sampling (skip N rows then take M)
-- Use ctid ordering as a cheap surrogate when no PK is available.
-- For large tables a proper indexed column is recommended.
EXECUTE format(
'INSERT INTO _profile_sample_data
SELECT * FROM (
SELECT *, row_number() OVER (ORDER BY ctid) AS _rn
FROM %I.%I
) t
WHERE _rn > %s AND _rn <= %s',
v_table_schema, v_table_name,
v_skip_rows,
v_skip_rows + v_sample_rows
);
ELSE
BEGIN
-- TABLESAMPLE BERNOULLI is page-level random; use system for speed
-- Estimate percentage needed based on row count
DECLARE
v_pct NUMERIC;
BEGIN
v_pct := LEAST(100.0, (v_sample_rows::NUMERIC / v_rowcount::NUMERIC) * 100.0);
EXECUTE format(
'INSERT INTO _profile_sample_data SELECT * FROM %I.%I TABLESAMPLE BERNOULLI (%s)',
v_table_schema, v_table_name, v_pct
);
END;
EXCEPTION WHEN OTHERS THEN
-- Fallback: random ORDER BY with LIMIT
EXECUTE format(
'INSERT INTO _profile_sample_data SELECT * FROM %I.%I ORDER BY random() LIMIT %s',
v_table_schema, v_table_name, v_sample_rows
);
END;
END IF;
v_data_source := '_profile_sample_data';
EXECUTE 'SELECT COUNT(*) FROM _profile_sample_data' INTO v_rowcount;
ELSE
v_data_source := format('%I.%I', v_table_schema, v_table_name);
END IF;
-- 7. Per-column profiling (dynamic SQL loop)
FOR v_col_name, v_char_max_length, v_data_type IN
SELECT column_name,
COALESCE(character_maximum_length, 0),
data_type
FROM _profile_column_list
WHERE data_type IS NOT NULL
LOOP
-- Build the per-column INSERT dynamically based on data type group
IF v_data_type IN ('character varying', 'varchar', 'character', 'char')
AND v_char_max_length > 0
THEN
-- ---- Regular text columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
MAX(length(%I)),
MIN(length(%I)),
AVG(length(%I))::BIGINT,
(SELECT MAX(%I::TEXT) FROM %s WHERE %I ~ '^\d{4}-\d{2}-\d{2}'),
(SELECT MIN(%I::TEXT) FROM %s WHERE %I ~ '^\d{4}-\d{2}-\d{2}'),
(SELECT MIN(%I::TEXT) FROM %s WHERE %I ~ '^-?\d+(\.\d+)?$'),
(SELECT MAX(%I::TEXT) FROM %s WHERE %I ~ '^-?\d+(\.\d+)?$'),
NULL,
NULL,
(SELECT COUNT(*) FROM %s WHERE %I ~ '^-?\d+(\.\d+)?$'),
(SELECT COUNT(*) FROM %s WHERE %I ~ '^\d{4}-\d{2}-\d{2}'),
(SELECT COUNT(*) FROM %s WHERE lower(trim(%I)) IN ('1','0','y','n','yes','no','true','false','t','f')),
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
(SELECT COUNT(*) FROM %s WHERE length(trim(%I)) = 0),
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
-- scalar literals
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
-- MAX/MIN/AVG length
v_col_name, v_col_name, v_col_name,
-- max_date subquery
v_col_name, v_data_source, v_col_name,
-- min_date subquery
v_col_name, v_data_source, v_col_name,
-- min_numeric subquery
v_col_name, v_data_source, v_col_name,
-- max_numeric subquery
v_col_name, v_data_source, v_col_name,
-- no_is_numeric
v_data_source, v_col_name,
-- no_is_date
v_data_source, v_col_name,
-- no_is_boolean
v_data_source, v_col_name,
-- no_nulls
v_data_source, v_col_name,
-- no_zero_length
v_data_source, v_col_name,
-- no_distinct + FROM
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type IN ('numeric', 'integer', 'bigint', 'smallint',
'decimal', 'real', 'double precision', 'money')
THEN
-- ---- Numeric columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
MAX(%I)::BIGINT,
MIN(%I)::BIGINT,
AVG(%I::NUMERIC(36,2))::BIGINT,
NULL, NULL,
MIN(%I)::TEXT,
MAX(%I)::TEXT,
NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name, v_col_name,
v_col_name, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type IN ('timestamp without time zone',
'timestamp with time zone')
THEN
-- ---- Timestamp columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
NULL, NULL, NULL,
MAX(%I)::TEXT,
MIN(%I)::TEXT,
NULL, NULL,
(SELECT COUNT(*) FROM %s
WHERE EXTRACT(HOUR FROM %I) <> 0
OR EXTRACT(MINUTE FROM %I) <> 0
OR EXTRACT(SECOND FROM %I) <> 0),
(SELECT COUNT(*) FROM %s
WHERE EXTRACT(SECOND FROM %I) <> 0),
NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name,
-- no_date_with_hms
v_data_source, v_col_name, v_col_name, v_col_name,
-- no_date_with_second
v_data_source, v_col_name,
-- no_nulls
v_data_source, v_col_name,
-- no_distinct
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type = 'date' THEN
-- ---- Date-only columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
NULL, NULL, NULL,
MAX(%I)::TEXT,
MIN(%I)::TEXT,
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type IN ('time without time zone', 'time with time zone') THEN
-- ---- Time columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
NULL, NULL, NULL,
MAX(%I)::TEXT,
MIN(%I)::TEXT,
NULL, NULL, NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type IN ('text', 'xml', 'json', 'jsonb')
OR (v_data_type IN ('character varying', 'varchar')
AND v_char_max_length = 0) -- unbounded varchar maps like text
THEN
-- ---- LOB / large text columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
MAX(length(%I::TEXT)),
MIN(length(%I::TEXT)),
AVG(length(%I::TEXT))::BIGINT,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
(SELECT COUNT(*) FROM %s WHERE length(trim(%I::TEXT)) = 0),
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name, v_col_name,
v_data_source, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSIF v_data_type = 'bytea' THEN
-- ---- Binary / bytea columns ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
MAX(length(%I)),
MIN(length(%I)),
AVG(length(%I))::BIGINT,
NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_col_name, v_col_name, v_col_name,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
ELSE
-- ---- Fallback for any other type ----
v_sql := format(
$q$
INSERT INTO _profile_data
(column_data_length, column_name, table_schema, table_name, data_type,
max_data_length, min_data_length, avg_data_length,
max_date, min_date, min_numeric, max_numeric,
no_date_with_hms, no_date_with_second,
no_is_numeric, no_is_date, no_is_boolean,
no_nulls, no_zero_length, no_distinct)
SELECT
%s, %L, %L, %L, %L,
NULL, NULL, NULL,
NULL, NULL, NULL, NULL,
NULL, NULL, NULL, NULL, NULL,
(SELECT COUNT(*) FROM %s WHERE %I IS NULL),
NULL,
(SELECT COUNT(DISTINCT %I) FROM %s WHERE %I IS NOT NULL)
FROM %s
$q$,
v_char_max_length, v_col_name, v_table_schema, v_table_name, v_data_type,
v_data_source, v_col_name,
v_col_name, v_data_source, v_col_name,
v_data_source
);
END IF;
EXECUTE v_sql;
END LOOP;
-- 8. Flag Foreign Key columns
UPDATE _profile_data pd
SET is_fk = TRUE
FROM (
SELECT DISTINCT cu.column_name
FROM information_schema.table_constraints tc
JOIN information_schema.constraint_column_usage cu
ON tc.constraint_name = cu.constraint_name
AND tc.table_schema = cu.table_schema
AND tc.table_name = cu.table_name
WHERE tc.table_schema = v_table_schema
AND tc.table_name = v_table_name
AND tc.constraint_type = 'FOREIGN KEY'
) fk
WHERE pd.column_name = fk.column_name;
-- 9. Calculate null / zero-length percentages
UPDATE _profile_data
SET percentage_nulls = (no_nulls::NUMERIC / v_rowcount) * 100,
percentage_zero_length = (no_zero_length::NUMERIC / v_rowcount) * 100
WHERE no_nulls IS NOT NULL;
-- 10. Data type commentary / suggestions
v_datatype_variance := ROUND((v_rowcount * v_datatype_percentage) / 100.0, 0);
-- Possible date type for text columns
UPDATE _profile_data
SET data_type_comments = 'Possibly could be one of the DATE types. '
WHERE no_is_date BETWEEN (v_rowcount - v_datatype_variance)
AND (v_rowcount + v_datatype_variance)
AND data_type IN ('character varying', 'varchar', 'character', 'char', 'text');
-- Possible numeric type for text columns
UPDATE _profile_data
SET data_type_comments = 'Possibly could be one of the NUMERIC types. '
WHERE no_is_numeric BETWEEN (v_rowcount - v_datatype_variance)
AND (v_rowcount + v_datatype_variance)
AND data_type IN ('character varying', 'varchar', 'character', 'char', 'text');
-- Possible boolean
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'All non-null values are boolean-like - possible BOOLEAN type. '
WHERE no_is_boolean = (v_rowcount - no_nulls)
AND no_is_boolean > 0
AND data_type IN ('character varying', 'varchar', 'character', 'char', 'text')
AND is_fk = FALSE;
-- bigint -> integer downcast
UPDATE _profile_data
SET data_type_comments = 'Possibly could be INTEGER type. '
WHERE min_numeric::BIGINT >= -2147483648
AND max_numeric::BIGINT <= 2147483647
AND data_type = 'bigint';
-- bigint/integer -> smallint
UPDATE _profile_data
SET data_type_comments = 'Possibly could be SMALLINT type. '
WHERE min_numeric::BIGINT >= -32768
AND max_numeric::BIGINT <= 32767
AND data_type IN ('bigint', 'integer');
-- Timestamp -> date (no time component present)
UPDATE _profile_data
SET data_type_comments = 'Possibly could be DATE type. '
WHERE no_date_with_hms = 0
AND data_type IN ('timestamp without time zone', 'timestamp with time zone');
-- Empty column
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'Seems empty - is it required? '
WHERE (percentage_nulls = 100 OR percentage_zero_length = 100)
AND is_fk = FALSE;
-- High NULL percentage
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'There is a large percentage of NULLs - attention may be required. '
WHERE percentage_nulls >= v_null_boundary_percent;
-- Low-cardinality with NULLs -> potential lookup table
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'Few distinct elements - potential for reference/lookup table (contains NULLs). '
WHERE no_distinct < v_distinct_values_min
AND v_rowcount > v_distinct_values_min
AND is_fk = FALSE
AND percentage_nulls <> 100
AND no_nulls <> 0;
-- Low-cardinality with no NULLs -> potential FK
UPDATE _profile_data
SET data_type_comments = COALESCE(data_type_comments, '') ||
'Few distinct elements - potential for Foreign Key. '
WHERE no_distinct < v_distinct_values_min
AND v_rowcount > v_distinct_values_min
AND is_fk = FALSE
AND no_nulls = 0
AND data_type NOT ILIKE '%date%'
AND data_type NOT ILIKE '%time%';
-- 11. Optional advanced analysis
IF v_advanced_analysis THEN
-- ----- 11a. Boundary / limit analysis -----
DROP TABLE IF EXISTS _profile_limit_test;
CREATE TEMP TABLE _profile_limit_test (
column_name TEXT,
no_at_limit BIGINT
);
FOR v_col_name, v_data_type IN
SELECT column_name, data_type
FROM _profile_data
WHERE data_type IN (
'numeric', 'integer', 'bigint', 'smallint',
'decimal', 'real', 'double precision', 'money',
'character varying', 'varchar', 'character', 'char', 'text', 'bytea'
)
LOOP
DECLARE
v_min_val TEXT;
v_max_val TEXT;
BEGIN
SELECT min_data_length::TEXT, max_data_length::TEXT
INTO v_min_val, v_max_val
FROM _profile_data
WHERE column_name = v_col_name;
IF v_data_type IN ('numeric', 'integer', 'bigint', 'smallint',
'decimal', 'real', 'double precision', 'money')
THEN
EXECUTE format(
$q$
INSERT INTO _profile_limit_test (column_name, no_at_limit)
SELECT %L, COUNT(%I)
FROM %s
WHERE %I = %s OR %I = %s
$q$,
v_col_name, v_col_name,
v_data_source,
v_col_name, COALESCE(v_max_val,'0'),
v_col_name, COALESCE(v_min_val,'0')
);
ELSE
EXECUTE format(
$q$
INSERT INTO _profile_limit_test (column_name, no_at_limit)
SELECT %L, COUNT(%I)
FROM %s
WHERE length(%I::TEXT) = %s
OR length(%I::TEXT) = %s
$q$,
v_col_name, v_col_name,
v_data_source,
v_col_name, COALESCE(v_max_val,'0'),
v_col_name, COALESCE(v_min_val,'0')
);
END IF;
END;
END LOOP;
UPDATE _profile_data m
SET no_at_limit = t.no_at_limit,
data_type_comments =
CASE
WHEN t.no_at_limit::NUMERIC / v_rowcount::NUMERIC >= v_boundary_percent
THEN COALESCE(m.data_type_comments,'') ||
'Large number of data elements at the max/min values. '
ELSE m.data_type_comments
END
FROM _profile_limit_test t
WHERE m.column_name = t.column_name;
-- ----- 11b. Domain analysis -----
DROP TABLE IF EXISTS _profile_domain_analysis;
CREATE TEMP TABLE _profile_domain_analysis (
domain_name TEXT,
domain_element TEXT,
domain_counter BIGINT,
domain_percent NUMERIC(7,4)
);
FOR v_col_name, v_data_type IN
SELECT column_name, data_type
FROM _profile_data
WHERE no_distinct < v_distinct_values_min
LOOP
EXECUTE format(
$q$
INSERT INTO _profile_domain_analysis (domain_name, domain_element, domain_counter)
SELECT %L,
%I::TEXT,
COUNT(*)
FROM %s
GROUP BY %I
ORDER BY COUNT(*) DESC
$q$,
v_col_name,
v_col_name,
v_data_source,
v_col_name
);
END LOOP;
-- Calculate domain percentages
UPDATE _profile_domain_analysis d
SET domain_percent =
(d.domain_counter::NUMERIC(36,4) /
totals.total::NUMERIC(36,4)) * 100
FROM (
SELECT domain_name, SUM(COALESCE(domain_counter, 0)) AS total
FROM _profile_domain_analysis
GROUP BY domain_name
) totals
WHERE d.domain_name = totals.domain_name
AND d.domain_counter <> 0;
END IF; -- advanced analysis
-- 12. Output
-- Primary profile results
RAISE NOTICE 'Profile data written to temp table: _profile_data';
-- (psql clients can SELECT from the temp table after the block executes,
-- or you can wrap this block in a function that returns a set.)
IF v_advanced_analysis THEN
RAISE NOTICE 'Domain analysis written to temp table: _profile_domain_analysis';
END IF;
-- Cleanup sample table
IF v_use_sample THEN
DROP TABLE IF EXISTS _profile_sample_data;
END IF;
EXCEPTION WHEN OTHERS THEN
RAISE NOTICE 'Error: % / %', SQLSTATE, SQLERRM;
END;
$$;
-- Run these queries after the block to view results:
SELECT * FROM _profile_data ORDER BY column_name;
-- SELECT * FROM _profile_domain_analysis ORDER BY domain_name, domain_percent DESC;
[/ui-tab] [ui-tab title="Query Results"]
This results in the following table:
| TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ColumnDataLength | DataType | MinDataLength | MaxDataLength | AvgDataLength | MinDate | MaxDate | MinNumeric | MaxNumeric | NoDistinct | NoNulls | NoZeroLength | PercentageNulls | PercentageZeroLength | NoDateWithHourminuteSecond | NoDateWithSecond | NoIsNumeric | NoIsDate | NoIsBoolean | NoAtLimit | IsFK | DataTypeComments |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| [dbo] | [table_name] | [Id] | 0 | bigint | 1 | 99999 | 20885 | NULL | NULL | 1 | 5204 | 5204 | 0.0000 | NULL | 0.0000 | NULL | NULL | NULL | 5204 | NULL | NULL | NULL | 1 | Possibly could be INT type. |
| [dbo] | [table_name] | [Name] | 100 | varchar | 15 | 68 | 29 | NULL | NULL | NULL | NULL | 335 | 5.0000 | NULL | 93.5434 | NULL | NULL | NULL | NULL | NULL | 0 | NULL | 0 | There is a large percentage of NULLs - attention may be required. Could benefit from sparse columns. |
| [dbo] | [table_name] | [Type] | 50 | varchar | 5 | 20 | 13 | NULL | NULL | NULL | NULL | 53 | 1505.0000 | NULL | 28.9201 | NULL | NULL | NULL | NULL | NULL | 0 | NULL | 0 | Few distinct elements - potential for reference/lookup table (contains NULLs). |
| [dbo] | [table_name] | [Active] | 0 | bit | NULL | NULL | NULL | NULL | NULL | NULL | NULL | 24 | 550.0000 | NULL | 10.5688 | NULL | NULL | NULL | NULL | NULL | 5204 | NULL | 0 | (contains NULLs). |
| [dbo] | [table_name] | [Description] | 200 | varchar | 15 | 163 | 53 | NULL | NULL | NULL | NULL | 528 | 3866.0000 | 256.0000 | 74.2890 | NULL | NULL | NULL | 0 | 0 | 0 | NULL | 0 | There is a large percentage of NULLs - attention may be required. Could benefit from sparse columns. |
| [dbo] | [table_name] | [DateCreated] | 0 | datetime2 | NULL | NULL | NULL | 2021-05-19 00:00:00.000 | 2023-04-11 00:00:00.000 | NULL | NULL | 1275 | 0.0000 | 0.0000 | 0.0000 | NULL | NULL | NULL | 0 | 5204 | 0 | NULL | 0 |
[/ui-tabs]
About the Author: