>>>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
All Fixed! For those of you interested in the solution I will post the whole SP below - feel free to use it!
IF EXISTS
(
SELECT *
FROM dbo.SysObjects
WHERE ID = OBJECT_ID(N'uspdba_DefragAllIndexesForDB')
AND OBJECTPROPERTY(ID, N'IsProcedure') = 1
)
DROP PROCEDURE uspdba_DefragAllIndexesForDB
GO
CREATE PROCEDURE uspdba_DefragAllIndexesForDB
@DBName VARCHAR(100)
AS
DECLARE @DBTable VARCHAR(200)
, @SQLDropTable NVARCHAR(500)
, @SQLSelectMain NVARCHAR(2000)
, @SQLSelectMaxIndex NVARCHAR(500)
, @SQLSelectCommand NVARCHAR(500)
, @SQLUseDB NVARCHAR(500)
, @Indexes INT
, @MaxIndexes INT
, @MI_Param NVARCHAR(100)
, @SC_Param NVARCHAR(100)
, @IDFCommand NVARCHAR(500)
SET @DBTable = 'tbldba_' + @DBName + 'IndexDefrag'
SET @Indexes = 1
SET @MI_Param = '@MaxIndexesOUT INT OUTPUT'
SET @SQLUseDB = 'USE ' + @DBName
SET @SQLDropTable = @SQLUseDB + '
DROP TABLE ' + @DBTable + ''
SET @SQLSelectMain =
(
@SQLUseDB + '
SELECT ''' + @DBName + ''' DBNAME
, O.Name TableName
, I.Name IndexName
, ''DBCC INDEXDEFRAG (' + @DBName + ', '' + O.Name + '', '' + I.Name + '')'' IndexDFCommand
, CAST(0 AS BIT) IsDefragged
FROM ' + @@SERVERNAME + '.' + @DBName + '.dbo.SysObjects O
INNER JOIN ' + @@SERVERNAME + '.' + @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'
)
/* Notes:
OBJECTPROPERTY(id, 'IsMSShipped') = 0: Leaves out indexes on system tables
(DBCC INDEXDEFRAG is not supported on system tables)
IndID NOT IN (0, 255): Leaves out tables without an index (indid = 0)
and indexes for text, ntext and image columns (indid = 255)
INDEXPROPERTY(id, name, 'IsStatistics') = 0: leaves out entries in sysindexes
that are actually statistics and not indexes. Statistics can be updated with UPDATE STATISTICS.
INDEXPROPERTY(id, name, 'IsHypothetical') = 0: leaves out (temporary) indexes created by the
Index Tuning Wizard to aid in performance tuning
INDEXPROPERTY(id, name, 'IsFulltextKey') : leaves out full text indexes,
they are handled indepently of normal SQL Server indexes
*/
SET @SQLSelectMaxIndex = @SQLUseDB + '
SELECT @MaxIndexesOUT = MAX(IndexDFID)
FROM ' + @DBTable + ' (NOLOCK)'
IF EXISTS
(
SELECT *
FROM SysObjects
WHERE ID = OBJECT_ID(@DBTable)
AND OBJECTPROPERTY(ID, 'IsUserTable') = 1
)
EXECUTE sp_ExecuteSQL @SQLDropTable
EXECUTE sp_ExecuteSQL @SQLSelectMain
EXECUTE sp_ExecuteSQL @SQLSelectMaxIndex
, @MI_Param
, @MaxIndexesOUT = @MaxIndexes OUTPUT
/*Debug*/
WHILE @Indexes <= @MaxIndexes
BEGIN
SET @SQLSelectCommand = @SQLUseDB + '
SELECT @IDFCommandOUT = IndexDFCommand
FROM ' + @DBTable + ' (NOLOCK)
WHERE IndexDFID = ' + CAST(@Indexes AS VARCHAR(50)) + ''
SET @SC_Param = '@IDFCommandOUT VARCHAR(500) OUTPUT'
/*Debug*/
EXECUTE sp_ExecuteSQL @SQLSelectCommand
, @SC_Param
, @IDFCommandOUT = @IDFCommand OUTPUT
PRINT 'Executing ' + @IDFCommand + '...'
EXECUTE sp_ExecuteSQL @IDFCommand
SET @Indexes = @Indexes + 1
END
PRINT 'DONE!'
GO
