Rebuilds all indexes on a list of tables provided

sp_rebuild_indexes.sql

use master
go
/*
**	- synopsis -
**	Rebuilds all indexes on a list of tables provided
**
**	- syntax -
**	@ls: an XML object of the format <tables><table>{table name}</table>...</tables>
**	@tbpat: a pattern applied to table names containing SQL wild characters that adds
**		to the list provided via @ls
**	@ixpath: a pattern applied to index names to filter which indices to operate upon
**	@schema: the schema within which objects reside to be operated upon
**	@op: the operation to perform - may be used to indicate REORGANIZE
**	@debug: a true value suppresses execution and displays what would be done
**
**	- exempli gratia -
**	use MyDb
**	go
**	declare @ls xml
**	select	@ls = '<tables><table>TimeCard</table></tables>'
**	exec sp_rebuild_indexes @ls
**	go
*/

if object_id('sp_rebuild_indexes') is not null
	drop proc sp_rebuild_indexes
go
create proc sp_rebuild_indexes
	@ls xml
,	@tbpat varchar(32) = null
,	@ixpat varchar(32) = '%'
,	@schema sysname = 'dbo'
,	@op varchar(32) = 'REBUILD'
,	@debug bit = 1
as
	declare @spin cursor
	set		@spin = cursor for
	select	v.value('.', 'sysname')
	,		si.name
	from	@ls.nodes('/tables/table') t(v)
	join	sys.indexes si
			on si.object_id = object_id('[' + @schema + '].[' + v.value('.', 'sysname') + ']')
	where	si.name like @ixpat
	union
	select	t.name
	,		i.name
	from	sys.tables t
	join	sys.indexes i on i.object_id = t.object_id
	where	t.name like @tbpat
	and		i.name like @ixpat

	open @spin

	declare	@table sysname
	,		@index sysname
	
	while 0 < 1
		begin
		fetch	@spin 
		into	@table, @index
		
		if @@fetch_status < 0 or @@error != 0
			break
			
		declare @sql nvarchar(max)
		select	@sql = 'ALTER INDEX '
		+		'[' + @index + '] '
		+		'ON [' + @schema + '].[' + @table + '] '
		+		@op
		
		if @debug = 1
			print @sql
		else
			exec(@sql)
		end
	deallocate @spin
go
exec sp_MS_marksystemobject 'sp_rebuild_indexes'
go
/*
use TE_3E_NEWPMQ
go
declare @ls xml = '<tables><table>TimeCard</table><table>CostCard</table></tables>'
exec sp_rebuild_indexes @ls, @debug = 1, @ixpat = 'PK%', @tbpat = 'Metric%'
go
*/