Перейти к содержимому

Как создать хранимую процедуру для реиндекса таблиц mssql (reindex)

Задача делать реиндекс только определенных таблиц.

Создаём таблицу

USE [ax_molniya]
GO

/****** Object:  Table [dbo].[www_reindex_tablename]    Script Date: 08/17/2018 08:45:57 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[www_reindex_tablename](
	[objectname] [nvarchar](128) NULL,
	[group] [nvarchar](1) NULL,
	[id] [int] IDENTITY(1,1) NOT NULL
) ON [PRIMARY]

GO

Наполняем её таблицами которые нужно реиндексировать

Как создать хранимую процедуру для реиндекса таблиц mssql (reindex)

Делаем хранимую процедуру

USE [ax_molniya]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[www_reindex_ALTER]
-- Add the parameters for the stored procedure here

-- если readonly = 0 (значение по- умолчанию), тогда выполняется вся работа скрипта)
-- если readonly = 1, тогда предоставится информация о состоянии индексов на таблицу и реиндекс не будет выполнен
@readonly bit = 0,

-- защита от случайного запуска
@password nvarchar(max) = '',

-- тихий режим
-- если @displayoff = 0 (значение по- умолчанию), тогда прогрес выполнения скрипта дублируется текстовыми сообщениями")
-- если @displayoff = 1, тогда прогресс выполнения скрипта не дублируется(например если реиндекс инициализирован джобом,то нет необходимости показывать текстовые сообщения)
@displayoff bit = 0,

-- имя базы данных
@dbname nvarchar(64),
-- имя таблицы
@tablename nvarchar(128),

-- уведомление по email
-- если 0 - выключено, 1 - включено
@emailalert bit = 1


AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  
  DECLARE @ErrorMessage NVARCHAR(4000);
  DECLARE @ErrorSeverity INT;
  DECLARE @ErrorState INT;
  -- дата выполнения реиндекс в формате ггггммдд
  DECLARE @wjobday nvarchar(8)
  -- hash password
  DECLARE @adminhash varbinary
  DECLARE @hashpassword varbinary
  -- ключ для выборки данных
  DECLARE @seckey nvarchar(12)
  -- текущее время чч:мм
  DECLARE @b nvarchar(5)
  -- два правых символа от @b
  DECLARE @c nvarchar(2)
  -- два левых символа от @b
  DECLARE @d nvarchar(2)

  -- флаг выполнения задания
  DECLARE @flag bit
  DECLARE @email nvarchar(512)
  DECLARE @at nvarchar(500);
  DECLARE @createindexstart nvarchar(128)
  DECLARE @createindexerror nvarchar(128)
  DECLARE @createindexstop nvarchar(128)
  DECLARE @createindexnone nvarchar(128)
  DECLARE @dropindex nvarchar(128)
  DECLARE @dropindexerror nvarchar(128)
  DECLARE @indexname nvarchar(256)
  DECLARE @createindexcommand nvarchar(max)
  DECLARE @dropindexcommand nvarchar(max)
  DECLARE @errmes nvarchar(512)
  DECLARE @wrongpassalert nvarchar(256)
  -- ALTER переменные
  DECLARE @re_objectid int;
  DECLARE @re_indexid int;
  DECLARE @re_partitioncount bigint;
  DECLARE @re_schemaname nvarchar(130);
  DECLARE @re_objectname nvarchar(130);
  DECLARE @re_indexname nvarchar(130);
  DECLARE @re_partitionnum bigint;
  DECLARE @re_partitions bigint;
  DECLARE @re_frag float;
  DECLARE @re_command nvarchar(4000);
  
  -- получаем соль пароля
  SET @password = CONVERT(nvarchar, @password);
  SET @hashpassword = (SELECT
    HASHBYTES('MD5', @password));
  -- обнуляем флаг
  SET @flag = 0

  -- текущая дата
  SET @wjobday = CONVERT(nvarchar(8), GETDATE(), 112)
  SET @b = (SELECT
    CONVERT(nvarchar(5), GETDATE(), 108))
  SET @c = RIGHT(@b, 2)
  SET @d = LEFT(@b, 2)
  -- получаем значение seckey
  SET @seckey = @wjobday + @d + @c
  

  /*
  
  --------------------#CONFIG---------------------------
  секция с настройками скрипта, необходимо обязательно
  заполнить(проверить) перед запуском
  
  */
  -- имя индекса
  SET @indexname = 'temp_index'
  -- соль пароля на запуск скрипта,НЕ ТРОГАТЬ если не знаешь,что это
  SET @adminhash = 0x385B43E3833EA9639158D535A765196D
  -- имя задания
  --set @jobname = ''
  -- email для уведомления
  SET @email = 'event.messages@ctmol.ru;'
  --SET @email = 'event.messages@ctmol.ru;'
  --SET @email = 'it.sysadmin.dba1@sparural.ru'

  /*
  
   -------------------#END CONFIG------------------------
  
  */

  --------------------------------------------------------------------
  SET @createindexstart = 'Реиндекс на таблице dbo.' + @tablename + ' начат '
  SET @createindexerror = '======ОШИБКА====== еиндекс на таблице на таблице ' + @tablename + ' ======ОШИБКА====== '
  SET @createindexstop = 'Реиндекс на таблице на таблице dbo.' + @tablename + ' завершено '
  SET @createindexnone = 'Все индексы на таблице '+ @tablename + ' в норме,перестраивать не нужно'
  SET @dropindex = 'Удаление временного кластеризованного индекса на таблице dbo.' + @tablename + ' выполнено '
  SET @dropindexerror = '======ОШИБКА====== Удаление временного кластеризованного индекса на таблице dbo.' + @tablename + ' ======ОШИБКА====== '
  SET @createindexcommand = 'CREATE CLUSTERED INDEX [' + @indexname + '] ON [dbo].[' + @tablename + ']([RECID] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);'
  --set @createindexcommand = 'ALTER TABLE [dbo].['+@tablename+'] ADD CONSTRAINT ['+@indexname+'] PRIMARY KEY CLUSTERED ([DATAAREAID] ASC,[PRODID] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]'
  SET @dropindexcommand = 'DROP INDEX [' + @indexname + '] ON [dbo].[' + @tablename + '] WITH (ONLINE = ON);'
  --set @dropindexcommand ='ALTER TABLE [dbo].['+@tablename+'] DROP CONSTRAINT ['+@indexname+']'
  ---------------------------------------------------------------------

  -- проверка входных параметров
  IF ((SELECT
      COUNT(name)
    FROM sys.databases
    WHERE [name] = @dbname)
    = 0)
  BEGIN
IF (@displayoff = 0)
    BEGIN
PRINT 'указанной бд [' + @dbname + '] на сервере не существует, проверь секцию #CONFIG в процедуре [www_reindex_' + @tablename + ']'
PRINT ' '
END

    SELECT
        @ErrorMessage = 'указанной бд [' + @dbname + '] на сервере не существует, проверь секцию #CONFIG в процедуре [www_reindex_' + @tablename + ']',
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

  END
  ELSE
  BEGIN
    IF (@hashpassword <> @adminhash)
    BEGIN
      IF (@displayoff = 0)
      BEGIN
        PRINT '--------------------------------------------------'
        PRINT ''
        PRINT 'ОШИБКА - введен не правильный пароль - ОШИБКА'
        PRINT ''
        PRINT '--------------------------------------------------'
      END
      SET @wrongpassalert = 'ОШИБКА - введен не правильный пароль - ОШИБКА, пользователь ' + SUSER_SNAME()
      EXEC www_reindex_stat_log @objectname = @tablename,
                                @description = @wrongpassalert,
                                @jobday = @wjobday,
                                @seckey = @seckey

SELECT
        @ErrorMessage = @wrongpassalert,
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT
    END
    ELSE
    BEGIN
      IF (@readonly = 1)
      BEGIN
        IF (@displayoff = 0)
        BEGIN
          PRINT 'Дата проведения операции ' + @wjobday
          PRINT '--------------------------------------------------'
          PRINT ''
          PRINT 'Имя базы данных-> ' + @dbname
          PRINT 'Имя таблицы ->' + @tablename
          PRINT ''
          PRINT '--------------------------------------------------'
        END
        -- смотрим статистику по индексам таблицы
        EXEC www_reindex_stat_index_all_log @objectname = @tablename,
                                            @dbname = @dbname,
                                            @readonly = @readonly

      END
      ELSE
      BEGIN
        IF (@displayoff = 0)
        BEGIN
          PRINT 'Дата проведения реиндекса ' + @wjobday
          PRINT '--------------------------------------------------'
          PRINT ''
          PRINT 'Имя базы данных-> ' + @dbname
          PRINT 'Имя таблицы ->' + @tablename
          PRINT ''
          PRINT '--------------------------------------------------'
        END
      /*
      
      шаг 1
      
      */
      BEGIN TRY
        -----------------------------------------------------
        -- смотрим состояние индексов до
        EXEC www_reindex_stat_index_all_log @objectname = @tablename,
                                            @jobday = @wjobday,
                                            @status = 'before',
                                            @dbname = @dbname,
                                            @seckey = @seckey
        IF (@displayoff = 0)
        BEGIN
          PRINT @createindexstart + CAST(GETDATE() AS varchar);
          RAISERROR ('', 0, 1) WITH NOWAIT
        END
        -- пишем в лог, о начале перестройки индексов на таблице
        EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @createindexstart,
                                  @jobday = @wjobday,
                                  @seckey = @seckey

      END TRY
      BEGIN CATCH
        SELECT
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity,
          ERROR_STATE() AS ErrorState,
          ERROR_PROCEDURE() AS ErrorProcedure,
          ERROR_LINE() AS ErrorLine,
          ERROR_MESSAGE() AS ErrorMessage,
          'шаг 1 ошибка вставки данных в таблицы лога';

        SET @errmes = 'шаг 1 ошибка вставки данных в таблицы лога - ' + ERROR_MESSAGE()
        EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @errmes,
                                  @jobday = @wjobday,
                                  @seckey = @seckey

SELECT
        @ErrorMessage = @errmes,
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

      END CATCH
      -----------------------------------------------------

      /*
      
      шаг 1 конец
      
      */

      /*
      
      шаг 2
      
      */

      /*------------перестройка индекса начало--------------*/
      BEGIN TRY
      
      
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(@dbname),OBJECT_ID(@tablename), NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;

if ((select count(*) from #work_to_do) = 0 )
BEGIN
IF (@displayoff = 0)
BEGIN
print @createindexnone + CAST(GETDATE() AS varchar);
RAISERROR ('', 0, 1) WITH NOWAIT
END

-- пишем в лог
EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @createindexnone,
                                  @jobday = @wjobday,
                                  @seckey = @seckey

END
-- Declare the cursor for the list of partitions to be processed.
DECLARE partitions CURSOR FOR SELECT * FROM #work_to_do;
-- Open the cursor.
OPEN partitions;
-- Loop through the partitions.
WHILE (1=1)
BEGIN;
FETCH NEXT
FROM partitions
INTO @re_objectid, @re_indexid, @re_partitionnum, @re_frag;
IF @@FETCH_STATUS < 0 BREAK;
SELECT @re_objectname = QUOTENAME(o.name), @re_schemaname = QUOTENAME(s.name)
FROM sys.objects AS o
JOIN sys.schemas as s ON s.schema_id = o.schema_id
WHERE o.object_id = @re_objectid;
SELECT @re_indexname = QUOTENAME(name)
FROM sys.indexes
WHERE object_id = @re_objectid AND index_id = @re_indexid;
SELECT @re_partitioncount = count (*)
FROM sys.partitions
WHERE object_id = @re_objectid AND index_id = @re_indexid;
-- 30 is an arbitrary decision point at which to switch between reorganizing and rebuilding.
IF @re_frag < 30.0
SET @re_command = N'ALTER INDEX ' + @re_indexname + N' ON ' + @re_schemaname + N'.' + @re_objectname + N' REORGANIZE';
IF @re_frag >= 30.0
SET @re_command = N'ALTER INDEX ' + @re_indexname + N' ON ' + @re_schemaname + N'.' + @re_objectname + N' REBUILD WITH (STATISTICS_NORECOMPUTE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, SORT_IN_TEMPDB = ON, ONLINE = ON)';
IF @re_partitioncount > 1
SET @re_command = @re_command + N' PARTITION=' + CAST(@re_partitionnum AS nvarchar(10));
EXEC (@re_command);
IF (@displayoff = 0)
BEGIN
PRINT N'Executed: ' + @re_command +CAST(GETDATE() AS varchar);
RAISERROR ('', 0, 1) WITH NOWAIT
END

END;
-- Close and deallocate the cursor.
CLOSE partitions;
DEALLOCATE partitions;
-- Drop the temporary table.
DROP TABLE #work_to_do;
      
      
 
    -- EXEC sp_executesql @createindexcommand
        
        
        
        
        -- пишем в лог
        EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @createindexstop,
                                  @jobday = @wjobday,
                                  @seckey = @seckey
        IF (@displayoff = 0)
        BEGIN
          -- выводим на экран
          PRINT @createindexstop + CAST(GETDATE() AS varchar);
          RAISERROR ('', 0, 1) WITH NOWAIT
        END
        --ставим флаг
        SET @flag = 1
      END TRY
      BEGIN CATCH
        SELECT
          ERROR_NUMBER() AS ErrorNumber,
          ERROR_SEVERITY() AS ErrorSeverity,
          ERROR_STATE() AS ErrorState,
          ERROR_PROCEDURE() AS ErrorProcedure,
          ERROR_LINE() AS ErrorLine,
          ERROR_MESSAGE() AS ErrorMessage,
          'ошибка при создании индекса';

        SET @errmes = 'ошибка при создании индекса- ' + ERROR_MESSAGE()
        EXEC www_reindex_stat_log @objectname = @tablename,
                                  @description = @errmes,
                                  @jobday = @wjobday,
                                  @seckey = @seckey
SELECT
        @ErrorMessage = @errmes,
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

        IF (@displayoff = 0)
        BEGIN
          -- выводим на экран, если не получилось
          PRINT @createindexerror + CAST(GETDATE() AS varchar);
        END
        -- ставим флаг
        SET @flag = 0
      END CATCH
        /*------------перестройка индекса конец--------------*/

/*

шаг 4

        */
        -- если предыдущее действие завершилось успешно,то делаем следующий шаг
        IF (@flag = 1)
        BEGIN

        -- пишем инфу о индексах после проведения операции
        BEGIN TRY
          EXEC www_reindex_stat_index_all_log @objectname = @tablename,
                                              @jobday = @wjobday,
                                              @status = 'after',
                                              @dbname = @dbname,
                                              @seckey = @seckey

          SET @flag = 1
        END TRY
        BEGIN CATCH
          SELECT
            ERROR_NUMBER() AS ErrorNumber,
            ERROR_SEVERITY() AS ErrorSeverity,
            ERROR_STATE() AS ErrorState,
            ERROR_PROCEDURE() AS ErrorProcedure,
            ERROR_LINE() AS ErrorLine,
            ERROR_MESSAGE() AS ErrorMessage,
            'шаг 4 ошибка вставки данных в таблицы лога';

          SET @errmes = 'шаг 4 ошибка вставки данных в таблицы лога - ' + ERROR_MESSAGE()
          EXEC www_reindex_stat_log @objectname = @tablename,
                                    @description = @errmes,
                                    @jobday = @wjobday,
                                    @seckey = @seckey


SELECT
        @ErrorMessage = @errmes,
        @ErrorSeverity = 16,
        @ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

          SET @flag = 0
        END CATCH

        END

        IF (@displayoff = 0)
        BEGIN
          IF (@flag = 1)
          BEGIN
            SELECT
              t1.[index_id],
              t1.[index_type_desc],
              t1.[avg_fragmentation_in_percent],
              t2.[avg_fragmentation_in_percent],
              t1.[avg_fragmentation_in_percent] - t2.[avg_fragmentation_in_percent] AS [profit]
            FROM [dbo].[www_reindex_stat_index_all] t1
            FULL OUTER JOIN [www_reindex_stat_index_all] t2
              ON t1.index_id = t2.index_id
            WHERE t1.objectname = @tablename
            AND t1.seckey = @seckey
            AND t1.status = 'before'
            AND t2.status = 'after'
            AND t2.objectname = @tablename
            AND t2.seckey = @seckey

          END
        END

        IF (@flag = 1)
        BEGIN
BEGIN TRY
SET @at = 'задание по реиндексу выполнено ' + @tablename + ' '+CAST(GETDATE() AS varchar);

if (@emailalert = 1)
EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'atrans',
@recipients = @email,
@subject = @tablename,
@body = @at

END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
'ошибка отправки email';


SELECT
@ErrorMessage = 'ошибка отправки email',
@ErrorSeverity = 16,
@ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT
END CATCH

        END
        ELSE
        BEGIN
BEGIN TRY
SET @at = 'Ошибка - задание по реиндексу ' + @tablename + CAST(GETDATE() AS varchar);
if (@emailalert = 1)
EXECUTE msdb.dbo.sp_send_dbmail @profile_name = 'atrans',
@recipients = @email,
@subject = @tablename,
@body = @at
END TRY
BEGIN CATCH
SELECT
ERROR_NUMBER() AS ErrorNumber,
ERROR_SEVERITY() AS ErrorSeverity,
ERROR_STATE() AS ErrorState,
ERROR_PROCEDURE() AS ErrorProcedure,
ERROR_LINE() AS ErrorLine,
ERROR_MESSAGE() AS ErrorMessage,
'ошибка отправки email';

SELECT
@ErrorMessage = 'ошибка отправки email',
@ErrorSeverity = 16,
@ErrorState = 100;

RAISERROR (@ErrorMessage,@ErrorSeverity,@ErrorState) WITH NOWAIT

END CATCH
        END

      /*
      
      шаг 4 конец
      
      */

      -- конец бегина с параметром ридонли
      END;
    -- конец бегина с проверкой пароля
    END;
  -- конец первого бегина
  END;

-- конец процедуры
END

Запуск хранимой процедуры

DECLARE @objectname nvarchar(130);
DECLARE partitions1 CURSOR FOR SELECT objectname FROM www_reindex_tablename where [group] = 'a'
-- Open the cursor.
OPEN partitions1;
-- Loop through the partitions.
WHILE (1=1)
    BEGIN;
        FETCH NEXT
           FROM partitions1
           INTO @objectname
        IF @@FETCH_STATUS < 0 BREAK;
 
        --print @objectname
 
        exec www_reindex_alter
		@dbname = 'ax_molniya',
		@tablename =  @objectname,
		@password='день победы',
		@emailalert = 1,
		@displayoff = 1,
		@readonly = 0      
 
         END;
-- Close and deallocate the cursor.
CLOSE partitions1;
DEALLOCATE partitions1;

Хранимая процедура для записи лога

USE [ax_molniya]
GO

/****** Object:  StoredProcedure [dbo].[www_reindex_stat_index_all_log]    Script Date: 08/17/2018 09:02:52 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[www_reindex_stat_index_all_log]
-- Add the parameters for the stored procedure here
@objectname nvarchar(128)
,@jobday nvarchar(8) = ''
,@status nvarchar(10) = 'before'
,@dbname nvarchar(64)
,@seckey nvarchar(12) = ''
,@readonly bit = 0

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
IF(@readonly = 0)
BEGIN
insert into www_reindex_stat_index_all(
[database_id],
[object_id],
[index_id] ,
[partition_number],
[index_type_desc],
[alloc_unit_type_desc],
[index_depth],
[index_level] ,
[avg_fragmentation_in_percent],
[fragment_count],
[avg_fragment_size_in_pages],
[page_count],
[avg_page_space_used_in_percent] ,
[record_count],
[ghost_record_count] ,
[version_ghost_record_count],
[min_record_size_in_bytes],
[max_record_size_in_bytes],
[avg_record_size_in_bytes] ,
[forwarded_record_count] ,
[jobday],
[objectname],
[status],
[seckey]
)
SELECT [database_id],
[object_id],
[index_id] ,
[partition_number],
[index_type_desc],
[alloc_unit_type_desc],
[index_depth],
[index_level] ,
[avg_fragmentation_in_percent],
[fragment_count],
[avg_fragment_size_in_pages],
[page_count],
[avg_page_space_used_in_percent] ,
[record_count],
[ghost_record_count] ,
[version_ghost_record_count],
[min_record_size_in_bytes],
[max_record_size_in_bytes],
[avg_record_size_in_bytes] ,
[forwarded_record_count] ,
@jobday as [jobday],
@objectname as [objectname],
@status as [status],
@seckey as [seckey]
FROM sys.dm_db_index_physical_stats(DB_ID(@dbname), OBJECT_ID(@objectname), NULL, NULL , 'LIMITED');
END
ELSE
BEGIN
SELECT
[database_id],
@objectname as [objectname],
[object_id],
[index_id] ,
[partition_number],
[index_type_desc],
[alloc_unit_type_desc],
[index_depth],
[index_level] ,
[avg_fragmentation_in_percent],
[fragment_count],
[avg_fragment_size_in_pages],
[page_count],
[avg_page_space_used_in_percent] ,
[record_count],
[ghost_record_count] ,
[version_ghost_record_count],
[min_record_size_in_bytes],
[max_record_size_in_bytes],
[avg_record_size_in_bytes] ,
[forwarded_record_count]
FROM sys.dm_db_index_physical_stats(DB_ID(@dbname), OBJECT_ID(@objectname), NULL, NULL , 'LIMITED');
END


    -- Insert statements for procedure here

 
END

GO

Таблица для записи лога

USE [ax_molniya]
GO

/****** Object:  Table [dbo].[www_reindex_stat_index_all]    Script Date: 08/17/2018 09:04:33 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[www_reindex_stat_index_all](
	[database_id] [smallint] NULL,
	[object_id] [int] NULL,
	[index_id] [int] NULL,
	[partition_number] [int] NULL,
	[index_type_desc] [nvarchar](60) NULL,
	[alloc_unit_type_desc] [nvarchar](60) NULL,
	[index_depth] [tinyint] NULL,
	[index_level] [tinyint] NULL,
	[avg_fragmentation_in_percent] [float] NULL,
	[fragment_count] [bigint] NULL,
	[avg_fragment_size_in_pages] [float] NULL,
	[page_count] [bigint] NULL,
	[avg_page_space_used_in_percent] [float] NULL,
	[record_count] [bigint] NULL,
	[ghost_record_count] [bigint] NULL,
	[version_ghost_record_count] [bigint] NULL,
	[min_record_size_in_bytes] [int] NULL,
	[max_record_size_in_bytes] [int] NULL,
	[avg_record_size_in_bytes] [float] NULL,
	[forwarded_record_count] [bigint] NULL,
	[createdate] [datetime] NOT NULL,
	[createdate_unix] [int] NOT NULL,
	[jobday] [nvarchar](8) NOT NULL,
	[objectname] [nvarchar](128) NULL,
	[status] [nvarchar](10) NULL,
	[seckey] [nvarchar](12) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[www_reindex_stat_index_all] ADD  DEFAULT (getdate()) FOR [createdate]
GO

ALTER TABLE [dbo].[www_reindex_stat_index_all] ADD  DEFAULT (datediff(second,'1970-01-01 00:00:00',getutcdate())) FOR [createdate_unix]
GO

 

 

Similar Posts:

Метки:

Добавить комментарий

Ваш адрес email не будет опубликован. Обязательные поля помечены *