January 9, 2017 at 7:42 am
I was looking the way to rebuild all indexes in all tables and I found an script that does it, but i would like to get 6 specific tables for not getting index rebuild.
How can I accomplished that with the below script. In Oracle I can add a where condition like : where table not in ('table1','table2').
-------------------------------------------------------------
DECLARE @TableName VARCHAR(255)
DECLARE @sql NVARCHAR(500)
DECLARE @fillfactor INT
SET @fillfactor = 80
DECLARE TableCursor CURSOR FOR
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables
OPEN TableCursor
FETCH NEXT FROM TableCursor INTO @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql = 'ALTER INDEX ALL ON ' + @TableName + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'
EXEC (@sql)
FETCH NEXT FROM TableCursor INTO @TableName
END
CLOSE TableCursor
DEALLOCATE TableCursor
GO
Thanks
Paul
January 9, 2017 at 7:44 am
FROM sys.tables WHERE name NOT IN (<list of tables to be excluded>);
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 9, 2017 at 7:47 am
Why not exclude them in your WHERE clause?
SELECT OBJECT_SCHEMA_NAME([object_id])+'.'+name AS TableName
FROM sys.tables t
WHERE t.name NOT IN('Table1','Table2','Table3','Table4','Table5','table6') --You could get these from a table if they could change
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply