Recently had a need to ensure that some tables were indexed in a particular order, mostly due to a very small maintenance window. I did not want to create any supporting tables to do this, so we quickly modified the reindex script to include a priority and list the tables in the query. If there were more than just a handful, our thought was to use the extended properties of each table to add a priority, and then order by that priority for the reindexing.
The quick way for a few tables was just changing the select statement to:
SELECT table_name, table_schema,
Case when table_name in ('Client','conflict') Then 1
When table_name in ('appointment') then 2
Else 3 End As Priority
FROM information_schema.tables a
WHERE a.table_type = 'BASE TABLE'
AND a.table_name <> 'dtproperties'
AND NOT (a.table_name LIKE 'tmp%')
ORDER BY Priority,table_name
If you have many tables to do, and you need very fine grain control, than use the extended properties, add one to each table that you need to control. It is not necessary to add it to every table unless you want to control the order of all. Below is the script example to do this for two tables.
Create Table BobTest (bobTestId int identity(1,1) primary key,
Create Table z_ReindexFirst (testid int identity(1,1) primary key)
EXEC sys.sp_addextendedproperty @name=N'ReindexPriority', @value=N'1' ,
EXEC sys.sp_addextendedproperty @name=N'ReindexPriority', @value=N'2' ,
SELECT CAST(e.name AS VARCHAR(1000)) As ext_prop_name,
Coalesce(CAST(e.value AS VARCHAR(1000)),0) As ext_prop_value,
FROM sys.tables AS t
LEFT OUTER JOIN sys.extended_properties AS e
ON t.[object_id] = e.major_id
AND e.minor_id = 0
WHERE e.name is null or e.name='ReindexPriority'
ORDER BY e.value desc,t.name
drop table bobTest
drop table z_ReindexFirst