Dynamic SQL execution - EXEC @SQL vs. EXEC (@SQL)

-- EXEC @SQL vs. EXEC (@SQL)

--Without the brackets, the assumption is that the content of the variable is a stored procedure name

--the original codes===

--find all of the indexes in the current database whose fragmentation is over 50%
SELECT OBJECT_NAME (object_id),*
FROM sys.dm_db_index_physical_stats(
 DB_ID()
 , DEFAULT--object ID I want to view
 , DEFAULT -- Index ID
 , DEFAULT
 , DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
 --AND x.index_id =1
--ORDER BY  x.avg_fragmentation_in_percent DESC
GO

SET NOCOUNT ON
GO

/*Some columns can be rebuild online, others cannot such as varchar(max) etc.
Tables with these BLOB or CLOB columns must be rebuilt offline.
Offline rebuld is more thorough, effcient, but not always possible
Do the online build possible to keep the business on, Warning: temp db intensive
if not possible, have to be offline
Offline rebuilt just affects the table, not other tables in the database.
*/
--build a temp table
DECLARE @Tables TABLE (
 ID INT IDENTITY(1,1)
 , SchemaName SYSNAME
 , TableName SYSNAME
 , CanBuildOnline BIT
)

-- All tables that can be rebuilt online:
INSERT @Tables (SchemaName, TableName, CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 1
FROM sys.tables t
WHERE NOT EXISTS (
 SELECT * FROM sys.columns c
 WHERE c.object_id = t.object_id
  AND ( -- SELECT * FROM sys.types
    c.system_type_id IN (34, 35, 99, 241)
   --34=iamge, 35=text, 99=ntext,241=xml
   OR (c.system_type_id IN (165, 167, 231) AND c.max_length = -1)
   --165=varbinary, 167=varchar, 231=sysname, -1=max
  )
 )
 AND--if we want to build tables with 50% and higher fragmentation
t.object_id IN (
 SELECT x.object_id
 FROM sys.dm_db_index_physical_stats(
 DB_ID()
 , DEFAULT
 , DEFAULT
 , DEFAULT
 , DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50)

-- All tables that must be rebuild offline:
INSERT @Tables (SchemaName, TableName, CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 0
FROM sys.tables t
WHERE EXISTS (
 SELECT * FROM sys.columns c
 WHERE c.object_id = t.object_id
  AND ( -- SELECT * FROM sys.types
   c.system_type_id IN (34, 35, 99, 241)
   OR (c.system_type_id IN (165, 167, 231) AND c.max_length = -1)
  )
)

DECLARE @Id INT
 , @Sql NVARCHAR(1000)
 , @Schema SYSNAME
 , @Table SYSNAME
 , @CanBuildOnline BIT

SELECT @Id = MIN(ID) FROM @Tables

WHILE @Id IS NOT NULL
 BEGIN
  SELECT @Schema = SchemaName
   , @Table = TableName
   , @CanBuildOnline = CanBuildOnline
  FROM @Tables 
  WHERE ID=@Id
 
  SET @Sql = 'ALTER INDEX ALL ON [' + @Schema + '].[' + @Table + ']
   REBUILD WITH(ONLINE = '
   + CASE @CanBuildOnline WHEN 1 THEN 'ON' ELSE 'OFF' END
   + ' )'
  PRINT @Sql
  EXEC @Sql

  DELETE @Tables WHERE ID=@Id
  SET @Id = NULL
  SELECT @Id = MIN(ID) FROM @Tables
 END


--===the new codes====

--find all of the indexes in the current database whose fragmentation is over 50%
SELECT OBJECT_NAME (object_id),*
FROM sys.dm_db_index_physical_stats(
 DB_ID()
 , DEFAULT--object ID I want to view
 , DEFAULT -- Index ID
 , DEFAULT
 , DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50
 --AND x.index_id =1
--ORDER BY  x.avg_fragmentation_in_percent DESC
GO

SET NOCOUNT ON
GO

/*Some columns can be rebuild online, others cannot such as varchar(max) etc.
Tables with these BLOB or CLOB columns must be rebuilt offline.
Offline rebuld is more thorough, effcient, but not always possible
Do the online build possible to keep the business on, Warning: temp db intensive
if not possible, have to be offline
Offline rebuilt just affects the table, not other tables in the database.
*/
--build a temp table
DECLARE @Tables TABLE (
 ID INT IDENTITY(1,1)
 , SchemaName SYSNAME
 , TableName SYSNAME
 , CanBuildOnline BIT
)

-- All tables that can be rebuilt online:
INSERT @Tables (SchemaName, TableName, CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 1
FROM sys.tables t
WHERE NOT EXISTS (
 SELECT * FROM sys.columns c
 WHERE c.object_id = t.object_id
  AND ( -- SELECT * FROM sys.types
    c.system_type_id IN (34, 35, 99, 241)
   --34=iamge, 35=text, 99=ntext,241=xml
   OR (c.system_type_id IN (165, 167, 231) AND c.max_length = -1)
   --165=varbinary, 167=varchar, 231=sysname, -1=max
  )
 )
 AND--if we want to build tables with 50% and higher fragmentation
t.object_id IN (
 SELECT x.object_id
 FROM sys.dm_db_index_physical_stats(
 DB_ID()
 , DEFAULT
 , DEFAULT
 , DEFAULT
 , DEFAULT
) x
WHERE x.avg_fragmentation_in_percent > 50)

-- All tables that must be rebuild offline:
INSERT @Tables (SchemaName, TableName, CanBuildOnline)
SELECT SCHEMA_NAME(t.schema_id), t.name, 0
FROM sys.tables t
WHERE EXISTS (
 SELECT * FROM sys.columns c
 WHERE c.object_id = t.object_id
  AND ( -- SELECT * FROM sys.types
   c.system_type_id IN (34, 35, 99, 241)
   OR (c.system_type_id IN (165, 167, 231) AND c.max_length = -1)
  )
)

DECLARE @Id INT
 , @Sql NVARCHAR(1000)
 , @Schema SYSNAME
 , @Table SYSNAME
 , @CanBuildOnline BIT

SELECT @Id = MIN(ID) FROM @Tables

WHILE @Id IS NOT NULL
 BEGIN
  SELECT @Schema = SchemaName
   , @Table = TableName
   , @CanBuildOnline = CanBuildOnline
  FROM @Tables 
  WHERE ID=@Id
 
  SET @Sql = 'ALTER INDEX ALL ON [' + @Schema + '].[' + @Table + ']
   REBUILD WITH(ONLINE = '
   + CASE @CanBuildOnline WHEN 1 THEN 'ON' ELSE 'OFF' END
   + ' )'
  PRINT @Sql
  EXEC (@Sql)

/*
  DELETE @Tables WHERE ID=@Id
  SET @Id = NULL
  SELECT @Id = MIN(ID) FROM @Tables
*/
 END