You are here:Home » tsql » Find Selectivity of each column of every tables in database

Find Selectivity of each column of every tables in database

create table indexdetails (tablename varchar(200),indexname varchar(300), indexdesc varchar(300), indexkeys varchar(300),TotalRows bigint,selectivity numeric(18,2))

declare @tablename varchar(100)
DECLARE index_cursor CURSOR FOR
SELECT name from sysobjects where xtype = 'U'
open index_cursor
FETCH NEXT FROM index_cursor
INTO @tablename
WHILE @@FETCH_STATUS = 0
BEGIN
insert into indexdetails (indexname,indexdesc,indexkeys) exec sp_helpindex @tablename
update indexdetails set tablename = @tablename where tablename IS NULL
FETCH NEXT FROM index_cursor
INTO @tablename
end
close index_cursor
deallocate index_cursor



DECLARE @sql varchar(8000)
DECLARE @tablename VARCHAR(4000)
DECLARE @indexkeys VARCHAR(4000)
DECLARE @indexname VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select tablename,indexkeys,indexname from indexdetails where indexname like 'IX%' and indexkeys not like '%,%'
OPEN c_bak
FETCH NEXT FROM c_bak INTO @tablename, @indexkeys, @indexname
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'declare @DistinctRows numeric (18,2)
declare @TotalRows numeric (18,2)
declare @Selectivity numeric (18,2)
set @DistinctRows = (select count(distinct '+@indexkeys+') from '+@tablename+')
set @TotalRows = (select count('+@indexkeys+') from '+@tablename+')

if @TotalRows > 0
begin
select @Selectivity = @DistinctRows/@TotalRows

update indexdetails set TotalRows = @TotalRows , selectivity = @Selectivity where indexname = '''+@indexname+'''
end
'
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @tablename, @indexkeys, @indexname
END
CLOSE C_BAK
DEALLOCATE C_BAK



select * from indexdetails where selectivity > 0.50
order by totalrows desc


select * from indexdetails where selectivity < 0.50
order by totalrows desc

select 'drop index '+indexname+' on '+tablename from indexdetails where selectivity < 0.50
order by totalrows desc

-------------
create table columndetails (tablename varchar(200),columnname varchar(300),TotalRows bigint,selectivity numeric(18,2))


DECLARE @sql varchar(8000)
DECLARE @tablename VARCHAR(4000)
DECLARE @indexkeys VARCHAR(4000)
DECLARE @indexname VARCHAR(4000)
declare c_bak cursor FAST_FORWARD FOR
select distinct a.table_name, a.column_name from information_schema.columns a
left outer join indexdetails b on (b.tablename = a.table_name and b.indexkeys = a.column_name)
where b.indexkeys is null
order by 2
OPEN c_bak
FETCH NEXT FROM c_bak INTO @tablename, @indexkeys
WHILE @@FETCH_STATUS = 0
BEGIN
SET @SQL = 'declare @DistinctRows numeric (18,2)
declare @TotalRows numeric (18,2)
declare @Selectivity numeric (18,2)
set @DistinctRows = (select count(distinct '+@indexkeys+') from '+@tablename+')
set @TotalRows = (select count('+@indexkeys+') from '+@tablename+')

if @TotalRows > 0
begin
select @Selectivity = @DistinctRows/@TotalRows

insert into columndetails
select '''+@tablename+''' ,'''+@indexkeys+''',@TotalRows,@Selectivity
end

'
PRINT (@SQL)
EXEC(@SQL)
FETCH NEXT FROM c_bak INTO @tablename, @indexkeys
END
CLOSE C_BAK
DEALLOCATE C_BAK




select * from columndetails
where selectivity > 0.50
order by Totalrows desc, selectivity desc