quote:

---

Originally posted by Groaning

if you're anywhere near London we might take you up on the offer...

---

SELECT	[Database] = db.name,
	[Object ID] = ps.OBJECT_ID,
	[Name] = OBJECT_NAME(ps.OBJECT_ID),
	[Index ID] = ps.index_id,
	[Partition No.] = ps.partition_number,
	[Fragmentation = ps.avg_fragmentation_in_percent,
	[Page count] = ps.page_count
FROM	sys.databases AS db
	INNER JOIN sys.dm_db_index_physical_stats (NULL, NULL, NULL , NULL, N'Limited') AS ps
		ON db.database_id = ps.database_id
WHERE	    ps.index_id > 0 
	AND ps.page_count > 100 
	AND ps.avg_fragmentation_in_percent > 30
	AND db.name = 'MyDatabaseName'
OPTION (MaxDop 1)

SET NOCOUNT ON
SELECT	'PRINT ''Reindex ' + [name] + ''''
	+ CHAR(13)+CHAR(10) + 'GO'
	+ CHAR(13)+CHAR(10) + 'ALTER INDEX ALL ON dbo.[' + [name] + '] REBUILD '
	+ 'WITH (ONLINE=OFF, STATISTICS_NORECOMPUTE = OFF)' 
	+ CHAR(13)+CHAR(10) + 'GO'
FROM sys.tables 
ORDER BY [name]
--
SELECT	'PRINT ''Update Stats ' + [name] + ''''
	+ CHAR(13)+CHAR(10) + 'GO'
	+ CHAR(13)+CHAR(10) + 'UPDATE STATISTICS dbo.[' + [name] + '] WITH FULLSCAN' 
	+ CHAR(13)+CHAR(10) + 'GO'
FROM sys.tables 
ORDER BY [name]
--
SET NOCOUNT OFF