September 13, 2010 at 3:24 pm
How do we compare indexes present on two different databases.
What we are trying to achieve here is when we do release from one environment to other we have to make sure we dont miss anything in database so we have to compare indexes.
Appreciate your suggestions
Regards,
Ather
September 13, 2010 at 3:31 pm
Redgate's SQL Compare is good here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2010 at 6:26 pm
I'll second Gail on this!
RedGate tools fill a neat gap in the Visual Studio / SSMS world.
September 14, 2010 at 6:02 pm
Thanks for quick reply
Actually I am not looking for any tool....
I am looking...using SQL job, proc etc ?
September 14, 2010 at 6:32 pm
The reason people are recommending a tool is because there isn't a good way to do this natively within SQL Server. You can write something, but you'll easily burn more than $300 in time trying to get it written, debugged, and be sure it works.
The only way this can be done is to load up all indexes into a table, one for each server, and then run a series of queries to look for missing items. It's essentially a full outer join, checking for matches in columns, order, etc. Not something you can whip up easily.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 14, 2010 at 8:24 pm
There are queries for retrieving the indexes, but you would have to run it on both databases - and compare the results. That method is a slow process.
If you purchase a tool, the process is much faster as the differences are highlighted very quickly for you by the tool.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 14, 2010 at 8:35 pm
From the script front, here is something to get you moving in the right direction.
This script does not account for include columns. It would need to be modified for that. It also does not generate the create statements - that would have to be added as well.
With IndexStuff as (
SELECT
icol.object_id
,i.name
,icol.index_id
,STUFF(
(
SELECT ',' + c.name AS [text()]
FROM sys.index_columns ic
Inner Join sys.columns c
ON ic.object_id = c.object_id
AND ic.column_id = c.column_id
WHERE ic.object_id = icol.object_id
AND ic.index_id = icol.index_id
ORDER BY ic.key_ordinal
FOR XML PATH('')
), 1, 1, '') AS ColList
FROM sys.index_columns icol
Inner Join sys.indexes i
ON icol.object_id = i.object_id
AND icol.index_id = i.index_id
GROUP BY icol.object_id, i.name, icol.index_id
)
SELECT
SchemaName = USER_NAME(OBJECTPROPERTY(i.object_id, 'OwnerID')),
--ObjectID = i.object_id,
ObjectName = OBJECT_NAME(i.object_id),
ObjectType = CASE OBJECTPROPERTY(i.object_id, 'IsTable')
WHEN 1 THEN 'Table' ELSE 'View' END,
IndexID = i.index_id,
IndexName = i.name,
IndexColumns = dt.ColList,
IsClustered = CASE i.type
WHEN 1 THEN 'YES' ELSE 'NO' END,
IsUnique = CASE i.is_unique
WHEN 1 THEN 'YES' ELSE 'NO' END,
LastUpdatedDate = STATS_DATE(i.object_id, i.index_id)
FROM sys.indexes AS i
Inner Join IndexStuff dt
ON dt.object_id = i.object_id
AND dt.index_id = i.index_id
WHERE OBJECTPROPERTY(i.object_id, 'IsMSShipped') = 0
AND INDEXPROPERTY(i.object_id, i.name, 'IsStatistics') = 0
AND INDEXPROPERTY(i.object_id, i.name, 'IsAutoStatistics') = 0
AND INDEXPROPERTY(i.object_id, i.name, 'IsHypothetical') =0
AND i.index_id BETWEEN 1 And 250
ORDER BY SchemaName, ObjectName, Indexid
If you ran this on both databases and loaded a servername and dbname into the table with the results of these queries, it would simplify the process a little.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
September 16, 2010 at 8:06 am
Thanks alot for all of you.
I will try this script posted by jason and surely will let you ppl know....
Thanks again 🙂
September 16, 2010 at 12:38 pm
You're welcome.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply