September 22, 2011 at 11:45 pm
can someone help me in comparing the indexes from two database. I have a db Adventureworks, i migrated some table and data(some records from all the tables) to a new db say AdventureWorks_New. Now i want to check whether i missed any index. How can i do that. Could you Please help me.
thanks
September 23, 2011 at 1:17 am
MsSqlNew (9/22/2011)
can someone help me in comparing the indexes from two database. I have a db Adventureworks, i migrated some table and data(some records from all the tables) to a new db say AdventureWorks_New. Now i want to check whether i missed any index. How can i do that. Could you Please help me.thanks
Use this
select * from DB1.sys.indexes a join DB1.sys.objects b
on a.object_id =b.object_id where
a.name in (select c.name from DB1.sys.indexes c join DB1.sys.objects d
on a.object_id =b.object_id)
and b.type<>'s'and b.type<>'it'
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
September 23, 2011 at 1:20 am
i think u can do it,
get all the index names from sys.indexes and put into a temp tables from database1 and database2 and then put a query to just check the difference in both the tables, u come to know the missed indexes.
September 23, 2011 at 4:42 am
Thanks For responding moorthy,
I tried it but it is not showing correct results..Are you sure that it works...?
September 23, 2011 at 5:35 am
MsSqlNew (9/23/2011)
Thanks For responding moorthy,I tried it but it is not showing correct results..Are you sure that it works...?
I just showed the way. You may modify it as you want.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
September 23, 2011 at 6:05 am
You can run below query on both databases and compare the output using some text comparison tool. Don't forget to set your query output to 'Results to Grid' (Ctrl+D), or the generated xml may easily be truncated.
Note: This method has the advantage that is also works to compare differences created over time in the same database (and you may even be able to reconstruct the old situation from the old output).
select (
select
schema_name(o.schema_id) as [@schema],
o.name as [@name],
(
select ix.name as [@name],
case ix.is_unique when 1 then 'true' end as [@is_unique],
case ix.type when 1 then 'true' end as [@is_clustered],
(
select col_name(ic.object_id, ic.column_id) as [@name],
case ic.is_descending_key when 1 then 'true' end as [@is_descending_key]
from sys.index_columns ic
where ic.object_id = ix.object_id
and ic.index_id = ix.index_id
and not ic.is_included_column = 1
order by ic.index_column_id
for xml path('index_column'), type
) [index_columns],
(
select col_name(ic.object_id, ic.column_id) as [@name],
case ic.is_descending_key when 1 then 'true' end as [@is_descending_key]
from sys.index_columns ic
where ic.object_id = ix.object_id
and ic.index_id = ix.index_id
and ic.is_included_column = 1
order by ic.index_column_id
for xml path('include_column'), type
) [include_columns]
from sys.indexes ix
where ix.object_id = o.object_id
order by ix.index_id
for xml path('index'), type
)
where o.type = 'U'
for xml path('table'), type
),
(
select
schema_name(o.schema_id) as [@schema],
o.name as [@name],
(
select ix.name as [@name],
case ix.is_unique when 1 then 'true' end as [@is_unique],
case ix.type when 1 then 'true' end as [@is_clustered],
(
select col_name(ic.object_id, ic.column_id) as [@name],
case ic.is_descending_key when 1 then 'true' end as [@is_descending_key]
from sys.index_columns ic
where ic.object_id = ix.object_id
and ic.index_id = ix.index_id
and not ic.is_included_column = 1
order by ic.index_column_id
for xml path('index_column'), type
) [index_columns],
(
select col_name(ic.object_id, ic.column_id) as [@name],
case ic.is_descending_key when 1 then 'true' end as [@is_descending_key]
from sys.index_columns ic
where ic.object_id = ix.object_id
and ic.index_id = ix.index_id
and ic.is_included_column = 1
order by ic.index_column_id
for xml path('include_column'), type
) [include_columns]
from sys.indexes ix
where ix.object_id = o.object_id
order by ix.index_id
for xml path('index'), type
)
where o.type = 'V'
for xml path('view'), type
)
from sys.objects o
where not o.is_ms_shipped = 1
and exists (
select *
from sys.indexes ix
where ix.object_id = o.object_id
)
order by schema_name(o.schema_id),
o.name
for xml path(''), root('list'), type
edit: changed 'U' into o.type = 'V' for the view part.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply