Gets the selectivity of all columns in a table or view in a MS SQL database.

getColumnSelectivity.sql

if exists 
(
 	select *
	from sys.objects
	where object_id = OBJECT_ID(N'[dbo].[getColumnSelectivity]')
)
begin
	drop procedure dbo.getColumnSelectivity
end
go

create procedure dbo.getColumnSelectivity 
	@table nvarchar(256)
as
begin
if OBJECT_ID(@table) is null
begin
	return
end

declare @columnsStatement nvarchar(max) = N'select columns.name
from sys.columns
inner join sys.objects
	on objects.object_id = columns.object_id
where objects.name = @tableName'
declare @rowCountStatement nvarchar(max) = N'select @rowCountOUT = count(*) from ' + quotename(@table)
declare @columnCountStatement nvarchar(max);

declare @columnsTable table
(
	column_to_test varchar(max)
	,number_of_distinct_values int
)
declare @rowCount int

--This table contains one row per column in the table.
insert into @columnsTable
(
	column_to_test
)
execute sp_executesql @columnsStatement, N'@tableName nvarchar(256)', @tableName = @table

--Selectivity is number of distinct values / total row count, so this will be the denominator
exec sp_executesql @rowCountStatement, N'@rowCountOUT int OUTPUT', @rowCountOUT = @rowCount OUTPUT

--Check each columns selectivity in turn and record it in the table
declare columnsCursor cursor
for
select column_to_test
from @columnsTable
declare @nextcolumn varchar(max), @nextCount int
open columnsCursor
fetch next from columnsCursor into @nextcolumn
while (@@FETCH_STATUS <> -1) --cursor has reached the end of the array
begin
	set @columnCountStatement = N'select @columnCountOUT = count(distinct ' + quotename(@nextcolumn) + ') from ' + quotename(@table)

	exec sp_executesql @columnCountStatement
						,N'@columnCountOUT int OUTPUT'
						,@columnCountOUT = @nextCount OUTPUT
	update @columnsTable
	set number_of_distinct_values = @nextCount
	where column_to_test = @nextcolumn
fetch next from columnsCursor into @nextcolumn
end

close columnsCursor
deallocate columnsCursor

select column_to_test
		,cast(number_of_distinct_values as float) / cast(@rowCount as float) as selectivity
from @columnsTable
order by cast(number_of_distinct_values as float) / cast(@rowCount as float) desc
end
go