November 3, 2022 at 2:24 am
How can I find table cardinality stats
November 3, 2022 at 2:28 am
SET NOCOUNT ON
-- Specify the table here - this is the only place you need to modify.
DECLARE @SchemaName sysname SET @SchemaName = 'dbo'
DECLARE @TableName sysname SET @TableName = 'Cardinality'
-- Declare variables.
DECLARE @CrLf CHAR(2) SET @CrLf = CHAR(13) + CHAR(10)
DECLARE @Sql NVARCHAR(MAX) SET @Sql = ''
DECLARE @ColumnName sysname SET @ColumnName = ''
-- Show total number of rows in table.
SET @Sql = 'SELECT COUNT(*) AS "Row Count for ''' + @TableName + '''" FROM ' + @SchemaName + '.' + @TableName
EXEC sp_executesql @Sql
-- Calculate selectivity as "cardinality / row count" for each column.
DECLARE cur CURSOR FOR
SELECT name
FROM sys.columns
WHERE object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
AND is_identity = 0
OPEN cur
FETCH NEXT FROM cur INTO @ColumnName
WHILE @@FETCH_STATUS = 0
BEGIN
RAISERROR('Processing column: %s', 10, 1, @ColumnName) WITH NOWAIT
SET @Sql = 'SELECT ''' + QUOTENAME(@ColumnName) + ''' AS ''Column'' ' + @CrLf
+ ' , COUNT(DISTINCT ' + QUOTENAME(@ColumnName) + ') AS ''Cardinality'' ' + @CrLf
+ ' , LEFT((COUNT(DISTINCT ' + QUOTENAME(@ColumnName) + ') * 1.0) / ' + @CrLf
+ ' NULLIF(COUNT(*), 0), 6) AS ''Selectivity'' ' + @CrLf
+ ' FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) + ' ' + @CrLf
+ ' WHERE ' + QUOTENAME(@ColumnName) + ' IS NOT NULL ' + @CrLf
+ '' + @CrLf
EXEC sp_executesql @Sql
FETCH NEXT FROM cur INTO @ColumnName
END
CLOSE cur
DEALLOCATE cur
November 3, 2022 at 11:44 am
Seems like a lot of work.
That does it.
You can also see some of this info from sys.dm_db_stats_properties, or, if you're on 2016, go straight to sys.dm_db_status_histogram to query directly for specific values.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply