SLAM_Alpha_profile_data

Overview

SLAM_Alpha_profile_data is a utility procedure that profiles SQL Server tables to produce comprehensive descriptive statistics at three granularities:

• Table-level: Row counts, column counts
• Column-level: Min/max values, averages, null counts, blank counts, distinct values
• Value-level: Frequency distribution for low-cardinality columns

The profiling is designed for incremental processing, allowing you to profile tables selectively and avoid re-profiling unchanged data. Results are written to three output tables: profile_tables, profile_columns, and profile_values.

Note: This operation requires valid Salesforce credentials for license validation. However, the profiling itself only accesses SQL Server data.

Parameters

Name	Type	Default	Description
@table_match	VARCHAR(255)	'%'	LIKE pattern to filter tables to profile (e.g., 'Customer%' for tables starting with "Customer")
@schema_match	VARCHAR(255)	'%'	LIKE pattern to filter schemas (e.g., 'dbo' for only the dbo schema)
@refresh_existing	BIT	0	Whether to re-profile tables that match the filter criteria but have been profiled before. 0 = skip already profiled tables, 1 = re-profile all matching tables
@value_limit	INT	50	Maximum distinct values to profile for frequency distribution. Columns with more distinct values will skip value profiling. Maximum: 60

Output Tables

The procedure writes results to three tables in the dbo schema (created automatically if they don't exist).

profile_tables

Table-level statistics with one row per profiled table.

Column	Type	Description
schema_name	NVARCHAR(128)	Schema name
table_name	NVARCHAR(128)	Table name
qualified_name	Computed	schema_name.table_name
num_rows	BIGINT	Total row count
num_columns	INT	Total column count
date_profiled	DATETIME2	Timestamp of profiling run

profile_columns

Column-level statistics with one row per column in each profiled table.

Column	Type	Description
schema_name	NVARCHAR(128)	Schema name
table_name	NVARCHAR(128)	Table name
column_name	NVARCHAR(128)	Column name
qualified_name	Computed	schema_name.table_name.column_name
type	NVARCHAR(128)	SQL Server data type
length	INT	Column length (for string/binary types)
scale	INT	Column scale (for numeric types)
precision	INT	Column precision (for numeric types)
nullable	BIT	Whether column allows NULLs
min_value	NVARCHAR(MAX)	Minimum value (NULL for non-applicable types)
max_value	NVARCHAR(MAX)	Maximum value
average_value	NVARCHAR(50)	Average value (numeric types only)
max_length	INT	Maximum character/byte length (string/binary types only)
num_distinct	BIGINT	Number of distinct non-NULL values (similar to SQL COUNT(DISTINCT column_name))
is_unique_non_null	BIT	1 if all non-NULL values are unique and no NULLs exist
num_filled	BIGINT	Number of non-NULL values (blanks count as filled)
num_null	BIGINT	Number of NULL rows
num_blank	BIGINT	Number of blank rows (empty string or whitespace-only, string types only)
num_blank_or_null	BIGINT	num_null + num_blank
num_zero	BIGINT	Number of rows with value 0 (numeric) or '0' (string)
num_table_rows	BIGINT	Total table row count
percent_filled	DECIMAL(5,2)	Percentage of filled rows (0.00 - 100.00)
percent_null	DECIMAL(5,2)	Percentage of NULL rows
percent_blank	DECIMAL(5,2)	Percentage of blank rows
percent_blank_or_null	DECIMAL(5,2)	Percentage of blank or NULL rows
percent_zero	DECIMAL(5,2)	Percentage of zero rows
date_profiled	DATETIME2	Timestamp of profiling run

profile_values

Value frequency distribution for columns with low cardinality (≤ @value_limit distinct values).

Column	Type	Description
schema_name	NVARCHAR(128)	Schema name
table_name	NVARCHAR(128)	Table name
column_name	NVARCHAR(128)	Column name
value	NVARCHAR(255)	Distinct value (truncated to 252 chars + '...' if longer)
count	BIGINT	Number of occurrences
frequency	DECIMAL(10,6)	Frequency as decimal (count / num_table_rows)
date_profiled	DATETIME2	Timestamp of profiling run

Results are sorted by count DESC, value ASC (most frequent values first).

Usage Examples

Profile Tables by Pattern

Profile only tables matching a specific pattern:

-- Profile all tables starting with 'Customer'
-- e.g. Customer, CustomerAddress, CustomerOrders
EXEC dbo.SLAM_Alpha_profile_data
    @table_match = 'Customer%'

-- View table summaries
SELECT * FROM dbo.profile_tables

-- View column statistics for a specific table
SELECT * FROM dbo.profile_columns
WHERE table_name = 'CustomerAddress'

Incremental Profiling

Profile tables incrementally across multiple runs:

-- Profile tables starting with 'A'
EXEC dbo.SLAM_Alpha_profile_data @table_match = 'A%'

-- Later, profile tables starting with 'B' (doesn't re-profile 'A' tables)
EXEC dbo.SLAM_Alpha_profile_data @table_match = 'B%'

-- View all profiled tables
SELECT * FROM dbo.profile_tables ORDER BY schema_name, table_name

Refresh Existing Profiling Data

Re-profile tables that have already been profiled:

-- Re-profile all previously profiled tables
EXEC dbo.SLAM_Alpha_profile_data @refresh_existing = 1

-- Re-profile only Customer tables
EXEC dbo.SLAM_Alpha_profile_data
    @table_match = 'Customer%',
    @refresh_existing = 1

Adjust Value Profiling Limit

Profile value distributions for columns with more distinct values:

-- Profile with higher value limit (up to 60)
EXEC dbo.SLAM_Alpha_profile_data
    @value_limit = 60

-- This will profile value distributions for columns with up to 60 distinct values

Complete Example with All Parameters

-- Profile tables in dbo schema starting with 'SF_', refresh existing, max value limit
EXEC dbo.SLAM_Alpha_profile_data
    @table_match = 'SF_%',
    @schema_match = 'dbo',
    @refresh_existing = 1,
    @value_limit = 60

Data Type Handling

Numeric Types (int, bigint, decimal, float, money, etc.)

• min_value, max_value, average_value: Calculated and stored as strings
• average_value: Rounded to 2 decimal places for money types, 4 for others
• num_zero: Count of rows where value = 0
• max_length: NULL (not applicable)

Date/Time Types (date, datetime, datetime2, time, etc.)

• min_value, max_value: ISO 8601 format (YYYY-MM-DD or YYYY-MM-DD HH:MM:SS.mmm)
• average_value: NULL (not applicable)
• num_zero: NULL (not applicable)

String Types (char, varchar, nchar, nvarchar, text)

• min_value, max_value: Lexicographic ordering (case-sensitive)
• max_length: Character count (excludes trailing spaces)
• num_zero: Count of rows where value = '0' (string zero)
• num_blank: Count of rows where value is empty or whitespace-only

Binary Types (binary, varbinary, image)

• min_value, max_value, average_value: NULL (not applicable)
• max_length: Byte count
• Value profiling: Skipped (no rows in profile_values)

NULL and Blank Definitions

Metric	Definition
num_null	Rows where column IS NULL (all data types)
num_blank	Rows where column is empty string or whitespace-only after trimming (string types only, 0 for other types)
num_blank_or_null	num_null + num_blank
num_filled	num_table_rows - num_null (blanks count as filled)

Whitespace vs Empty String in Distinct Counts

The num_distinct value in profile_columns and the values in profile_values treat whitespace-only strings (e.g., ' ') as distinct from empty strings (''). This differs from SQL Server's COUNT(DISTINCT column_name), which treats whitespace and empty strings as equivalent values.

For example, if a column contains both '' and ' ', these are counted as 2 distinct values, while SQL's COUNT(DISTINCT) would count them as 1.

Value Profiling Rules

• Values are profiled only if num_distinct <= @value_limit
• NULL values are included in value profiling with their own row
• String values longer than 255 characters are truncated to 252 + '...'
• Columns exceeding the value limit are skipped entirely (no rows in profile_values)