July 7, 2009 at 2:50 pm
DB Gurus,
Our production database indexes have goine out of sync with the reporting database. (Pls don't ask how :-)). Now the requirement is to have the add those indexes from the reporting db to the production db.
I am using the Red-Gate SQL Compare, however i want to base my compare on the column structure and not the names. It could be possible that a particular index is based on certain columns in both databases but its just referred by different names, i do not want to apply this index to production because it already exists but with a different name.
For example:
CREATE INDEX [idx_reportdb] ON
I do not want such indexes to be a part of my comparision report.
Has anyone of you dealt with this situation before? What would be the best way to achieve this using T-SQL or any other method?
Any ideas, thoughts are much appreciated.
Thanks,
Amol
Amol Naik
July 8, 2009 at 8:17 am
I don't know how you are going to compare them then because the name is not the same, nor is the index_id going to match at that point. I think the closest you can come is something like this:
SELECT
*
FROM
(
SELECT
'Production' AS location,
I.[name] AS index_name,
O.[name] AS table_name,
C.[name] AS column_name,
IC.index_column_id,
IC.is_descending_key,
IC.is_included_column
FROM
sys.indexes AS I JOIN
sys.index_columns AS IC ON I.[object_id] = IC.[object_id] AND I.index_id = IC.index_id JOIN
sys.columns AS C ON IC.[object_id] = C.[object_id] AND IC.column_id = C.column_id JOIN
sys.objects AS O ON C.[object_id] = O.[object_id]
) AS Production FULL OUTER JOIN
(
SELECT
'REporting' AS location,
I.NAME AS index_name,
O.[name] AS table_name,
C.[name] AS column_name,
IC.index_column_id,
IC.is_descending_key,
IC.is_included_column
FROM
AD2.sys.indexes AS I JOIN
AD2.sys.index_columns AS IC ON I.[object_id] = IC.[object_id] AND I.index_id = IC.index_id JOIN
AD2.sys.columns AS C ON IC.[object_id] = C.[object_id] AND IC.column_id = C.column_id JOIN
AD2.sys.objects AS O ON C.[object_id] = O.[object_id]
) AS reporting ON
Production.column_name = reporting.column_name AND Production.index_column_id = reporting.index_column_id AND PRoduction.is_descending_key = reporting.is_descending_key AND PRoduction.is_included_column = reporting.is_included_column AND PRoduction.table_name = reporting.table_name
WHERE
Production.Location IS NULL OR
reporting.location IS NULL
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply