Filtering Table Name.

• paul.s.vidal

  SSC Eights!

  Points: 835

  More actions

  January 9, 2017 at 7:42 am

  #316810

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 9, 2017 at 7:44 am

  #1921372

  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

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Thom A

  SSC Guru

  Points: 99143

  More actions

  January 9, 2017 at 7:47 am

  #1921376

  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