>I am building a Stored procedure that when ran will reindex all indexes within a given database. Right now it works fine when ran from the database being reindexed, but I want this SP to reside only in a protected DBA database. The Driving query for this (the base of which I found online, then very heavily modified) is as follows:
>

>SELECT 'Northwind' DBName
>	, O.Name TableName
>	, I.Name IndexName
>	, 'DBCC INDEXDEFRAG (Northwind, ' + O.Name + ', ' + I.Name + ')' IndexDFCommand
>	, CAST(0 AS BIT) IsDefragged
>	FROM Northwind.dbo.SysObjects O
>		INNER JOIN Northwind.dbo.SysIndexes I
>			ON O.ID = I.ID
>	WHERE O.xType = 'U'
>		AND OBJECTPROPERTY(I.ID, 'IsMSShipped') = 0
>		AND I.IndID NOT IN (0, 255)
>		AND INDEXPROPERTY(I.ID, I.Name, 'IsStatistics') = 0
>		AND INDEXPROPERTY(I.ID, I.Name, 'IsHypothetical') = 0
>		AND INDEXPROPERTY(I.ID, I.Name, 'IsFulltextKey') = 0
>

>
>This works fine cross-database so long as the OBJECTPROPERTY and INDEXPROPERTY clauses are removed.
>
>To test this out, run it in Northwind, then any other DB.
>
>Any help would be much appreciated!
>

Justin,

Change the query as shown below and it would work for any database.

SELECT DB_NAME() AS DBName
	, O.Name TableName
	, I.Name IndexName
	, 'DBCC INDEXDEFRAG (' + DB_NAME() + ', ' + O.Name + ', ' + I.Name + ')' IndexDFCommand
	, CAST(0 AS BIT) IsDefragged
	FROM dbo.SysObjects O
		INNER JOIN dbo.SysIndexes I
			ON O.ID = I.ID
	WHERE O.xType = 'U'
		AND OBJECTPROPERTY(I.ID, 'IsMSShipped') = 0
		AND I.IndID NOT IN (0, 255)
		AND INDEXPROPERTY(I.ID, I.Name, 'IsStatistics') = 0
		AND INDEXPROPERTY(I.ID, I.Name, 'IsHypothetical') = 0
		AND INDEXPROPERTY(I.ID, I.Name, 'IsFulltextKey') = 0