I have a large number of tables that may change, may have infrequent changes in index, etc. I may well hand this off to somebody else and I don't want indexes to become overly fragmented, slowing the system to a crawl. So, I began hunting and found this:
With some changes to the "WHERE" criteria I was able to isolate my table of tables/indexes
SELECT USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) OwnerName, OBJECT_NAME( i.id ) [TableName], i.name AS [IndexName], CASE INDEXPROPERTY( i.id , i.name , 'IsClustered') WHEN 1 THEN 'YES' ELSE 'NO' END [IsClustered], CASE INDEXPROPERTY(i.id, i.name, 'IsUnique') WHEN 1 THEN 'YES' ELSE 'NO' END [IsUnique], STATS_DATE( i.id , i.indid ) AS LastUpdatedDate FROM sysindexes i WHERE USER_NAME(OBJECTPROPERTY(i.id, 'OwnerID')) = 'dbo' and INDEXPROPERTY( i.id , i.name , 'IsClustered') = 1 ORDER BY OwnerName, TableName, IndexName
I know that I can loop through this result set using dynamic SQL and apply this:
ALTER INDEX Index_Name ON Table_Name REBUILD PARTITION = ALL WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, ONLINE = OFF, SORT_IN_TEMPDB = OFF )
Where "Index_Name" and "Table_Name" are substituted for each time the line is run, and while this seems fine and good I can't help but wonder if there is a better solution. Can I avoid cursors and dynamic SQL in this case?