November 4, 2010 at 8:04 am
We have a situation where some of our tables between production, test, and development are not in sync regarding indexes: i.e. a production table might have two indexes, but the table in test only has one index.
I'm not familiar with how the definition of a index is stored in sql, but I'm thinking that there's probably a way to query the information out of various system tables/views.
Using a sql query, I'd like to know if it's possible to identify a table's index definitions (i.e., name of index, type of index (non/clustered), which fields are part of the index, as well as which fields are "included"). If this can be done, then it should be possible via sql to apply these definitions to copies of the table elsewhere such that all instances of a given table are in-sync regarding the same indexes.
The goal is to ensure that all tables are in-sync regarding indexes between production, test, and development.
November 4, 2010 at 8:23 am
Start with sys.indexes and sys.index_columns. Read up on those, and it'll get you started.
Really though, I'd get a product like RedGate's SQL Compare or ApexSQL's Diff, which can compare databases on different servers, and generate synchronization scripts for you. They've already done the work, and it'll compare a lot more than just index data. Makes migrations from Dev to QA to Prod much easier.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply