I need to automate and Sync only indexes between two tables ( Primary and Stage_Table) within the same database.
I tried using SSIS SQL Server Objects Task, but looks like it works only when we sync between two different instances. Indexes on Primary table keep getting changed and I need the empty stage_table with all the updated indexes when I do the partition switch.
Best Answer
You can use opensource tool like sql-dbdiff or OpenDBDiff. Both are commandline, so can be used in automating scripts.
Also, if you want 3rd party licensed tool then Redgate's SQL Compare (if u want for data compare -- there is data compare as well) is very useful and I have used it extensively for automation.
Out of curiosity, why do you need Indexes on Staging table as a staging table is meant for temporary loading data and then after cleaning it, the data gets loaded in the primary table ?
EDIT :
Based on your need, best will be to use SQL Server Partition Management Tool from Codeplex.
This utility provides a command line interface to: 1. Remove all the data from one partition by switching it out to a staging table. It creates the required staging table.2. Create a staging table for loading data into a partition. The staging table can be created with or without indexes -- if created without indexes this utility provides a separate command to create appropriate indexes on the staging table, before SWITCHing it into the partitioned table. The commands can be invoked from other scripts for end-to-end sliding window scenarios. Using the utility allows you to avoid maintaining partition maintenance scripts that must remain synchronized with index or column changes in the permanent table, since necessary staging objects can be created on-demand.