>>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 >>>>
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 @SQLAs 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.