Skip to main content

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

Comments

  1. Great, this is great sharing knowledge. We can surely get this in a presentable format and give it to everyone to follow these practices.
    -Sandeep

    ReplyDelete

Post a Comment