>>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
>

Thanks, Sergey, but I think you misunderstand what I am trying to do. The code needs to be MORE dynamic, not less. I want this query to be able to be ran for any DB from one specific DB. The SP will be in only that database, but can be ran for any database based on a passed parameter. The Select statement I originally posted was a 'cleaned' version designed for anyone to test out. Here is some of the actual SP code to give you a better idea of what I mean (@DBName is passed to the SP):

set @SQL =  'SELECT ''' + @DBName + ''' DBNAME
	, O.Name TableName
	, I.Name IndexName
	, ''DBCC INDEXDEFRAG (' + @DBName + ', '' + O.Name + '', '' + I.Name + '')'' IndexDFCommand
	, CAST(0 AS BIT) IsDefragged
	FROM ' + @DBName + '.dbo.SysObjects O
		INNER JOIN ' + @DBName + '.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
	ORDER BY TableName
		, IndexName'
PRINT @SQL
EXECUTE sp_ExecuteSQL @SQL

As I said before, if you copy my original statement into QA and run it in the Northwind DB, it will work, but not while in any other DB. What I want the query to do is (in this case) return all Northwind data reqardless of what database is open.

TIA,
-Justin