-- 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
