Thursday, June 02, 2011

Collect every index from DB to compare

if(OBJECT_ID('tempdb..#tmp')>1)
drop table #tmp
create table #tmp (index_name nvarchar(256), index_description nvarchar(500), index_keys nvarchar(500), included_columns nvarchar(1000),filter_definition nvarchar(256))
if(OBJECT_ID('tempdb..#tmp2')>1)
drop table #tmp2
create table #tmp2 (dbname nvarchar(256), tablename nvarchar(256), index_name nvarchar(256), index_description nvarchar(500), index_keys nvarchar(500), included_columns nvarchar(1000),filter_definition nvarchar(256))

declare @tablename nvarchar(256), @sql nvarchar(4000)
declare tmp_cur cursor for
select name from sys.tables where schema_id = 1

open tmp_cur
Fetch next from tmp_cur into @tablename

while @@fetch_status = 0
begin
print @tablename
set @sql = 'insert into #tmp (index_name , index_description , index_keys , included_columns )
exec sp_helpindex2 '''+@tablename+'''

insert into #tmp2 (dbname , tablename, index_name , index_description , index_keys , included_columns )
select db_name(), '''+@tablename+''' , index_name , index_description , index_keys , included_columns from #tmp

truncate table #tmp
'
print @sql
exec sp_executesql @sql

Fetch next from tmp_cur into @tablename
print '---'
end

close tmp_cur
deallocate tmp_cur

drop table __tmp_all_indexes

select *
into __tmp_all_indexes
from #tmp2

Friday, May 20, 2011

Index - sp_helpindex2 to show include columns

Kimberly L. Tripp | sp_helpindex2 to show included columns (2005+) and filtered indexes (2008) which are not shown by sp_helpindex

Determine SQL Server memory use by database and object

Determine SQL Server memory use by database and object

indexing - List of all index & index columns in SQL Server DB - Stack Overflow

indexing - List of all index & index columns in SQL Server DB - Stack Overflow


--select * from sys.indexes
--select * From sys.index_columns

select
ind.name, ind.index_id, ic.index_column_id, col.name as indexname, t.name as tablename,
ind.*, ic.*, col.*
from
sys.indexes ind
inner join
sys.index_columns ic on
ind.object_id = ic.object_id and ind.index_id = ic.index_id
inner join
sys.columns col on
ic.object_id = col.object_id and ic.column_id = col.column_id
inner join
sys.tables t on
ind.object_id = t.object_id
where
ind.is_primary_key = 0
and ind.is_unique = 0
and ind.is_unique_constraint = 0
and t.is_ms_shipped = 0
and col.name = 'status_id'
and t.name = 'user_lo'
order by
t.name, ind.name, ind.index_id, ic.index_column_id