August 17, 2004 at 11:50 am
I just got done tweaking some code that I found on the internet. It runs a cursor through all tables in all of the databases and runs a DBCC INDEXDEFRAG against the indexes. I need another set of eyes to review the code and see if you can find any errors or improvements. Thanks in advance for the help!
--Defrag all of the indexes in the database
DECLARE @database VARCHAR(50)
DECLARE @TableName SYSNAME
DECLARE @indid INT
DECLARE @sql VARCHAR(500)
DECLARE @sql1 VARCHAR(500)
--Temp table to hold all user database names
CREATE TABLE #tmp_tbl_db
(
db_nm SYSNAME
)
CREATE TABLE #tmp_tbl_tbls
(
TableName SYSNAME
)
CREATE TABLE #tmp_tbl_ind_id
(
indid INT
)
--Fill the temp table with everything except tempdb, and model
INSERT INTO #tmp_tbl_db
SELECT NAME FROM master.dbo.sysdatabases WITH (NOLOCK)
WHERE dbid not in (2,3)
--BAE Debugging
--select * from #tmp_tbl_db
--Declare a cursor to get the database names
DECLARE cur_db CURSOR FOR
SELECT db_nm
FROM #tmp_tbl_db
OPEN cur_db
FETCH NEXT FROM cur_db INTO @database
WHILE @@fetch_status = 0
BEGIN
--BAE
PRINT @database
SET @sql = 'insert into #tmp_tbl_tbls select table_name from ' + @database +
'.information_schema.tables where table_type = ''base table'''
select @sql
EXEC(@sql)
select * from #tmp_tbl_tbls
--Declare a cursor for looping through the tables
DECLARE cur_tblfetch CURSOR FOR
SELECT TableName
FROM #tmp_tbl_tbls
--Declare a cursor for looping through the indexes
OPEN cur_tblfetch
FETCH NEXT FROM cur_tblfetch INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql1 = 'insert into #tmp_tbl_ind_id select indid from ' + @database +
'.dbo.sysindexes where id = object_id ('''+ @database + '.dbo.' + @TableName + ''') and keycnt > 0'
select @sql1
exec(@sql1)
select * from #tmp_tbl_ind_id
--Get the index ID
DECLARE cur_indfetch CURSOR FOR
SELECT indid
FROM #tmp_tbl_ind_id
OPEN cur_indfetch
FETCH NEXT FROM cur_indfetch INTO @indid
WHILE @@FETCH_STATUS = 0
BEGIN
--BAE
print @indid
SELECT 'Derfagmenting index_id = ' + convert(char(3), @indid) + 'of the '
+ rtrim(@TableName) + ' table' + 'from the ' + @database
--Execute the DBCC INDEXDEFRAG with the database, table and index ID
IF @indid <> 255 DBCC INDEXDEFRAG (@database, @TableName, @indid)
FETCH NEXT FROM cur_indfetch INTO @indid
END
CLOSE cur_indfetch
DEALLOCATE cur_indfetch
TRUNCATE TABLE #tmp_tbl_ind_id
FETCH NEXT FROM cur_tblfetch INTO @TableName
END
CLOSE cur_tblfetch
DEALLOCATE cur_tblfetch
TRUNCATE TABLE #tmp_tbl_tbls
FETCH NEXT FROM cur_db INTO @database
END
CLOSE cur_db
DEALLOCATE cur_db
August 17, 2004 at 3:04 pm
Haven't run through it all, but I wouldn't depend on the dbid. I'd use the names you want to exclude. I might also insert the results into some log table as opposed to printing to the screen. Might provide a more stable record, especially if your machine were to crash. You'd still know how far you got.
August 18, 2004 at 8:28 am
Re-indexing all the tables on all the databases might be an overkill. You might want to re-index tables which are highly fragmented. I had a script for that somewhere and will try to find it and post it.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply