March 26, 2009 at 3:54 pm
I am trying to use this to disable all the non-clustered indexes on all the tables in a DB before a load.
use ASL_DEV_GLD;
GO
DECLARE tables_cursor CURSOR
FOR
SELECT s.name, t.name, t.object_id
FROM sys.objects AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.type = 'U';
OPEN tables_cursor;
DECLARE @schemaname sysname;
DECLARE @tablename sysname;
DECLARE @objectid sysname;
DECLARE @indexname sysname;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename, @objectid;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
DECLARE index_cursor CURSOR
FOR
SELECT name AS iname
FROM sys.indexes
WHERE object_id = @objectid
AND UPPER(type_desc) <> 'CLUSTERED';
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @indexname;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
-- EXEC ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' DISABLE;');
EXEC ('ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @tablename + ' DISABLE;');
FETCH NEXT FROM index_cursor INTO @indexname;
END;
CLOSE index_cursor;
DEALLOCATE index_cursor;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename, @objectid;
END;
PRINT 'The indexes on all tables have been disabled.';
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO
And then this to rebuild the indexes after the load:
use ASL_DEV_GLD;
GO
DECLARE tables_cursor CURSOR
FOR
SELECT s.name, t.name, t.object_id
FROM sys.objects AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.type = 'U';
OPEN tables_cursor;
DECLARE @schemaname sysname;
DECLARE @tablename sysname;
DECLARE @objectid sysname;
DECLARE @indexname sysname;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename, @objectid;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
DECLARE index_cursor CURSOR
FOR
SELECT name AS iname
FROM sys.indexes
WHERE object_id = @objectid
AND UPPER(type_desc) <> 'CLUSTERED';
OPEN index_cursor;
FETCH NEXT FROM index_cursor INTO @indexname;
WHILE (@@FETCH_STATUS <> -1)
BEGIN;
-- EXEC ('ALTER INDEX ALL ON ' + @schemaname + '.' + @tablename + ' REBUILD;');
EXEC ('ALTER INDEX ' + @indexname + ' ON ' + @schemaname + '.' + @tablename + ' REBUILD;');
-- PRINT @schemaname + ' ' + @tablename + ' ' + @objectid + ' ' + @indexname;
FETCH NEXT FROM index_cursor INTO @indexname;
END;
CLOSE index_cursor;
DEALLOCATE index_cursor;
FETCH NEXT FROM tables_cursor INTO @schemaname, @tablename, @objectid;
END;
PRINT 'The indexes on all tables have been REBUILT.';
CLOSE tables_cursor;
DEALLOCATE tables_cursor;
GO
They both seem to work, however, I get one error line for a table that has either no indexes or one clustered index. Any ideas what I've done wrong?
March 26, 2009 at 10:45 pm
You are not excluding HEAP, instead of excluding cluster indexes
Try this for disabling indexes ... just made few adjustments..
DECLARE @schemaname sysname
DECLARE @tablename sysname
DECLARE @objectid sysname
DECLARE @indexname sysname
DECLARE tables_cursor CURSOR FOR
SELECT s.name, t.name, t.object_id
FROM sys.objects AS t
JOIN sys.schemas AS s ON s.schema_id = t.schema_id
WHERE t.type = 'U'
OPEN tables_cursor
FETCH NEXT FROM tables_cursor
INTO @schemaname, @tablename, @objectid
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (SELECT COUNT(*)
FROM sys.indexes
WHERE object_id = @objectid
AND type_desc LIKE 'NONCLUSTERED') > 0
BEGIN
DECLARE index_cursor CURSOR FOR
SELECT name AS iname
FROM sys.indexes
WHERE object_id = @objectid
AND type_desc LIKE 'NONCLUSTERED'
OPEN index_cursor
FETCH NEXT FROM index_cursor
INTO @indexname
WHILE (@@FETCH_STATUS <> -1)
BEGIN
EXEC ('ALTER INDEX [' + @indexname + '] ON [' + @schemaname + '].[' + @tablename + '] DISABLE')
FETCH NEXT FROM index_cursor
INTO @indexname
END
CLOSE index_cursor
DEALLOCATE index_cursor
END
FETCH NEXT FROM tables_cursor
INTO @schemaname, @tablename, @objectid
END
PRINT 'The indexes on all tables have been disabled.'
CLOSE tables_cursor
DEALLOCATE tables_cursor
GO
And what is the error? Only thing I can think is you are trying to disable a heap and it complains. And when you disable a cluster it takes a table off-line; but shouldn't return errors...
PS I think this belongs under the 2005 form.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 27, 2009 at 7:07 am
Thanks Mohit!! Your suggested changes worked perfectly!
I apologize for posting in the wrong area. I honestly did not notice this was SQL 7, 2000 until you mentioned my posting in the wrong place. Was rushed by a deadline as usual.
Thanks again!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply