----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_DBCC_DBREINDEX
---- INPUTS    : @dbName - name of the database
-- OUTPUTS    : None
-- DEPENDENCIES         : None
--
-- DESCRIPTION         : This stored procedure runs DBCC DBREINDEX for each of the indexes in the database.
--
-- EXAMPLES (optional)  : EXEC sp_DBCC_DBREINDEX @dbName = 'CDS_Systems'
----------------------------------------------------------------------------------------------------
CREATE            PROC sp_DBCC_DBREINDEX
(@dbName SYSNAME)
AS
SET NOCOUNT ON

DECLARE @objName SYSNAME
DECLARE @idxName SYSNAME
DECLARE @SQL NVARCHAR(4000)
DECLARE @ID INT
DECLARE @RowCnt INT

CREATE TABLE ##Indexes
(
 Indexes_ID INT IDENTITY(1, 1) NOT NULL, 
 IndexName SYSNAME NOT NULL, 
 ObjectName SYSNAME NOT NULL
)

SET @SQL = ''
SET @SQL = @SQL + 'INSERT INTO ##Indexes (IndexName, ObjectName) '
SET @SQL = @SQL + 'SELECT i.name, o.name '
SET @SQL = @SQL + 'FROM ' + @dbName + '.dbo.sysindexes i ' 
SET @SQL = @SQL + 'INNER JOIN ' + @dbName + '.dbo.sysobjects o '
SET @SQL = @SQL + 'ON i.id = o.id '
SET @SQL = @SQL + 'WHERE indid > 0 AND indid < 255 AND '
SET @SQL = @SQL + 'o.type = ''U'' AND '
SET @SQL = @SQL + '(i.status & (64 | 8388608)) <= 0'

EXEC sp_executesql @statement = @SQL

SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
FROM ##Indexes
ORDER BY Indexes_ID

SET @RowCnt = @@ROWCOUNT

WHILE @RowCnt <> 0
BEGIN

 SET @SQL = 'DBCC DBREINDEX(''' + @dbName + '.dbo.' + @objName + ''', ' + @idxName + ', 0) WITH NO_INFOMSGS'

 EXEC sp_executesql @statement = @SQL

 SELECT TOP 1 @ID = Indexes_ID, @idxName = IndexName, @objName = ObjectName
 FROM ##Indexes
 WHERE Indexes_ID > @ID
 ORDER BY Indexes_ID
 
 SET @RowCnt = @@ROWCOUNT

END

DROP TABLE ##Indexes

RETURN 0








GO

quote:

---

Originally posted by Kristen

"Do i really need ##indexes, i guess i can change to #indexes?"

b) it won't be in scope

Kristen

---

quote:

---

Originally posted by mk_garg20
...Why it will be out of scope?
I feel Michael is right #indexes should work...

---