sp_DataProfile

Point a single stored procedure at a table and get an instant profile of your data — no hand-written queries required.

What you get:

• Column metadata: type, length, precision, scale, nullability, collation.
• NULL and uniqueness: distinct/unique counts and ratios, NULL counts and ratios, min/max length.
• Statistics: min, max, mean, median, and standard deviation for numeric and date/time columns.
• Candidate key checks: tell whether a set of columns forms a unique key.
• Value distributions: every distinct value in a column with its count and percentage.
• Optional foreign keys and indexes, in any mode.

Quickstart

1. Install — open sp_DataProfile.sql in SQL Server Management Studio (SSMS), connect to your instance, and execute (F5). It creates dbo.sp_DataProfile in master, so you can call it from any database.
2. Run it against any table:

   sp_DataProfile 'Users', 0;

3. See what the output looks like below.

Contents

• Requirements
• Usage
• What the output looks like
• Parameters
• Examples
• Notes
• Contributing
• License

Requirements

• SQL Server 2012 or higher (the proc refuses to run on 2008 R2 and older).
• Median calculations (Mode 2) require compatibility level 110 or higher, since they rely on PERCENTILE_DISC. Compatibility level is per-database, so a database set to a lower compat level runs fine but skips the median column.

The examples below target the StackOverflow sample database (Users, Posts), so you can reproduce them as-is.

Usage

sp_DataProfile @TableName, @Mode, @ColumnList, ...

The behavior is driven by @Mode:

Mode	Name	Description
0	Table Overview	Row count plus per-column type, length, precision, scale, nullability, and collation. (default) — example
1	Column Detail	Adds number of unique values, unique ratio, NULL count, NULL ratio, and min/max length per column. — example
2	Column Statistics	Min, max, mean, median, and standard deviation for numeric and date/time columns. — example
3	Candidate Key Check	Given a @ColumnList, reports duplicate combinations so you can tell whether the columns form a unique key. — example
4	Column Value Distribution	Given a single column, reports each distinct value with its count and percentage of the table. — example

You can set @ShowForeignKeys = 1 and/or @ShowIndexes = 1 in any mode to also return the table's foreign keys and indexes.

What the output looks like

Every call first returns a table header result set:

object_id	schema_name	table_name	row_count	is_sample
901578250	dbo	Users	2465713	False

Then a per-column result set. The columns shown depend on the mode (see the mode table for which metrics each mode adds); real output has one row per column.

Mode 0 — Table Overview (metadata only):

column_id	name	system_type	length	precision	scale	is_nullable
1	Id	int	4	10	0	0
3	CreationDate	datetime	8	23	3	0
4	DisplayName	nvarchar	40	0	0	0
5	Reputation	int	4	10	0	0
…

Mode 1 — Column Detail (adds uniqueness and NULL metrics):

name	num_unique_values	unique_ratio	num_nulls	nulls_ratio	min_length	max_length
Id	2465713	1.00000	0	0.00000	4	4
DisplayName	2088731	0.84709	0	0.00000	1	40
Age	78	0.00003	1631503	0.66167	4	4
WebsiteUrl	356198	0.14446	1900011	0.77058	0	200
…

Mode 2 — Column Statistics (adds min/max/mean/median/stddev for numeric and date/time columns):

name	min_value	max_value	mean	median	std_dev
Reputation	1	1041991	137.14	1	2103.55
Age	13	99	34.82	32	12.91
CreationDate	2008-07-31	2018-12-02
…

Parameters

Parameter	Type	Default	Notes
@TableName	NVARCHAR(500)	(required)	Table to profile. Accepts schema.table; defaults to the dbo schema if none is given.
@Mode	TINYINT	0	One of the modes above (0–4).
@ColumnList	NVARCHAR(4000)	NULL	Comma-separated column list. Required for Modes 3 and 4. Mode 4 uses only the first column supplied.
@DatabaseName	NVARCHAR(128)	current DB	Profile a table in another database on the same instance.
@ShowForeignKeys	BIT	0	Also return incoming and outgoing foreign keys.
@ShowIndexes	BIT	0	Also return indexes, including key/included columns and filter definitions.
@SampleValue	INT	NULL	Sample the table instead of scanning it all. Value between 0 and 100.
@SampleType	NVARCHAR(50)	'PERCENT'	'PERCENT' or 'ROWS', applied via TABLESAMPLE.
@ExactRowCount	BIT	0	Force an exact COUNT_BIG(*) row count. Off by default, the row count is read from table metadata (sys.dm_db_partition_stats) — near-instant, no scan. Sampling forces this on automatically.
@ApproxDistinct	BIT	0	Use APPROX_COUNT_DISTINCT (SQL Server 2019+) for distinct/unique counts in Modes 1 and 4; falls back to COUNT(DISTINCT) on older versions.
@Verbose	BIT	0	Print the generated dynamic SQL and progress messages for debugging.

Note on sampling: when @SampleValue is set, counts and ratios are computed against the sampled rows, not the whole table. TABLESAMPLE is page-based, so on small tables it may return all rows or none.

Examples

-- Table overview
sp_DataProfile 'Users', 0;

-- Overview with indexes and foreign keys
sp_DataProfile 'Users', 0, @ShowIndexes = 1, @ShowForeignKeys = 1;

-- Column detail (unique counts, nulls, min/max length)
sp_DataProfile 'Users', 1;

-- Column detail across several columns (unique counts, nulls, min/max length)
sp_DataProfile 'Posts', 1, 'AnswerCount, CreationDate', @ApproxDistinct = 1;

-- Column statistics using a 10% sample of the table
sp_DataProfile 'Users', 2, @SampleValue = 10;

-- Candidate key check across several columns
sp_DataProfile 'Users', 3, 'DisplayName, Location, WebsiteUrl, CreationDate';

-- Value distribution for a single column
sp_DataProfile 'Posts', 4, 'PostTypeId';

-- Profile a table in another database
sp_DataProfile 'Users', 1, @DatabaseName = 'StackOverflow';

-- Force an exact row count instead of the fast metadata read
sp_DataProfile 'Users', 0, @ExactRowCount = 1;

-- Approximate distinct counts (fast on large tables, SQL Server 2019+)
sp_DataProfile 'Posts', 1, @ApproxDistinct = 1;

Notes

• The proc runs under READ UNCOMMITTED, so it won't block writers — at the cost of possible dirty reads.
• By default the row count is read from table metadata (sys.dm_db_partition_stats), so Mode 0 is near-instant even on huge tables and no scan is needed. Set @ExactRowCount = 1 (or use sampling) to force a real COUNT_BIG(*).
• Mode 2 computes min/max and mean/standard deviation in a single scan per column, and COUNT(DISTINCT) is skipped on (max) LOB columns (nvarchar(max), varchar(max), varbinary(max)) where it is expensive and rarely meaningful.
• Modes 1 and 2 still scan the table once per metric per column, which can be expensive on wide or large tables. See docs/analysis.md for a deeper look at behavior, known gaps, and the performance roadmap.

Contributing

Development is tracked through GitHub Issues — bug reports, feature ideas, and pull requests are all welcome. Commit messages reference the issue they resolve (e.g. Fixed #15); please keep that convention in PRs.

License

Released under the MIT License. © 2026 Jorriss LLC.

Source: https://github.com/Jorriss/sp_DataProfile