Задача пересоздать индексы для новой файловой группы что бы распределить данные.
Если версия mssql до 2016
Создайте пользовательскую функцию (если нет)
IF OBJECT_ID('dbo.SplitString', 'TF') IS NOT NULL
DROP FUNCTION dbo.SplitString;
GO
CREATE FUNCTION dbo.SplitString
(
@Input NVARCHAR(MAX),
@Delimiter NCHAR(1)
)
RETURNS @Output TABLE (
Value NVARCHAR(4000)
)
AS
BEGIN
IF @Input IS NULL RETURN;
DECLARE @Start INT = 1, @End INT;
WHILE @Start <= LEN(@Input)
BEGIN
SET @End = CHARINDEX(@Delimiter, @Input, @Start);
IF @End = 0
SET @End = LEN(@Input) + 1;
INSERT INTO @Output (Value)
VALUES (LTRIM(RTRIM(SUBSTRING(@Input, @Start, @End - @Start))));
SET @Start = @End + 1;
END
RETURN;
END;
После скирпт для формирования запроса для пересоздание индексов
SET NOCOUNT ON;
-- Параметры скрипта
DECLARE @model_FileGroupName NVARCHAR(300) = NULL -- NULL = использовать файловую группу по умолчанию
,@ChangeFileGroup BIT = 1
,@RecreateUnconditionally BIT = 1 -- 0 = пересоздавать только при несоответствии FG или FillFactor, 1 = пересоздавать все
,@RecreateNonClusteredOnly BIT = 0 -- 0 = пересоздавать все индексы, 1 = только некластеризованные
,@IncludeDatabases NVARCHAR(MAX) = 'dev' -- Список баз данных через запятую, NULL = все
,@ExcludeDatabases NVARCHAR(MAX) = NULL -- Список исключаемых баз данных через запятую
,@IncludeTables NVARCHAR(MAX) = NULL -- Список таблиц через запятую, NULL = все
,@ExcludeTables NVARCHAR(MAX) = NULL -- Список исключаемых таблиц через запятую
-- Если файловая группа не указана, определяем её по умолчанию
IF @model_FileGroupName IS NULL
BEGIN
SELECT @model_FileGroupName = name
FROM sys.filegroups
WHERE is_default = 1;
END
-- Создание временных таблиц
IF (OBJECT_ID('tempdb..#TableColumn') IS NOT NULL) BEGIN DROP TABLE #TableColumn; END;
CREATE TABLE #TableColumn (
DatabaseName VARCHAR(200) NOT NULL
,TableName VARCHAR(200) NOT NULL
,SchemaName VARCHAR(200) NOT NULL
,ColumnName VARCHAR(200) NOT NULL
,ColumnType VARCHAR(200) NOT NULL
,ColumnLenght SMALLINT NULL
,ColumnCollation VARCHAR(200) NULL
,ColumnIsNullable BIT NOT NULL
,ColumnIsIdentity BIT NOT NULL
);
IF (OBJECT_ID('tempdb..#TableIndex') IS NOT NULL) BEGIN DROP TABLE #TableIndex; END;
CREATE TABLE #TableIndex (
RowNum INT NOT NULL IDENTITY(1, 1)
,DatabaseName VARCHAR(500) NOT NULL
,TableName VARCHAR(500) NOT NULL
,SchemaName VARCHAR(500) NOT NULL
,IndexName VARCHAR(500) NOT NULL
--может быть NULL в случае alter index disable
,IndexType VARCHAR(100) NULL
,ConstraintType VARCHAR(500) NULL
--может быть NULL в случае Foreign Key
,FileGroupName VARCHAR(500) /*not*/ NULL
,[FillFactor] TINYINT NULL
,QueryIndex VARCHAR(MAX) NOT NULL
,Command VARCHAR(50) NOT NULL
,Rnk INT NULL
);
IF (OBJECT_ID('tempdb..#ForeignKey') IS NOT NULL) BEGIN DROP TABLE #ForeignKey; END;
CREATE TABLE #ForeignKey (
DatabaseName VARCHAR(500) NOT NULL
,ConstraintName VARCHAR(500) NOT NULL
,ReferencingTableName VARCHAR(500) NOT NULL
,ReferencingSchemaName VARCHAR(500) NOT NULL
,ReferencingColumnName VARCHAR(500) NOT NULL
,ReferencedTableName VARCHAR(500) NOT NULL
,ReferencedSchemaName VARCHAR(500) NOT NULL
,ReferencedColumnName VARCHAR(500) NOT NULL
,DeleteReferentialActionDesc VARCHAR(500) NOT NULL
,UpdateReferentialActionDesc VARCHAR(500) NOT NULL
);
-- Создание временной таблицы для списка баз данных
IF (OBJECT_ID('tempdb..#FilteredDatabases') IS NOT NULL) BEGIN DROP TABLE #FilteredDatabases; END;
CREATE TABLE #FilteredDatabases (name NVARCHAR(128));
-- Заполнение таблицы отфильтрованными базами данных
INSERT INTO #FilteredDatabases
SELECT [name]
FROM master.sys.databases
WHERE database_id > 4 AND state = 0
-- Применение фильтров для баз данных
AND (@IncludeDatabases IS NULL OR [name] IN (SELECT value FROM dbo.SplitString (@IncludeDatabases, ',')))
AND (@ExcludeDatabases IS NULL OR [name] NOT IN (SELECT value FROM dbo.SplitString (@ExcludeDatabases, ',')))
-- Для тестирования
-- AND name = 'AlfaBankITM_C4T_dev'
-- Объявление переменных для цикла
DECLARE @DBName NVARCHAR(200);
DECLARE @query NVARCHAR(MAX); -- Объявляем переменную здесь, чтобы она была доступна во всем скрипте
-- Курсор по отфильтрованным базам данных
DECLARE CursorDatabase CURSOR FOR
SELECT name FROM #FilteredDatabases
OPEN CursorDatabase
FETCH NEXT FROM CursorDatabase INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Проверка фильтров для таблиц
DECLARE @FilterSql NVARCHAR(MAX) = N'
DECLARE @IncludeTables BIT = ' + CASE WHEN @IncludeTables IS NULL THEN '0' ELSE '1' END + ';
DECLARE @ExcludeTables BIT = ' + CASE WHEN @ExcludeTables IS NULL THEN '0' ELSE '1' END + ';
DECLARE @IncludeTableList NVARCHAR(MAX) = ''' + ISNULL(@IncludeTables, '') + ''';
DECLARE @ExcludeTableList NVARCHAR(MAX) = ''' + ISNULL(@ExcludeTables, '') + ''';
';
SET @query = N'USE ' + @DBName + N'; ' + @FilterSql + N'
DECLARE @TableName VARCHAR(500),
@SchemaName VARCHAR(100);
DECLARE @IndexName VARCHAR(256)
DECLARE @ColumnName VARCHAR(100)
DECLARE @is_unique VARCHAR(100)
DECLARE @IndexTypeDesc VARCHAR(100)
DECLARE @FileGroupName VARCHAR(300)
DECLARE @is_disabled VARCHAR(100)
DECLARE @fill_factor TINYINT
DECLARE @IndexOptions VARCHAR(MAX)
DECLARE @IndexFilter VARCHAR(MAX)
DECLARE @IndexColumnId INT
DECLARE @IsDescendingKey INT
DECLARE @IsIncludedColumn INT
DECLARE @TSQLScripCreationIndex VARCHAR(MAX)
DECLARE @TSQLScripDisableIndex VARCHAR(MAX)
DECLARE @TSQLDropIndex VARCHAR(MAX)
/**************** T-SQL Script to Get Foreign Key ****************/
INSERT INTO #ForeignKey
(DatabaseName,ConstraintName,ReferencingTableName,ReferencingSchemaName,ReferencingColumnName,ReferencedTableName,ReferencedSchemaName,ReferencedColumnName,DeleteReferentialActionDesc,UpdateReferentialActionDesc)
SELECT ''' + @DBName + N''' AS DatabaseName
,f.name AS ConstraintName
,OBJECT_NAME(f.parent_object_id) AS ReferencingTableName
,schema_name(tp.schema_id) AS ReferencingSchemaName
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferencingColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName
,schema_name(tr.schema_id) AS ReferencedSchemaName
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumnName
,delete_referential_action_desc AS DeleteReferentialActionDesc
,update_referential_action_desc AS UpdateReferentialActionDesc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
INNER JOIN sys.tables tp
ON tp.object_id= f.parent_object_id
INNER JOIN sys.tables tr
ON tr.object_id= f.referenced_object_id
-- Применение фильтров для таблиц
WHERE (@IncludeTables = 0 OR OBJECT_NAME(f.parent_object_id) IN (SELECT value FROM dbo.SplitString(@IncludeTableList, '',''))
OR OBJECT_NAME(f.referenced_object_id) IN (SELECT value FROM dbo.SplitString(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR (OBJECT_NAME(f.parent_object_id) NOT IN (SELECT value FROM dbo.SplitString(@ExcludeTableList, '',''))
AND OBJECT_NAME(f.referenced_object_id) NOT IN (SELECT value FROM dbo.SplitString(@ExcludeTableList, '',''))))
ORDER BY f.name
-----------------------------------------------------------------------------------------------------
/**************** T-SQL Script to Drop All SQL Server Indexes ****************/
DECLARE CursorDropIndexes CURSOR FOR
SELECT schema_name(t.schema_id)
,t.name
,i.name
,i.type_desc
,i.fill_factor
,FILEGROUP_NAME(i.data_space_id) FileGroupName
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 AND t.is_ms_shipped=0 AND t.name<>''sysdiagrams''
AND (is_primary_key=0 AND is_unique_constraint=0)
-- Применение фильтров для таблиц
AND (@IncludeTables = 0 OR t.name IN (SELECT value FROM dbo.SplitString(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM dbo.SplitString(@ExcludeTableList, '','')))
-- Фильтр для некластеризованных индексов
AND (' + CAST(@RecreateNonClusteredOnly AS NVARCHAR(1)) + ' = 0 OR i.type_desc <> ''CLUSTERED'')
OPEN CursorDropIndexes
FETCH NEXT FROM CursorDropIndexes INTO @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @fill_factor, @FileGroupName
WHILE @@fetch_status = 0
BEGIN
SET @TSQLDropIndex = ''DROP INDEX '' + QUOTENAME(@SchemaName) + ''.'' + QUOTENAME(@TableName) + ''.'' +QUOTENAME(@IndexName) + '';''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, IndexType, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(''' + @DBName + N''', @TableName, @SchemaName, @IndexName, @IndexTypeDesc, @FileGroupName, @fill_factor, @TSQLDropIndex, ''DROP'')
FETCH NEXT FROM CursorDropIndexes INTO @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @fill_factor, @FileGroupName
END
CLOSE CursorDropIndexes
DEALLOCATE CursorDropIndexes
/**************** T-SQL Script to Create All SQL Server Indexes ****************/
DECLARE CursorIndex CURSOR FOR
SELECT schema_name(t.schema_id) [schema_name]
,t.name
,ix.name
,CASE WHEN ix.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END
,ix.type_desc
,CASE WHEN ix.is_padded=1 THEN ''PAD_INDEX = ON, '' ELSE ''PAD_INDEX = OFF, '' END
+ CASE WHEN ix.allow_page_locks=1 THEN ''ALLOW_PAGE_LOCKS = ON, '' ELSE ''ALLOW_PAGE_LOCKS = OFF, '' END
+ CASE WHEN ix.allow_row_locks=1 THEN ''ALLOW_ROW_LOCKS = ON, '' ELSE ''ALLOW_ROW_LOCKS = OFF, '' END
+ CASE WHEN INDEXPROPERTY(t.object_id, ix.name, ''IsStatistics'') = 1 THEN ''STATISTICS_NORECOMPUTE = ON, '' ELSE ''STATISTICS_NORECOMPUTE = OFF, '' END
+ CASE WHEN ix.ignore_dup_key=1 THEN ''IGNORE_DUP_KEY = ON, '' ELSE ''IGNORE_DUP_KEY = OFF, '' END
+ ''SORT_IN_TEMPDB = OFF'' + CASE WHEN ix.fill_factor > 0 AND ix.fill_factor <> 80 THEN '', FILLFACTOR ='' + CAST(ix.fill_factor AS VARCHAR(3)) ELSE '''' END AS IndexOptions
,CASE WHEN ix.has_filter = 1 THEN ''WHERE '' + ix.filter_definition ELSE '''' END AS IndexFilter
,ix.is_disabled
,ix.fill_factor
,FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
INNER JOIN sys.indexes ix ON t.object_id=ix.object_id
WHERE ix.type>0 AND ix.is_primary_key=0 AND ix.is_unique_constraint=0
AND t.is_ms_shipped=0 AND t.name<>''sysdiagrams''
-- Применение фильтров для таблиц
AND (@IncludeTables = 0 OR t.name IN (SELECT value FROM dbo.SplitString(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM dbo.SplitString(@ExcludeTableList, '','')))
-- Фильтр для некластеризованных индексов
AND (' + CAST(@RecreateNonClusteredOnly AS NVARCHAR(1)) + ' = 0 OR ix.type_desc <> ''CLUSTERED'')
ORDER BY schema_name(t.schema_id), t.name, ix.name
OPEN CursorIndex
FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @IndexFilter, @is_disabled, @fill_factor, @FileGroupName
WHILE (@@fetch_status=0)
BEGIN
DECLARE @IndexColumns VARCHAR(MAX)
DECLARE @IncludedColumns VARCHAR(MAX)
SET @IndexColumns=''''
SET @IncludedColumns=''''
DECLARE CursorIndexColumn CURSOR FOR
SELECT col.name, ixc.is_descending_key, ixc.is_included_column
FROM sys.tables tb
INNER JOIN sys.indexes ix ON tb.object_id=ix.object_id
INNER JOIN sys.index_columns ixc ON ix.object_id=ixc.object_id AND ix.index_id= ixc.index_id
INNER JOIN sys.columns col ON ixc.object_id =col.object_id AND ixc.column_id=col.column_id
WHERE ix.type>0 AND (ix.is_primary_key=0 OR ix.is_unique_constraint=0)
AND schema_name(tb.schema_id)=@SchemaName AND tb.name=@TableName AND ix.name=@IndexName
ORDER BY ixc.index_column_id
OPEN CursorIndexColumn
FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn
WHILE (@@fetch_status=0)
BEGIN
IF @IsIncludedColumn=0
SET @IndexColumns=@IndexColumns + @ColumnName + CASE WHEN @IsDescendingKey=1 THEN '' DESC, '' ELSE '' ASC, '' END
ELSE
SET @IncludedColumns=@IncludedColumns + @ColumnName +'', ''
FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn
END
CLOSE CursorIndexColumn
DEALLOCATE CursorIndexColumn
SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns)-1)
SET @IncludedColumns = CASE WHEN LEN(@IncludedColumns) >0 THEN SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns)-1) ELSE '''' END
SET @TSQLScripCreationIndex =''CREATE ''+ @is_unique + @IndexTypeDesc + '' INDEX '' + QUOTENAME(@IndexName) + '' ON '' + QUOTENAME(@SchemaName) + ''.''+ QUOTENAME(@TableName)+ ''(''+@IndexColumns+'') '' +
CASE WHEN LEN(@IncludedColumns)>0 THEN CHAR(13) +''INCLUDE ('' + @IncludedColumns+ '')'' ELSE '''' END + @IndexFilter + CHAR(13)+''WITH ('' + @IndexOptions+ '') ON '' + QUOTENAME(''' + @model_FileGroupName + ''') + '';''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, IndexType, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(DB_NAME(), @TableName, @SchemaName, @IndexName, @IndexTypeDesc, @FileGroupName, @fill_factor, @TSQLScripCreationIndex, ''CREATE'')
IF (@is_disabled=1)
BEGIN
SET @TSQLScripDisableIndex= CHAR(13) +''ALTER INDEX '' +QUOTENAME(@IndexName) + '' ON '' + QUOTENAME(@SchemaName) +''.''+ QUOTENAME(@TableName) + '' DISABLE;'' + CHAR(13)
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(''' + @DBName + N''', @TableName, @SchemaName, @IndexName, @FileGroupName, @fill_factor, @TSQLScripDisableIndex, ''ALTER'')
END
FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @IndexFilter, @is_disabled, @fill_factor, @FileGroupName
END
CLOSE CursorIndex
DEALLOCATE CursorIndex
-----------------------------------------------------------------------------------------------------
/********** SCRIPT TO GENERATE THE DROP SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS. **********/
DECLARE @SchemaName_PK VARCHAR(256)
DECLARE @TableName_PK VARCHAR(256)
DECLARE @IndexName_PK VARCHAR(256)
DECLARE @fill_factor_PK TINYINT
DECLARE @FileGroupName_PK VARCHAR(300)
DECLARE @TSQLDropIndex_PK VARCHAR(MAX)
DECLARE @ColumnName_PK VARCHAR(100)
DECLARE @is_unique_constraint_PK VARCHAR(100)
DECLARE @IndexTypeDesc_PK VARCHAR(100)
DECLARE @is_disabled_PK VARCHAR(100)
DECLARE @IndexOptions_PK VARCHAR(MAX)
DECLARE @IndexColumnId_PK INT
DECLARE @IsDescendingKey_PK INT
DECLARE @IsIncludedColumn_PK INT
DECLARE @TSQLScripCreationIndex_PK VARCHAR(MAX)
DECLARE @TSQLScripDisableIndex_PK VARCHAR(MAX)
DECLARE @is_primary_key_PK VARCHAR(100)
DECLARE CursorDropIndexes_PK CURSOR FOR
SELECT schema_name(t.schema_id)
,t.name
,i.name
,CASE WHEN i.is_unique_constraint = 1 THEN '' UNIQUE '' ELSE '''' END
,CASE WHEN i.is_primary_key = 1 THEN '' PRIMARY KEY '' ELSE '''' END
,i.type_desc
,i.fill_factor
,FILEGROUP_NAME(i.data_space_id) FileGroupName
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 AND t.is_ms_shipped=0 AND t.name<>''sysdiagrams''
AND (is_primary_key=1 OR is_unique_constraint=1)
-- Применение фильтров для таблиц
AND (@IncludeTables = 0 OR t.name IN (SELECT value FROM dbo.SplitString(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM dbo.SplitString(@ExcludeTableList, '','')))
-- Фильтр для некластеризованных индексов
AND (' + CAST(@RecreateNonClusteredOnly AS NVARCHAR(1)) + ' = 0 OR i.type_desc <> ''CLUSTERED'')
OPEN CursorDropIndexes_PK
FETCH NEXT FROM CursorDropIndexes_PK INTO @SchemaName_PK, @TableName_PK, @IndexName_PK, @is_unique_constraint_PK, @is_primary_key_PK, @IndexTypeDesc_PK, @fill_factor_PK, @FileGroupName_PK
WHILE @@fetch_status = 0
BEGIN
SET @TSQLDropIndex_PK = ''ALTER TABLE '' + QUOTENAME(@SchemaName_PK) + ''.'' + QUOTENAME(@TableName_PK) + '' DROP CONSTRAINT '' +QUOTENAME(@IndexName_PK) + '';''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, IndexType, ConstraintType, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(''' + @DBName + N''', @TableName_PK, @SchemaName_PK, @IndexName_PK, @IndexTypeDesc_PK, ISNULL(CASE WHEN @is_primary_key_PK = '''' THEN NULL ELSE @is_primary_key_PK END, CASE WHEN @is_unique_constraint_PK = '''' THEN NULL ELSE @is_unique_constraint_PK END), @FileGroupName_PK, @fill_factor_PK, @TSQLDropIndex_PK, ''DROP'')
FETCH NEXT FROM CursorDropIndexes_PK INTO @SchemaName_PK, @TableName_PK, @IndexName_PK, @is_unique_constraint_PK, @is_primary_key_PK, @IndexTypeDesc_PK, @fill_factor_PK, @FileGroupName_PK
END
CLOSE CursorDropIndexes_PK
DEALLOCATE CursorDropIndexes_PK
/********** SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS. **********/
DECLARE CursorCreateIndex_PK CURSOR FOR
SELECT schema_name(t.schema_id) [schema_name], t.name, ix.name
,CASE WHEN ix.is_unique_constraint = 1 THEN '' UNIQUE '' ELSE '''' END
,CASE WHEN ix.is_primary_key = 1 THEN '' PRIMARY KEY '' ELSE '''' END
,ix.type_desc
,CASE WHEN ix.is_padded=1 THEN ''PAD_INDEX = ON, '' ELSE ''PAD_INDEX = OFF, '' END
+ CASE WHEN ix.allow_page_locks=1 THEN ''ALLOW_PAGE_LOCKS = ON, '' ELSE ''ALLOW_PAGE_LOCKS = OFF, '' END
+ CASE WHEN ix.allow_row_locks=1 THEN ''ALLOW_ROW_LOCKS = ON, '' ELSE ''ALLOW_ROW_LOCKS = OFF, '' END
+ CASE WHEN INDEXPROPERTY(t.object_id, ix.name, ''IsStatistics'') = 1 THEN ''STATISTICS_NORECOMPUTE = ON, '' ELSE ''STATISTICS_NORECOMPUTE = OFF, '' END
+ CASE WHEN ix.ignore_dup_key=1 THEN ''IGNORE_DUP_KEY = ON, '' ELSE ''IGNORE_DUP_KEY = OFF, '' END
+ ''SORT_IN_TEMPDB = OFF'' + CASE WHEN ix.fill_factor > 0 AND ix.fill_factor <> 80 THEN '', FILLFACTOR ='' + CAST(ix.fill_factor AS VARCHAR(3)) ELSE '''' END AS IndexOptions
,ix.fill_factor
,FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
INNER JOIN sys.indexes ix ON t.object_id=ix.object_id
WHERE ix.type>0 AND (ix.is_primary_key=1 OR ix.is_unique_constraint=1)
AND t.is_ms_shipped=0 AND t.name<>''sysdiagrams''
-- Применение фильтров для таблиц
AND (@IncludeTables = 0 OR t.name IN (SELECT value FROM dbo.SplitString(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM dbo.SplitString(@ExcludeTableList, '','')))
-- Фильтр для некластеризованных индексов
AND (' + CAST(@RecreateNonClusteredOnly AS NVARCHAR(1)) + ' = 0 OR ix.type_desc <> ''CLUSTERED'')
ORDER BY schema_name(t.schema_id), t.name, ix.name
OPEN CursorCreateIndex_PK
FETCH NEXT FROM CursorCreateIndex_PK INTO @SchemaName_PK, @TableName_PK, @IndexName_PK, @is_unique_constraint_PK, @is_primary_key_PK, @IndexTypeDesc_PK, @IndexOptions_PK, @fill_factor_PK, @FileGroupName_PK
WHILE (@@fetch_status=0)
BEGIN
DECLARE @IndexColumns_PK VARCHAR(MAX)
DECLARE @IncludedColumns_PK VARCHAR(MAX)
SET @IndexColumns_PK=''''
SET @IncludedColumns_PK=''''
DECLARE CursorCreateIndex_PKColumn CURSOR FOR
SELECT col.name, ixc.is_descending_key, ixc.is_included_column
FROM sys.tables tb
INNER JOIN sys.indexes ix ON tb.object_id=ix.object_id
INNER JOIN sys.index_columns ixc ON ix.object_id=ixc.object_id AND ix.index_id= ixc.index_id
INNER JOIN sys.columns col ON ixc.object_id =col.object_id AND ixc.column_id=col.column_id
WHERE ix.type>0 AND (ix.is_primary_key=1 OR ix.is_unique_constraint=1)
AND schema_name(tb.schema_id)=@SchemaName_PK AND tb.name=@TableName_PK AND ix.name=@IndexName_PK
ORDER BY ixc.index_column_id
OPEN CursorCreateIndex_PKColumn
FETCH NEXT FROM CursorCreateIndex_PKColumn INTO @ColumnName_PK, @IsDescendingKey_PK, @IsIncludedColumn_PK
WHILE (@@fetch_status=0)
BEGIN
IF @IsIncludedColumn_PK=0
SET @IndexColumns_PK=@IndexColumns_PK + @ColumnName_PK + CASE WHEN @IsDescendingKey_PK=1 THEN '' DESC, '' ELSE '' ASC, '' END
ELSE
SET @IncludedColumns_PK=@IncludedColumns_PK + @ColumnName_PK +'', ''
FETCH NEXT FROM CursorCreateIndex_PKColumn INTO @ColumnName_PK, @IsDescendingKey_PK, @IsIncludedColumn_PK
END
CLOSE CursorCreateIndex_PKColumn
DEALLOCATE CursorCreateIndex_PKColumn
SET @IndexColumns_PK = SUBSTRING(@IndexColumns_PK, 1, LEN(@IndexColumns_PK)-1)
SET @IncludedColumns_PK = CASE WHEN LEN(@IncludedColumns_PK) >0 THEN SUBSTRING(@IncludedColumns_PK, 1, LEN(@IncludedColumns_PK)-1) ELSE '''' END
SET @TSQLScripCreationIndex_PK =''ALTER TABLE '' + QUOTENAME(@SchemaName_PK) +''.''+ QUOTENAME(@TableName_PK) + '' ADD CONSTRAINT '' + QUOTENAME(@IndexName_PK) + @is_unique_constraint_PK + @is_primary_key_PK + +@IndexTypeDesc_PK + ''(''+@IndexColumns_PK+'') ''+
CASE WHEN LEN(@IncludedColumns_PK)>0 THEN CHAR(13) +''INCLUDE ('' + @IncludedColumns_PK+ '')'' ELSE '''' END + CHAR(13)+''WITH ('' + @IndexOptions_PK + '') ON '' + QUOTENAME(CASE WHEN ' + CAST(@ChangeFileGroup AS NVARCHAR(1)) + N' = 1 THEN ''' + @model_FileGroupName +''' ELSE @FileGroupName_PK END) + '';''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, IndexType, ConstraintType, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(''' + @DBName + N''', @TableName_PK, @SchemaName_PK, @IndexName_PK, @IndexTypeDesc_PK, ISNULL(CASE WHEN @is_primary_key_PK = '''' THEN NULL ELSE @is_primary_key_PK END, CASE WHEN @is_unique_constraint_PK = '''' THEN NULL ELSE @is_unique_constraint_PK END), @FileGroupName_PK, @fill_factor_PK, @TSQLScripCreationIndex_PK, ''CREATE'')
FETCH NEXT FROM CursorCreateIndex_PK INTO @SchemaName_PK, @TableName_PK, @IndexName_PK, @is_unique_constraint_PK, @is_primary_key_PK, @IndexTypeDesc_PK, @IndexOptions_PK, @fill_factor_PK, @FileGroupName_PK
END
CLOSE CursorCreateIndex_PK
DEALLOCATE CursorCreateIndex_PK
-----------------------------------------------------------------------------------------------------
/********** SCRIPT TO GENERATE THE DROP SCRIPT OF ALL FOREIGN KEY CONSTRAINTS. **********/
DECLARE @ForeignKeyName_FK VARCHAR(4000)
DECLARE @ParentTableName_FK VARCHAR(4000)
DECLARE @ParentTableSchema_FK VARCHAR(4000)
DECLARE @ForeignKeyID_FK INT
DECLARE @ParentColumn_FK VARCHAR(4000)
DECLARE @ReferencedTable_FK VARCHAR(4000)
DECLARE @ReferencedColumn_FK VARCHAR(4000)
DECLARE @StrParentColumn_FK VARCHAR(MAX)
DECLARE @StrReferencedColumn_FK VARCHAR(MAX)
DECLARE @ReferencedTableSchema_FK VARCHAR(4000)
DECLARE @TSQLScripCreationForeignKey_FK VARCHAR(MAX)
DECLARE @TSQLScripDropForeignKey_FK VARCHAR(MAX)
DECLARE CursorFK CURSOR FOR
SELECT fk.name ForeignKeyName, schema_name(t.schema_id) ParentTableSchema, t.name ParentTableName
FROM sys.foreign_keys fk
INNER JOIN sys.tables t
ON fk.parent_object_id=t.object_id
-- Применение фильтров для таблиц
WHERE (@IncludeTables = 0 OR t.name IN (SELECT value FROM dbo.SplitString(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM dbo.SplitString(@ExcludeTableList, '','')))
OPEN CursorFK
FETCH NEXT FROM CursorFK INTO @ForeignKeyName_FK, @ParentTableSchema_FK, @ParentTableName_FK
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @TSQLScripDropForeignKey_FK =''ALTER TABLE ''+quotename(@ParentTableSchema_FK)+''.''+quotename(@ParentTableName_FK)+'' DROP CONSTRAINT ''+quotename(@ForeignKeyName_FK)
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, ConstraintType, QueryIndex, Command)
VALUES(''' + @DBName + N''', @ParentTableName_FK, @ParentTableSchema_FK, @ForeignKeyName_FK, ''FOREIGN KEY'', @TSQLScripDropForeignKey_FK, ''DROP'')
FETCH NEXT FROM CursorFK INTO @ForeignKeyName_FK, @ParentTableSchema_FK, @ParentTableName_FK
END
CLOSE CursorFK
DEALLOCATE CursorFK
/********** SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS. **********/
DECLARE CursorFK_Create CURSOR FOR
SELECT fk.object_id
FROM sys.foreign_keys fk
INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
-- Применение фильтров для таблиц
WHERE (@IncludeTables = 0 OR t.name IN (SELECT value FROM dbo.SplitString(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM dbo.SplitString(@ExcludeTableList, '','')))
OPEN CursorFK_Create
FETCH NEXT FROM CursorFK_Create INTO @ForeignKeyID_FK
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @StrParentColumn_FK=''''
SET @StrReferencedColumn_FK=''''
DECLARE CursorFKDetails CURSOR FOR
SELECT fk.name ForeignKeyName, schema_name(t1.schema_id) ParentTableSchema,
object_name(fkc.parent_object_id) ParentTable, c1.name ParentColumn,schema_name(t2.schema_id) ReferencedTableSchema,
object_name(fkc.referenced_object_id) ReferencedTable,c2.name ReferencedColumn
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id=fkc.constraint_object_id
INNER JOIN sys.columns c1 ON c1.object_id=fkc.parent_object_id AND c1.column_id=fkc.parent_column_id
INNER JOIN sys.columns c2 ON c2.object_id=fkc.referenced_object_id AND c2.column_id=fkc.referenced_column_id
INNER JOIN sys.tables t1 ON t1.object_id=fkc.parent_object_id
INNER JOIN sys.tables t2 ON t2.object_id=fkc.referenced_object_id
WHERE fk.object_id=@ForeignKeyID_FK
OPEN CursorFKDetails
FETCH NEXT FROM CursorFKDetails INTO @ForeignKeyName_FK, @ParentTableSchema_FK, @ParentTableName_FK, @ParentColumn_FK, @ReferencedTableSchema_FK, @ReferencedTable_FK, @ReferencedColumn_FK
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @StrParentColumn_FK=@StrParentColumn_FK + '', '' + quotename(@ParentColumn_FK)
SET @StrReferencedColumn_FK=@StrReferencedColumn_FK + '', '' + quotename(@ReferencedColumn_FK)
FETCH NEXT FROM CursorFKDetails INTO @ForeignKeyName_FK, @ParentTableSchema_FK, @ParentTableName_FK, @ParentColumn_FK, @ReferencedTableSchema_FK, @ReferencedTable_FK, @ReferencedColumn_FK
END
CLOSE CursorFKDetails
DEALLOCATE CursorFKDetails
SET @StrParentColumn_FK=SUBSTRING(@StrParentColumn_FK,2,LEN(@StrParentColumn_FK)-1)
SET @StrReferencedColumn_FK=SUBSTRING(@StrReferencedColumn_FK,2,LEN(@StrReferencedColumn_FK)-1)
SET @TSQLScripCreationForeignKey_FK=''ALTER TABLE ''+quotename(@ParentTableSchema_FK)+''.''+quotename(@ParentTableName_FK)+'' WITH CHECK ADD CONSTRAINT ''+quotename(@ForeignKeyName_FK)
+ '' FOREIGN KEY(''+ltrim(@StrParentColumn_FK)+'') ''+ CHAR(13) +''REFERENCES ''+quotename(@ReferencedTableSchema_FK)+''.''+quotename(@ReferencedTable_FK)+'' (''+ltrim(@StrReferencedColumn_FK)+'') ''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, ConstraintType, QueryIndex, Command)
VALUES(''' + @DBName + N''', @ParentTableName_FK, @ParentTableSchema_FK, @ForeignKeyName_FK, ''FOREIGN KEY'', @TSQLScripCreationForeignKey_FK, ''CREATE'')
FETCH NEXT FROM CursorFK_Create INTO @ForeignKeyID_FK
END
CLOSE CursorFK_Create
DEALLOCATE CursorFK_Create
'
EXEC sp_executesql @query;
FETCH NEXT FROM CursorDatabase INTO @DBName
END
CLOSE CursorDatabase
DEALLOCATE CursorDatabase;
-- Определяем список зависимых таблиц по foreign key
IF (OBJECT_ID('tempdb..#ReferencingTableNameList') IS NOT NULL) BEGIN DROP TABLE #ReferencingTableNameList; END;
WITH TableIndex AS (
SELECT DISTINCT DatabaseName, TableName, SchemaName FROM #TableIndex
),
TableIndexFK AS (
SELECT ti.*, fk.ReferencingTableName, fk.ReferencingSchemaName
FROM TableIndex ti
LEFT JOIN #ForeignKey fk
ON ti.DatabaseName = fk.DatabaseName
AND ti.TableName = fk.ReferencedTableName
AND ti.SchemaName = fk.ReferencedSchemaName
)
SELECT DatabaseName, SchemaName, TableName, STRING_AGG(ReferencingTableName, ';') AS ReferencingTableNameList
INTO #ReferencingTableNameList
FROM TableIndexFK
GROUP BY DatabaseName, SchemaName, TableName
ORDER BY TableName;
-- Выставляем порядок таблиц в зависимости от foreign key
IF (OBJECT_ID('tempdb..#TableOrder') IS NOT NULL) BEGIN DROP TABLE #TableOrder; END;
WITH TableIndex AS (
SELECT DISTINCT DatabaseName, TableName, SchemaName FROM #TableIndex
),
TableIndexFK AS (
SELECT ti.*, fk.ReferencedTableName, fk.ReferencedSchemaName
FROM TableIndex ti
LEFT JOIN #ForeignKey fk
ON ti.DatabaseName = fk.DatabaseName
AND ti.TableName = fk.ReferencingTableName
AND ti.SchemaName = fk.ReferencedSchemaName
),
Recursion AS (
SELECT ti.*, CAST(ISNULL(ti.ReferencedTableName, '') AS VARCHAR(MAX)) AS List, 0 AS Rnk
FROM TableIndexFK ti
WHERE ti.ReferencedTableName IS NULL
UNION ALL
SELECT ti.*, r.List + '/' + CONVERT(VARCHAR(MAX), ISNULL(ti.ReferencedTableName, '')) AS List, r.Rnk + 1 AS Rnk
FROM TableIndexFK ti
INNER JOIN Recursion r
ON ti.DatabaseName = r.DatabaseName
AND ti.TableName = r.ReferencedTableName
AND ti.SchemaName = r.ReferencedSchemaName
)
SELECT DatabaseName, SchemaName, TableName, MAX(Rnk) AS Rnk
INTO #TableOrder
FROM Recursion
GROUP BY DatabaseName, SchemaName, TableName
OPTION(MAXRECURSION 0);
-- Обновляем ранг в основной таблице
UPDATE ti
SET ti.Rnk = ISNULL(tblOrder.Rnk, 0)
FROM #TableIndex ti
LEFT JOIN #TableOrder AS tblOrder
ON ti.DatabaseName = tblOrder.DatabaseName
AND ti.TableName = tblOrder.TableName
AND ti.SchemaName = tblOrder.SchemaName;
-- Фильтрация индексов для пересоздания в зависимости от параметров
UPDATE #TableIndex
SET QueryIndex = NULL
WHERE
-- Не пересоздавать, если не безусловное пересоздание и FG совпадает и FillFactor не задан или равен 80
(@RecreateUnconditionally = 0
AND FileGroupName = @model_FileGroupName
AND ([FillFactor] IS NULL OR [FillFactor] = 0 OR [FillFactor] = 80))
-- Исключаем команды с NULL QueryIndex
AND QueryIndex IS NOT NULL;
-- Генерация итогового скрипта
DECLARE @TableInfo_DatabaseName VARCHAR(300)
,@TableInfo_SchemaName VARCHAR(300)
,@TableInfo_TableName VARCHAR(300)
,@TableInfo_QueryDropIndex VARCHAR(MAX)
,@TableInfo_QueryCreateIndex VARCHAR(MAX)
,@TableInfo_QueryAlterIndex VARCHAR(MAX)
,@TableInfo_QueryDropForeignKey VARCHAR(MAX)
,@TableInfo_QueryCreateForeignKey VARCHAR(MAX)
,@Rnk INT;
PRINT 'SET NOCOUNT ON;' + CHAR(13);
DECLARE CursorTableInfo CURSOR FOR
SELECT DISTINCT ti.DatabaseName
,ti.SchemaName
,ti.TableName
,ti.Rnk
,tidx.QueryDropIndex
,tidx.QueryCreateIndex
,tidx.QueryAlterIndex
,tidx.QueryDropForeignKey
,tidx.QueryCreateForeignKey
FROM #TableIndex ti
LEFT JOIN (
SELECT DatabaseName
,SchemaName
,TableName
--QueryDropIndex
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN Command = 'DROP' AND (ConstraintType <> 'FOREIGN KEY' OR ConstraintType IS NULL) AND QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''IndexName = ' + IndexName + ''';' + CHAR(13) +
'PRINT ''Command = DROP'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + DatabaseName + ''',''' + SchemaName +''',''' + TableName +''',''' + IndexName + ''',''' + REPLACE(QueryIndex, '''', '''''''') + ''',''ALTER'', ''DropIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
CHAR(9) + QueryIndex + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ';'
END
FROM #TableIndex AS t1 WITH(NOLOCK)
WHERE 1=1
AND t.DatabaseName = t1.DatabaseName AND t.SchemaName = t1.SchemaName AND t.TableName = t1.TableName
AND t1.QueryIndex IS NOT NULL
ORDER BY CASE WHEN t1.IndexType = 'CLUSTERED' THEN 0 ELSE 1 END DESC, t1.IndexName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryDropIndex
--QueryCreateIndex
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN Command = 'CREATE' AND (ConstraintType <> 'FOREIGN KEY' OR ConstraintType IS NULL) AND QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''IndexName = ' + IndexName + ''';' + CHAR(13) +
'PRINT ''Command = CREATE'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + DatabaseName + ''',''' + SchemaName +''',''' + TableName +''',''' + IndexName + ''',''' + REPLACE(QueryIndex, '''', '''''''') + ''',''ALTER'', ''CreateIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = ''' + IndexName + ''' AND object_id = OBJECT_ID(''' + SchemaName + '.' + TableName + '''))' + CHAR(13) +
'BEGIN' + CHAR(13) +
QueryIndex + CHAR(13) +
'END' + CHAR(13) +
'ELSE' + CHAR(13) +
'BEGIN' + CHAR(13) +
'PRINT ''Index ' + IndexName + ' already exists, skipping...'';' + CHAR(13) +
'END' + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ';'
END
FROM #TableIndex AS t1 WITH(NOLOCK)
WHERE 1=1
AND t.DatabaseName = t1.DatabaseName AND t.SchemaName = t1.SchemaName AND t.TableName = t1.TableName
AND t1.QueryIndex IS NOT NULL
ORDER BY CASE WHEN t1.IndexType = 'CLUSTERED' THEN 1 ELSE 0 END DESC, t1.IndexName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryCreateIndex
--QueryAlterIndex
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN Command = 'ALTER' AND QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''IndexName = ' + IndexName + ''';' + CHAR(13) +
'PRINT ''Command = ALTER'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + DatabaseName + ''',''' + SchemaName +''',''' + TableName +''',''' + IndexName + ''',''' + REPLACE(QueryIndex, '''', '''''''') + ''',''ALTER'', ''AlterIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
QueryIndex + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ';'
END
FROM #TableIndex AS t1 WITH(NOLOCK)
WHERE 1=1
AND t.DatabaseName = t1.DatabaseName AND t.SchemaName = t1.SchemaName AND t.TableName = t1.TableName
AND t1.QueryIndex IS NOT NULL
ORDER BY IndexName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryAlterIndex
--QueryDropForeignKey
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN t_ti.Command = 'DROP' AND t_ti.ConstraintType = 'FOREIGN KEY' AND t_ti.QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + t_ti.IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''ForeignKeyName = ' + t_ti.IndexName + ''';' + CHAR(13) +
'PRINT ''Command = DROP'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(t_ti.QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + t_ti.DatabaseName + ''',''' + t_ti.SchemaName +''',''' + t_ti.TableName +''',''' + t_ti.IndexName + ''',''' + REPLACE(t_ti.QueryIndex, '''', '''''''') + ''',''ALTER'', ''DropIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
CHAR(9) + t_ti.QueryIndex + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ';'
END
FROM (
SELECT rt.DatabaseName, rt.SchemaName, oa.value AS ReferencingTableName, rt.TableName
FROM #ReferencingTableNameList AS rt
OUTER APPLY (SELECT * FROM dbo.SplitString(rt.ReferencingTableNameList, ';')) AS oa
WHERE oa.value IS NOT NULL
) AS rt
INNER JOIN #TableIndex AS t_ti
ON rt.DatabaseName = t_ti.DatabaseName AND rt.SchemaName = t_ti.SchemaName AND rt.ReferencingTableName = t_ti.TableName
WHERE 1=1
AND t.DatabaseName = t_ti.DatabaseName AND t.SchemaName = t_ti.SchemaName AND t.TableName = rt.TableName
AND t_ti.QueryIndex IS NOT NULL
ORDER BY rt.TableName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryDropForeignKey
--QueryCreateForeignKey
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN t_ti.Command = 'CREATE' AND t_ti.ConstraintType = 'FOREIGN KEY' AND t_ti.QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + t_ti.IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''IndexName = ' + t_ti.IndexName + ''';' + CHAR(13) +
'PRINT ''Command = CREATE'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(t_ti.QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + t_ti.DatabaseName + ''',''' + t_ti.SchemaName +''',''' + t_ti.TableName +''',''' + t_ti.IndexName + ''',''' + REPLACE(t_ti.QueryIndex, '''', '''''''') + ''',''ALTER'', ''CreateIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = ''' + t_ti.IndexName + ''' AND parent_object_id = OBJECT_ID(''' + t_ti.SchemaName + '.' + t_ti.TableName + '''))' + CHAR(13) +
'BEGIN' + CHAR(13) +
t_ti.QueryIndex + CHAR(13) +
'END' + CHAR(13) +
'ELSE' + CHAR(13) +
'BEGIN' + CHAR(13) +
'PRINT ''Foreign Key ' + t_ti.IndexName + ' already exists, skipping...'';' + CHAR(13) +
'END' + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ';'
END
FROM (
SELECT rt.DatabaseName, rt.SchemaName, oa.value AS ReferencingTableName, rt.TableName
FROM #ReferencingTableNameList AS rt
OUTER APPLY (SELECT * FROM dbo.SplitString(rt.ReferencingTableNameList, ';')) AS oa
WHERE oa.value IS NOT NULL
) AS rt
INNER JOIN #TableIndex AS t_ti
ON rt.DatabaseName = t_ti.DatabaseName AND rt.SchemaName = t_ti.SchemaName AND rt.ReferencingTableName = t_ti.TableName
WHERE 1=1
AND t.DatabaseName = t_ti.DatabaseName AND t.SchemaName = t_ti.SchemaName AND t.TableName = rt.TableName
AND t_ti.QueryIndex IS NOT NULL
ORDER BY rt.TableName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryCreateForeignKey
FROM #TableIndex AS t
WHERE t.QueryIndex IS NOT NULL
GROUP BY DatabaseName, SchemaName, TableName
) AS tidx
ON ti.DatabaseName = tidx.DatabaseName AND ti.SchemaName = tidx.SchemaName AND ti.TableName = tidx.TableName
WHERE ti.QueryIndex IS NOT NULL
ORDER BY ti.DatabaseName, ti.SchemaName, ti.Rnk, ti.TableName
OPEN CursorTableInfo
FETCH NEXT FROM CursorTableInfo INTO @TableInfo_DatabaseName, @TableInfo_SchemaName, @TableInfo_TableName, @Rnk, @TableInfo_QueryDropIndex, @TableInfo_QueryCreateIndex, @TableInfo_QueryAlterIndex, @TableInfo_QueryDropForeignKey, @TableInfo_QueryCreateForeignKey
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '----------------------------------------------------------------------------------------------------------';
PRINT 'USE ' + @TableInfo_DatabaseName + ';';
PRINT '--';
PRINT '-- DataBaseName = ' + @TableInfo_DatabaseName;
PRINT '-- SchemaName = ' + @TableInfo_SchemaName;
PRINT '-- TableName = ' + @TableInfo_TableName;
PRINT '--';
PRINT 'PRINT ''----------------------------------------------------------------------------------------------------------''';
PRINT 'PRINT ''--'';';
PRINT 'PRINT ''DataBaseName = ' + @TableInfo_DatabaseName + ''';';
PRINT 'PRINT ''SchemaName = ' + @TableInfo_SchemaName + ''';';
PRINT 'PRINT ''TableName = ' + @TableInfo_TableName + ''';';
IF(@TableInfo_QueryDropForeignKey <> '')
BEGIN
PRINT '--';
PRINT '-- DROP FOREIGN KEY';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryDropForeignKey;
END;
IF(@TableInfo_QueryDropIndex <> '')
BEGIN
PRINT '--';
PRINT '-- DROP INDEX';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryDropIndex;
END;
IF(@TableInfo_QueryCreateIndex <> '')
BEGIN
PRINT '--';
PRINT '-- CREATE INDEX';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryCreateIndex;
END;
IF(@TableInfo_QueryAlterIndex <> '')
BEGIN
PRINT '--';
PRINT '-- ALTER INDEX';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryAlterIndex;
END;
IF(@TableInfo_QueryCreateForeignKey <> '')
BEGIN
PRINT '--';
PRINT '-- CREATE FOREIGN KEY';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryCreateForeignKey;
END;
PRINT '--';
PRINT 'GO'
PRINT 'PRINT ''--'';';
FETCH NEXT FROM CursorTableInfo INTO @TableInfo_DatabaseName, @TableInfo_SchemaName, @TableInfo_TableName, @Rnk, @TableInfo_QueryDropIndex, @TableInfo_QueryCreateIndex, @TableInfo_QueryAlterIndex, @TableInfo_QueryDropForeignKey, @TableInfo_QueryCreateForeignKey
END
CLOSE CursorTableInfo
DEALLOCATE CursorTableInfo;
--drop table #TableIndex
/*
Вы можете создать собственную функцию dbo.SplitString, которая будет работать как STRING_SPLIT. При старой версии sql
Шаг 1: Создайте пользовательскую функцию (если нет)
IF OBJECT_ID('dbo.SplitString', 'TF') IS NOT NULL
DROP FUNCTION dbo.SplitString;
GO
CREATE FUNCTION dbo.SplitString
(
@Input NVARCHAR(MAX),
@Delimiter NCHAR(1)
)
RETURNS @Output TABLE (
Value NVARCHAR(4000)
)
AS
BEGIN
IF @Input IS NULL RETURN;
DECLARE @Start INT = 1, @End INT;
WHILE @Start <= LEN(@Input)
BEGIN
SET @End = CHARINDEX(@Delimiter, @Input, @Start);
IF @End = 0
SET @End = LEN(@Input) + 1;
INSERT INTO @Output (Value)
VALUES (LTRIM(RTRIM(SUBSTRING(@Input, @Start, @End - @Start))));
SET @Start = @End + 1;
END
RETURN;
END;
-- После формирования #TableIndex и фильтрации
SELECT
DatabaseName,
SchemaName,
TableName,
IndexName,
Command, -- CREATE / DROP / ALTER
QueryIndex
FROM #TableIndex
WHERE QueryIndex IS NOT NULL
ORDER BY DatabaseName, SchemaName, TableName, Command DESC;
*/
Что бы работало надо [statdb].[dbo].[LongPrint]
USE [statdb]
GO
/****** Object: StoredProcedure [dbo].[LongPrint] Script Date: 18.09.2025 14:32:12 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LongPrint]
@String NVARCHAR(MAX)
AS
/*
Example:
exec LongPrint @string =
'This String
Exists to test
the system.'
*/
/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).
It will print the text passed to it in substrings smaller than 4000
characters. If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.
If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.
If it is passed a null value, it will do virtually nothing.
NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.
*/
DECLARE
@CurrentEnd BIGINT, /* track the length of the next substring */
@offset tinyint /*tracks the amount of offset needed */
set @string = replace( replace(@string, char(13) + char(10), char(10)) , char(13), char(10))
WHILE LEN(@String) > 1
BEGIN
IF CHARINDEX(CHAR(10), @String) between 1 AND 4000
BEGIN
SET @CurrentEnd = CHARINDEX(char(10), @String) -1
set @offset = 2
END
ELSE
BEGIN
SET @CurrentEnd = 4000
set @offset = 1
END
PRINT SUBSTRING(@String, 1, @CurrentEnd)
set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)
END /*End While loop*/
GO
И логи в таблицу
USE [DBATools]
GO
/****** Object: Table [dbo].[CheckProjectTables_log] Script Date: 18.09.2025 14:33:32 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[CheckProjectTables_log](
[Id] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [sysname] NOT NULL,
[SchemaName] [sysname] NOT NULL,
[TableName] [sysname] NOT NULL,
[ColumnName] [sysname] NULL,
[IndexName] [sysname] NULL,
[Command] [varchar](max) NOT NULL,
[CommandType] [varchar](300) NOT NULL,
[CommandSubType] [varchar](300) NULL,
[StartTime] [datetime] NOT NULL,
[StopTime] [datetime] NULL,
[Result] [varchar](300) NULL,
[ErrorNumber] [int] NULL,
[ErrorMessage] [varchar](max) NULL,
CONSTRAINT [PK_CheckProjectTables_log] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [DBATools_FG_Current]
) ON [DBATools_FG_Current] TEXTIMAGE_ON [DBATools_FG_Current]
GO
Если версия mssql 2016 и выше
SET NOCOUNT ON;
-- Параметры скрипта
DECLARE @model_FileGroupName NVARCHAR(300) = NULL -- NULL = использовать файловую группу по умолчанию
,@ChangeFileGroup BIT = 1
,@RecreateUnconditionally BIT = 0 -- 0 = пересоздавать только при несоответствии FG или FillFactor, 1 = пересоздавать все
,@RecreateNonClusteredOnly BIT = 0 -- 0 = пересоздавать все индексы, 1 = только некластеризованные
,@IncludeDatabases NVARCHAR(MAX) = 'hds' -- Список баз данных через запятую, NULL = все
,@ExcludeDatabases NVARCHAR(MAX) = NULL -- Список исключаемых баз данных через запятую
,@IncludeTables NVARCHAR(MAX) = NULL -- Список таблиц через запятую, NULL = все
,@ExcludeTables NVARCHAR(MAX) = NULL -- Список исключаемых таблиц через запятую
-- Если файловая группа не указана, определяем её по умолчанию
IF @model_FileGroupName IS NULL
BEGIN
SELECT @model_FileGroupName = name
FROM sys.filegroups
WHERE is_default = 1;
END
-- Создание временных таблиц
IF (OBJECT_ID('tempdb..#TableColumn') IS NOT NULL) BEGIN DROP TABLE #TableColumn; END;
CREATE TABLE #TableColumn (
DatabaseName VARCHAR(200) NOT NULL
,TableName VARCHAR(200) NOT NULL
,SchemaName VARCHAR(200) NOT NULL
,ColumnName VARCHAR(200) NOT NULL
,ColumnType VARCHAR(200) NOT NULL
,ColumnLenght SMALLINT NULL
,ColumnCollation VARCHAR(200) NULL
,ColumnIsNullable BIT NOT NULL
,ColumnIsIdentity BIT NOT NULL
);
IF (OBJECT_ID('tempdb..#TableIndex') IS NOT NULL) BEGIN DROP TABLE #TableIndex; END;
CREATE TABLE #TableIndex (
RowNum INT NOT NULL IDENTITY(1, 1)
,DatabaseName VARCHAR(500) NOT NULL
,TableName VARCHAR(500) NOT NULL
,SchemaName VARCHAR(500) NOT NULL
,IndexName VARCHAR(500) NOT NULL
--может быть NULL в случае alter index disable
,IndexType VARCHAR(100) NULL
,ConstraintType VARCHAR(500) NULL
--может быть NULL в случае Foreign Key
,FileGroupName VARCHAR(500) /*not*/ NULL
,[FillFactor] TINYINT NULL
,QueryIndex VARCHAR(MAX) NOT NULL
,Command VARCHAR(50) NOT NULL
,Rnk INT NULL
);
IF (OBJECT_ID('tempdb..#ForeignKey') IS NOT NULL) BEGIN DROP TABLE #ForeignKey; END;
CREATE TABLE #ForeignKey (
DatabaseName VARCHAR(500) NOT NULL
,ConstraintName VARCHAR(500) NOT NULL
,ReferencingTableName VARCHAR(500) NOT NULL
,ReferencingSchemaName VARCHAR(500) NOT NULL
,ReferencingColumnName VARCHAR(500) NOT NULL
,ReferencedTableName VARCHAR(500) NOT NULL
,ReferencedSchemaName VARCHAR(500) NOT NULL
,ReferencedColumnName VARCHAR(500) NOT NULL
,DeleteReferentialActionDesc VARCHAR(500) NOT NULL
,UpdateReferentialActionDesc VARCHAR(500) NOT NULL
);
-- Создание временной таблицы для списка баз данных
IF (OBJECT_ID('tempdb..#FilteredDatabases') IS NOT NULL) BEGIN DROP TABLE #FilteredDatabases; END;
CREATE TABLE #FilteredDatabases (name NVARCHAR(128));
-- Заполнение таблицы отфильтрованными базами данных
INSERT INTO #FilteredDatabases
SELECT [name]
FROM master.sys.databases
WHERE database_id > 4 AND state = 0
-- Применение фильтров для баз данных
AND (@IncludeDatabases IS NULL OR [name] IN (SELECT value FROM STRING_SPLIT(@IncludeDatabases, ',')))
AND (@ExcludeDatabases IS NULL OR [name] NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeDatabases, ',')))
-- Для тестирования
-- AND name = 'AlfaBankITM_C4T_dev'
-- Объявление переменных для цикла
DECLARE @DBName NVARCHAR(200);
DECLARE @query NVARCHAR(MAX); -- Объявляем переменную здесь, чтобы она была доступна во всем скрипте
-- Курсор по отфильтрованным базам данных
DECLARE CursorDatabase CURSOR FOR
SELECT name FROM #FilteredDatabases
OPEN CursorDatabase
FETCH NEXT FROM CursorDatabase INTO @DBName
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Проверка фильтров для таблиц
DECLARE @FilterSql NVARCHAR(MAX) = N'
DECLARE @IncludeTables BIT = ' + CASE WHEN @IncludeTables IS NULL THEN '0' ELSE '1' END + ';
DECLARE @ExcludeTables BIT = ' + CASE WHEN @ExcludeTables IS NULL THEN '0' ELSE '1' END + ';
DECLARE @IncludeTableList NVARCHAR(MAX) = ''' + ISNULL(@IncludeTables, '') + ''';
DECLARE @ExcludeTableList NVARCHAR(MAX) = ''' + ISNULL(@ExcludeTables, '') + ''';
';
SET @query = N'USE ' + @DBName + N'; ' + @FilterSql + N'
DECLARE @TableName VARCHAR(500),
@SchemaName VARCHAR(100);
DECLARE @IndexName VARCHAR(256)
DECLARE @ColumnName VARCHAR(100)
DECLARE @is_unique VARCHAR(100)
DECLARE @IndexTypeDesc VARCHAR(100)
DECLARE @FileGroupName VARCHAR(300)
DECLARE @is_disabled VARCHAR(100)
DECLARE @fill_factor TINYINT
DECLARE @IndexOptions VARCHAR(MAX)
DECLARE @IndexFilter VARCHAR(MAX)
DECLARE @IndexColumnId INT
DECLARE @IsDescendingKey INT
DECLARE @IsIncludedColumn INT
DECLARE @TSQLScripCreationIndex VARCHAR(MAX)
DECLARE @TSQLScripDisableIndex VARCHAR(MAX)
DECLARE @TSQLDropIndex VARCHAR(MAX)
/**************** T-SQL Script to Get Foreign Key ****************/
INSERT INTO #ForeignKey
(DatabaseName,ConstraintName,ReferencingTableName,ReferencingSchemaName,ReferencingColumnName,ReferencedTableName,ReferencedSchemaName,ReferencedColumnName,DeleteReferentialActionDesc,UpdateReferentialActionDesc)
SELECT ''' + @DBName + N''' AS DatabaseName
,f.name AS ConstraintName
,OBJECT_NAME(f.parent_object_id) AS ReferencingTableName
,schema_name(tp.schema_id) AS ReferencingSchemaName
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferencingColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName
,schema_name(tr.schema_id) AS ReferencedSchemaName
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS ReferencedColumnName
,delete_referential_action_desc AS DeleteReferentialActionDesc
,update_referential_action_desc AS UpdateReferentialActionDesc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
INNER JOIN sys.tables tp
ON tp.object_id= f.parent_object_id
INNER JOIN sys.tables tr
ON tr.object_id= f.referenced_object_id
-- Применение фильтров для таблиц
WHERE (@IncludeTables = 0 OR OBJECT_NAME(f.parent_object_id) IN (SELECT value FROM STRING_SPLIT(@IncludeTableList, '',''))
OR OBJECT_NAME(f.referenced_object_id) IN (SELECT value FROM STRING_SPLIT(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR (OBJECT_NAME(f.parent_object_id) NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeTableList, '',''))
AND OBJECT_NAME(f.referenced_object_id) NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeTableList, '',''))))
ORDER BY f.name
-----------------------------------------------------------------------------------------------------
/**************** T-SQL Script to Drop All SQL Server Indexes ****************/
DECLARE CursorDropIndexes CURSOR FOR
SELECT schema_name(t.schema_id)
,t.name
,i.name
,i.type_desc
,i.fill_factor
,FILEGROUP_NAME(i.data_space_id) FileGroupName
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 AND t.is_ms_shipped=0 AND t.name<>''sysdiagrams''
AND (is_primary_key=0 AND is_unique_constraint=0)
-- Применение фильтров для таблиц
AND (@IncludeTables = 0 OR t.name IN (SELECT value FROM STRING_SPLIT(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeTableList, '','')))
-- Фильтр для некластеризованных индексов
AND (' + CAST(@RecreateNonClusteredOnly AS NVARCHAR(1)) + ' = 0 OR i.type_desc <> ''CLUSTERED'')
OPEN CursorDropIndexes
FETCH NEXT FROM CursorDropIndexes INTO @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @fill_factor, @FileGroupName
WHILE @@fetch_status = 0
BEGIN
SET @TSQLDropIndex = ''DROP INDEX '' + QUOTENAME(@SchemaName) + ''.'' + QUOTENAME(@TableName) + ''.'' +QUOTENAME(@IndexName) + '';''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, IndexType, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(''' + @DBName + N''', @TableName, @SchemaName, @IndexName, @IndexTypeDesc, @FileGroupName, @fill_factor, @TSQLDropIndex, ''DROP'')
FETCH NEXT FROM CursorDropIndexes INTO @SchemaName, @TableName, @IndexName, @IndexTypeDesc, @fill_factor, @FileGroupName
END
CLOSE CursorDropIndexes
DEALLOCATE CursorDropIndexes
/**************** T-SQL Script to Create All SQL Server Indexes ****************/
DECLARE CursorIndex CURSOR FOR
SELECT schema_name(t.schema_id) [schema_name]
,t.name
,ix.name
,CASE WHEN ix.is_unique = 1 THEN ''UNIQUE '' ELSE '''' END
,ix.type_desc
,CASE WHEN ix.is_padded=1 THEN ''PAD_INDEX = ON, '' ELSE ''PAD_INDEX = OFF, '' END
+ CASE WHEN ix.allow_page_locks=1 THEN ''ALLOW_PAGE_LOCKS = ON, '' ELSE ''ALLOW_PAGE_LOCKS = OFF, '' END
+ CASE WHEN ix.allow_row_locks=1 THEN ''ALLOW_ROW_LOCKS = ON, '' ELSE ''ALLOW_ROW_LOCKS = OFF, '' END
+ CASE WHEN INDEXPROPERTY(t.object_id, ix.name, ''IsStatistics'') = 1 THEN ''STATISTICS_NORECOMPUTE = ON, '' ELSE ''STATISTICS_NORECOMPUTE = OFF, '' END
+ CASE WHEN ix.ignore_dup_key=1 THEN ''IGNORE_DUP_KEY = ON, '' ELSE ''IGNORE_DUP_KEY = OFF, '' END
+ ''SORT_IN_TEMPDB = OFF'' + CASE WHEN ix.fill_factor > 0 AND ix.fill_factor <> 80 THEN '', FILLFACTOR ='' + CAST(ix.fill_factor AS VARCHAR(3)) ELSE '''' END AS IndexOptions
,CASE WHEN ix.has_filter = 1 THEN ''WHERE '' + ix.filter_definition ELSE '''' END AS IndexFilter
,ix.is_disabled
,ix.fill_factor
,FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
INNER JOIN sys.indexes ix ON t.object_id=ix.object_id
WHERE ix.type>0 AND ix.is_primary_key=0 AND ix.is_unique_constraint=0
AND t.is_ms_shipped=0 AND t.name<>''sysdiagrams''
-- Применение фильтров для таблиц
AND (@IncludeTables = 0 OR t.name IN (SELECT value FROM STRING_SPLIT(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeTableList, '','')))
-- Фильтр для некластеризованных индексов
AND (' + CAST(@RecreateNonClusteredOnly AS NVARCHAR(1)) + ' = 0 OR ix.type_desc <> ''CLUSTERED'')
ORDER BY schema_name(t.schema_id), t.name, ix.name
OPEN CursorIndex
FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @IndexFilter, @is_disabled, @fill_factor, @FileGroupName
WHILE (@@fetch_status=0)
BEGIN
DECLARE @IndexColumns VARCHAR(MAX)
DECLARE @IncludedColumns VARCHAR(MAX)
SET @IndexColumns=''''
SET @IncludedColumns=''''
DECLARE CursorIndexColumn CURSOR FOR
SELECT col.name, ixc.is_descending_key, ixc.is_included_column
FROM sys.tables tb
INNER JOIN sys.indexes ix ON tb.object_id=ix.object_id
INNER JOIN sys.index_columns ixc ON ix.object_id=ixc.object_id AND ix.index_id= ixc.index_id
INNER JOIN sys.columns col ON ixc.object_id =col.object_id AND ixc.column_id=col.column_id
WHERE ix.type>0 AND (ix.is_primary_key=0 OR ix.is_unique_constraint=0)
AND schema_name(tb.schema_id)=@SchemaName AND tb.name=@TableName AND ix.name=@IndexName
ORDER BY ixc.index_column_id
OPEN CursorIndexColumn
FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn
WHILE (@@fetch_status=0)
BEGIN
IF @IsIncludedColumn=0
SET @IndexColumns=@IndexColumns + @ColumnName + CASE WHEN @IsDescendingKey=1 THEN '' DESC, '' ELSE '' ASC, '' END
ELSE
SET @IncludedColumns=@IncludedColumns + @ColumnName +'', ''
FETCH NEXT FROM CursorIndexColumn INTO @ColumnName, @IsDescendingKey, @IsIncludedColumn
END
CLOSE CursorIndexColumn
DEALLOCATE CursorIndexColumn
SET @IndexColumns = SUBSTRING(@IndexColumns, 1, LEN(@IndexColumns)-1)
SET @IncludedColumns = CASE WHEN LEN(@IncludedColumns) >0 THEN SUBSTRING(@IncludedColumns, 1, LEN(@IncludedColumns)-1) ELSE '''' END
SET @TSQLScripCreationIndex =''CREATE ''+ @is_unique + @IndexTypeDesc + '' INDEX '' + QUOTENAME(@IndexName) + '' ON '' + QUOTENAME(@SchemaName) + ''.''+ QUOTENAME(@TableName)+ ''(''+@IndexColumns+'') '' +
CASE WHEN LEN(@IncludedColumns)>0 THEN CHAR(13) +''INCLUDE ('' + @IncludedColumns+ '')'' ELSE '''' END + @IndexFilter + CHAR(13)+''WITH ('' + @IndexOptions+ '') ON '' + QUOTENAME(''' + @model_FileGroupName + ''') + '';''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, IndexType, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(DB_NAME(), @TableName, @SchemaName, @IndexName, @IndexTypeDesc, @FileGroupName, @fill_factor, @TSQLScripCreationIndex, ''CREATE'')
IF (@is_disabled=1)
BEGIN
SET @TSQLScripDisableIndex= CHAR(13) +''ALTER INDEX '' +QUOTENAME(@IndexName) + '' ON '' + QUOTENAME(@SchemaName) +''.''+ QUOTENAME(@TableName) + '' DISABLE;'' + CHAR(13)
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(''' + @DBName + N''', @TableName, @SchemaName, @IndexName, @FileGroupName, @fill_factor, @TSQLScripDisableIndex, ''ALTER'')
END
FETCH NEXT FROM CursorIndex INTO @SchemaName, @TableName, @IndexName, @is_unique, @IndexTypeDesc, @IndexOptions, @IndexFilter, @is_disabled, @fill_factor, @FileGroupName
END
CLOSE CursorIndex
DEALLOCATE CursorIndex
-----------------------------------------------------------------------------------------------------
/********** SCRIPT TO GENERATE THE DROP SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS. **********/
DECLARE @SchemaName_PK VARCHAR(256)
DECLARE @TableName_PK VARCHAR(256)
DECLARE @IndexName_PK VARCHAR(256)
DECLARE @fill_factor_PK TINYINT
DECLARE @FileGroupName_PK VARCHAR(300)
DECLARE @TSQLDropIndex_PK VARCHAR(MAX)
DECLARE @ColumnName_PK VARCHAR(100)
DECLARE @is_unique_constraint_PK VARCHAR(100)
DECLARE @IndexTypeDesc_PK VARCHAR(100)
DECLARE @is_disabled_PK VARCHAR(100)
DECLARE @IndexOptions_PK VARCHAR(MAX)
DECLARE @IndexColumnId_PK INT
DECLARE @IsDescendingKey_PK INT
DECLARE @IsIncludedColumn_PK INT
DECLARE @TSQLScripCreationIndex_PK VARCHAR(MAX)
DECLARE @TSQLScripDisableIndex_PK VARCHAR(MAX)
DECLARE @is_primary_key_PK VARCHAR(100)
DECLARE CursorDropIndexes_PK CURSOR FOR
SELECT schema_name(t.schema_id)
,t.name
,i.name
,CASE WHEN i.is_unique_constraint = 1 THEN '' UNIQUE '' ELSE '''' END
,CASE WHEN i.is_primary_key = 1 THEN '' PRIMARY KEY '' ELSE '''' END
,i.type_desc
,i.fill_factor
,FILEGROUP_NAME(i.data_space_id) FileGroupName
FROM sys.indexes i
INNER JOIN sys.tables t ON t.object_id= i.object_id
WHERE i.type>0 AND t.is_ms_shipped=0 AND t.name<>''sysdiagrams''
AND (is_primary_key=1 OR is_unique_constraint=1)
-- Применение фильтров для таблиц
AND (@IncludeTables = 0 OR t.name IN (SELECT value FROM STRING_SPLIT(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeTableList, '','')))
-- Фильтр для некластеризованных индексов
AND (' + CAST(@RecreateNonClusteredOnly AS NVARCHAR(1)) + ' = 0 OR i.type_desc <> ''CLUSTERED'')
OPEN CursorDropIndexes_PK
FETCH NEXT FROM CursorDropIndexes_PK INTO @SchemaName_PK, @TableName_PK, @IndexName_PK, @is_unique_constraint_PK, @is_primary_key_PK, @IndexTypeDesc_PK, @fill_factor_PK, @FileGroupName_PK
WHILE @@fetch_status = 0
BEGIN
SET @TSQLDropIndex_PK = ''ALTER TABLE '' + QUOTENAME(@SchemaName_PK) + ''.'' + QUOTENAME(@TableName_PK) + '' DROP CONSTRAINT '' +QUOTENAME(@IndexName_PK) + '';''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, IndexType, ConstraintType, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(''' + @DBName + N''', @TableName_PK, @SchemaName_PK, @IndexName_PK, @IndexTypeDesc_PK, ISNULL(CASE WHEN @is_primary_key_PK = '''' THEN NULL ELSE @is_primary_key_PK END, CASE WHEN @is_unique_constraint_PK = '''' THEN NULL ELSE @is_unique_constraint_PK END), @FileGroupName_PK, @fill_factor_PK, @TSQLDropIndex_PK, ''DROP'')
FETCH NEXT FROM CursorDropIndexes_PK INTO @SchemaName_PK, @TableName_PK, @IndexName_PK, @is_unique_constraint_PK, @is_primary_key_PK, @IndexTypeDesc_PK, @fill_factor_PK, @FileGroupName_PK
END
CLOSE CursorDropIndexes_PK
DEALLOCATE CursorDropIndexes_PK
/********** SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL PK AND UNIQUE CONSTRAINTS. **********/
DECLARE CursorCreateIndex_PK CURSOR FOR
SELECT schema_name(t.schema_id) [schema_name], t.name, ix.name
,CASE WHEN ix.is_unique_constraint = 1 THEN '' UNIQUE '' ELSE '''' END
,CASE WHEN ix.is_primary_key = 1 THEN '' PRIMARY KEY '' ELSE '''' END
,ix.type_desc
,CASE WHEN ix.is_padded=1 THEN ''PAD_INDEX = ON, '' ELSE ''PAD_INDEX = OFF, '' END
+ CASE WHEN ix.allow_page_locks=1 THEN ''ALLOW_PAGE_LOCKS = ON, '' ELSE ''ALLOW_PAGE_LOCKS = OFF, '' END
+ CASE WHEN ix.allow_row_locks=1 THEN ''ALLOW_ROW_LOCKS = ON, '' ELSE ''ALLOW_ROW_LOCKS = OFF, '' END
+ CASE WHEN INDEXPROPERTY(t.object_id, ix.name, ''IsStatistics'') = 1 THEN ''STATISTICS_NORECOMPUTE = ON, '' ELSE ''STATISTICS_NORECOMPUTE = OFF, '' END
+ CASE WHEN ix.ignore_dup_key=1 THEN ''IGNORE_DUP_KEY = ON, '' ELSE ''IGNORE_DUP_KEY = OFF, '' END
+ ''SORT_IN_TEMPDB = OFF'' + CASE WHEN ix.fill_factor > 0 AND ix.fill_factor <> 80 THEN '', FILLFACTOR ='' + CAST(ix.fill_factor AS VARCHAR(3)) ELSE '''' END AS IndexOptions
,ix.fill_factor
,FILEGROUP_NAME(ix.data_space_id) FileGroupName
FROM sys.tables t
INNER JOIN sys.indexes ix ON t.object_id=ix.object_id
WHERE ix.type>0 AND (ix.is_primary_key=1 OR ix.is_unique_constraint=1)
AND t.is_ms_shipped=0 AND t.name<>''sysdiagrams''
-- Применение фильтров для таблиц
AND (@IncludeTables = 0 OR t.name IN (SELECT value FROM STRING_SPLIT(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeTableList, '','')))
-- Фильтр для некластеризованных индексов
AND (' + CAST(@RecreateNonClusteredOnly AS NVARCHAR(1)) + ' = 0 OR ix.type_desc <> ''CLUSTERED'')
ORDER BY schema_name(t.schema_id), t.name, ix.name
OPEN CursorCreateIndex_PK
FETCH NEXT FROM CursorCreateIndex_PK INTO @SchemaName_PK, @TableName_PK, @IndexName_PK, @is_unique_constraint_PK, @is_primary_key_PK, @IndexTypeDesc_PK, @IndexOptions_PK, @fill_factor_PK, @FileGroupName_PK
WHILE (@@fetch_status=0)
BEGIN
DECLARE @IndexColumns_PK VARCHAR(MAX)
DECLARE @IncludedColumns_PK VARCHAR(MAX)
SET @IndexColumns_PK=''''
SET @IncludedColumns_PK=''''
DECLARE CursorCreateIndex_PKColumn CURSOR FOR
SELECT col.name, ixc.is_descending_key, ixc.is_included_column
FROM sys.tables tb
INNER JOIN sys.indexes ix ON tb.object_id=ix.object_id
INNER JOIN sys.index_columns ixc ON ix.object_id=ixc.object_id AND ix.index_id= ixc.index_id
INNER JOIN sys.columns col ON ixc.object_id =col.object_id AND ixc.column_id=col.column_id
WHERE ix.type>0 AND (ix.is_primary_key=1 OR ix.is_unique_constraint=1)
AND schema_name(tb.schema_id)=@SchemaName_PK AND tb.name=@TableName_PK AND ix.name=@IndexName_PK
ORDER BY ixc.index_column_id
OPEN CursorCreateIndex_PKColumn
FETCH NEXT FROM CursorCreateIndex_PKColumn INTO @ColumnName_PK, @IsDescendingKey_PK, @IsIncludedColumn_PK
WHILE (@@fetch_status=0)
BEGIN
IF @IsIncludedColumn_PK=0
SET @IndexColumns_PK=@IndexColumns_PK + @ColumnName_PK + CASE WHEN @IsDescendingKey_PK=1 THEN '' DESC, '' ELSE '' ASC, '' END
ELSE
SET @IncludedColumns_PK=@IncludedColumns_PK + @ColumnName_PK +'', ''
FETCH NEXT FROM CursorCreateIndex_PKColumn INTO @ColumnName_PK, @IsDescendingKey_PK, @IsIncludedColumn_PK
END
CLOSE CursorCreateIndex_PKColumn
DEALLOCATE CursorCreateIndex_PKColumn
SET @IndexColumns_PK = SUBSTRING(@IndexColumns_PK, 1, LEN(@IndexColumns_PK)-1)
SET @IncludedColumns_PK = CASE WHEN LEN(@IncludedColumns_PK) >0 THEN SUBSTRING(@IncludedColumns_PK, 1, LEN(@IncludedColumns_PK)-1) ELSE '''' END
SET @TSQLScripCreationIndex_PK =''ALTER TABLE '' + QUOTENAME(@SchemaName_PK) +''.''+ QUOTENAME(@TableName_PK) + '' ADD CONSTRAINT '' + QUOTENAME(@IndexName_PK) + @is_unique_constraint_PK + @is_primary_key_PK + +@IndexTypeDesc_PK + ''(''+@IndexColumns_PK+'') ''+
CASE WHEN LEN(@IncludedColumns_PK)>0 THEN CHAR(13) +''INCLUDE ('' + @IncludedColumns_PK+ '')'' ELSE '''' END + CHAR(13)+''WITH ('' + @IndexOptions_PK + '') ON '' + QUOTENAME(CASE WHEN ' + CAST(@ChangeFileGroup AS NVARCHAR(1)) + N' = 1 THEN ''' + @model_FileGroupName +''' ELSE @FileGroupName_PK END) + '';''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, IndexType, ConstraintType, FileGroupName, [FillFactor], QueryIndex, Command)
VALUES(''' + @DBName + N''', @TableName_PK, @SchemaName_PK, @IndexName_PK, @IndexTypeDesc_PK, ISNULL(CASE WHEN @is_primary_key_PK = '''' THEN NULL ELSE @is_primary_key_PK END, CASE WHEN @is_unique_constraint_PK = '''' THEN NULL ELSE @is_unique_constraint_PK END), @FileGroupName_PK, @fill_factor_PK, @TSQLScripCreationIndex_PK, ''CREATE'')
FETCH NEXT FROM CursorCreateIndex_PK INTO @SchemaName_PK, @TableName_PK, @IndexName_PK, @is_unique_constraint_PK, @is_primary_key_PK, @IndexTypeDesc_PK, @IndexOptions_PK, @fill_factor_PK, @FileGroupName_PK
END
CLOSE CursorCreateIndex_PK
DEALLOCATE CursorCreateIndex_PK
-----------------------------------------------------------------------------------------------------
/********** SCRIPT TO GENERATE THE DROP SCRIPT OF ALL FOREIGN KEY CONSTRAINTS. **********/
DECLARE @ForeignKeyName_FK VARCHAR(4000)
DECLARE @ParentTableName_FK VARCHAR(4000)
DECLARE @ParentTableSchema_FK VARCHAR(4000)
DECLARE @ForeignKeyID_FK INT
DECLARE @ParentColumn_FK VARCHAR(4000)
DECLARE @ReferencedTable_FK VARCHAR(4000)
DECLARE @ReferencedColumn_FK VARCHAR(4000)
DECLARE @StrParentColumn_FK VARCHAR(MAX)
DECLARE @StrReferencedColumn_FK VARCHAR(MAX)
DECLARE @ReferencedTableSchema_FK VARCHAR(4000)
DECLARE @TSQLScripCreationForeignKey_FK VARCHAR(MAX)
DECLARE @TSQLScripDropForeignKey_FK VARCHAR(MAX)
DECLARE CursorFK CURSOR FOR
SELECT fk.name ForeignKeyName, schema_name(t.schema_id) ParentTableSchema, t.name ParentTableName
FROM sys.foreign_keys fk
INNER JOIN sys.tables t
ON fk.parent_object_id=t.object_id
-- Применение фильтров для таблиц
WHERE (@IncludeTables = 0 OR t.name IN (SELECT value FROM STRING_SPLIT(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeTableList, '','')))
OPEN CursorFK
FETCH NEXT FROM CursorFK INTO @ForeignKeyName_FK, @ParentTableSchema_FK, @ParentTableName_FK
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @TSQLScripDropForeignKey_FK =''ALTER TABLE ''+quotename(@ParentTableSchema_FK)+''.''+quotename(@ParentTableName_FK)+'' DROP CONSTRAINT ''+quotename(@ForeignKeyName_FK)
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, ConstraintType, QueryIndex, Command)
VALUES(''' + @DBName + N''', @ParentTableName_FK, @ParentTableSchema_FK, @ForeignKeyName_FK, ''FOREIGN KEY'', @TSQLScripDropForeignKey_FK, ''DROP'')
FETCH NEXT FROM CursorFK INTO @ForeignKeyName_FK, @ParentTableSchema_FK, @ParentTableName_FK
END
CLOSE CursorFK
DEALLOCATE CursorFK
/********** SCRIPT TO GENERATE THE CREATION SCRIPT OF ALL FOREIGN KEY CONSTRAINTS. **********/
DECLARE CursorFK_Create CURSOR FOR
SELECT fk.object_id
FROM sys.foreign_keys fk
INNER JOIN sys.tables t ON fk.parent_object_id = t.object_id
-- Применение фильтров для таблиц
WHERE (@IncludeTables = 0 OR t.name IN (SELECT value FROM STRING_SPLIT(@IncludeTableList, '','')))
AND (@ExcludeTables = 0 OR t.name NOT IN (SELECT value FROM STRING_SPLIT(@ExcludeTableList, '','')))
OPEN CursorFK_Create
FETCH NEXT FROM CursorFK_Create INTO @ForeignKeyID_FK
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @StrParentColumn_FK=''''
SET @StrReferencedColumn_FK=''''
DECLARE CursorFKDetails CURSOR FOR
SELECT fk.name ForeignKeyName, schema_name(t1.schema_id) ParentTableSchema,
object_name(fkc.parent_object_id) ParentTable, c1.name ParentColumn,schema_name(t2.schema_id) ReferencedTableSchema,
object_name(fkc.referenced_object_id) ReferencedTable,c2.name ReferencedColumn
FROM sys.foreign_keys fk
INNER JOIN sys.foreign_key_columns fkc ON fk.object_id=fkc.constraint_object_id
INNER JOIN sys.columns c1 ON c1.object_id=fkc.parent_object_id AND c1.column_id=fkc.parent_column_id
INNER JOIN sys.columns c2 ON c2.object_id=fkc.referenced_object_id AND c2.column_id=fkc.referenced_column_id
INNER JOIN sys.tables t1 ON t1.object_id=fkc.parent_object_id
INNER JOIN sys.tables t2 ON t2.object_id=fkc.referenced_object_id
WHERE fk.object_id=@ForeignKeyID_FK
OPEN CursorFKDetails
FETCH NEXT FROM CursorFKDetails INTO @ForeignKeyName_FK, @ParentTableSchema_FK, @ParentTableName_FK, @ParentColumn_FK, @ReferencedTableSchema_FK, @ReferencedTable_FK, @ReferencedColumn_FK
WHILE (@@FETCH_STATUS=0)
BEGIN
SET @StrParentColumn_FK=@StrParentColumn_FK + '', '' + quotename(@ParentColumn_FK)
SET @StrReferencedColumn_FK=@StrReferencedColumn_FK + '', '' + quotename(@ReferencedColumn_FK)
FETCH NEXT FROM CursorFKDetails INTO @ForeignKeyName_FK, @ParentTableSchema_FK, @ParentTableName_FK, @ParentColumn_FK, @ReferencedTableSchema_FK, @ReferencedTable_FK, @ReferencedColumn_FK
END
CLOSE CursorFKDetails
DEALLOCATE CursorFKDetails
SET @StrParentColumn_FK=SUBSTRING(@StrParentColumn_FK,2,LEN(@StrParentColumn_FK)-1)
SET @StrReferencedColumn_FK=SUBSTRING(@StrReferencedColumn_FK,2,LEN(@StrReferencedColumn_FK)-1)
SET @TSQLScripCreationForeignKey_FK=''ALTER TABLE ''+quotename(@ParentTableSchema_FK)+''.''+quotename(@ParentTableName_FK)+'' WITH CHECK ADD CONSTRAINT ''+quotename(@ForeignKeyName_FK)
+ '' FOREIGN KEY(''+ltrim(@StrParentColumn_FK)+'') ''+ CHAR(13) +''REFERENCES ''+quotename(@ReferencedTableSchema_FK)+''.''+quotename(@ReferencedTable_FK)+'' (''+ltrim(@StrReferencedColumn_FK)+'') ''
INSERT INTO #TableIndex
(DatabaseName, TableName, SchemaName, IndexName, ConstraintType, QueryIndex, Command)
VALUES(''' + @DBName + N''', @ParentTableName_FK, @ParentTableSchema_FK, @ForeignKeyName_FK, ''FOREIGN KEY'', @TSQLScripCreationForeignKey_FK, ''CREATE'')
FETCH NEXT FROM CursorFK_Create INTO @ForeignKeyID_FK
END
CLOSE CursorFK_Create
DEALLOCATE CursorFK_Create
'
EXEC sp_executesql @query;
FETCH NEXT FROM CursorDatabase INTO @DBName
END
CLOSE CursorDatabase
DEALLOCATE CursorDatabase;
-- Определяем список зависимых таблиц по foreign key
IF (OBJECT_ID('tempdb..#ReferencingTableNameList') IS NOT NULL) BEGIN DROP TABLE #ReferencingTableNameList; END;
WITH TableIndex AS (
SELECT DISTINCT DatabaseName, TableName, SchemaName FROM #TableIndex
),
TableIndexFK AS (
SELECT ti.*, fk.ReferencingTableName, fk.ReferencingSchemaName
FROM TableIndex ti
LEFT JOIN #ForeignKey fk
ON ti.DatabaseName = fk.DatabaseName
AND ti.TableName = fk.ReferencedTableName
AND ti.SchemaName = fk.ReferencedSchemaName
)
SELECT DatabaseName, SchemaName, TableName, STRING_AGG(ReferencingTableName, ';') AS ReferencingTableNameList
INTO #ReferencingTableNameList
FROM TableIndexFK
GROUP BY DatabaseName, SchemaName, TableName
ORDER BY TableName;
-- Выставляем порядок таблиц в зависимости от foreign key
IF (OBJECT_ID('tempdb..#TableOrder') IS NOT NULL) BEGIN DROP TABLE #TableOrder; END;
WITH TableIndex AS (
SELECT DISTINCT DatabaseName, TableName, SchemaName FROM #TableIndex
),
TableIndexFK AS (
SELECT ti.*, fk.ReferencedTableName, fk.ReferencedSchemaName
FROM TableIndex ti
LEFT JOIN #ForeignKey fk
ON ti.DatabaseName = fk.DatabaseName
AND ti.TableName = fk.ReferencingTableName
AND ti.SchemaName = fk.ReferencedSchemaName
),
Recursion AS (
SELECT ti.*, CAST(ISNULL(ti.ReferencedTableName, '') AS VARCHAR(MAX)) AS List, 0 AS Rnk
FROM TableIndexFK ti
WHERE ti.ReferencedTableName IS NULL
UNION ALL
SELECT ti.*, r.List + '/' + CONVERT(VARCHAR(MAX), ISNULL(ti.ReferencedTableName, '')) AS List, r.Rnk + 1 AS Rnk
FROM TableIndexFK ti
INNER JOIN Recursion r
ON ti.DatabaseName = r.DatabaseName
AND ti.TableName = r.ReferencedTableName
AND ti.SchemaName = r.ReferencedSchemaName
)
SELECT DatabaseName, SchemaName, TableName, MAX(Rnk) AS Rnk
INTO #TableOrder
FROM Recursion
GROUP BY DatabaseName, SchemaName, TableName
OPTION(MAXRECURSION 0);
-- Обновляем ранг в основной таблице
UPDATE ti
SET ti.Rnk = ISNULL(tblOrder.Rnk, 0)
FROM #TableIndex ti
LEFT JOIN #TableOrder AS tblOrder
ON ti.DatabaseName = tblOrder.DatabaseName
AND ti.TableName = tblOrder.TableName
AND ti.SchemaName = tblOrder.SchemaName;
-- Фильтрация индексов для пересоздания в зависимости от параметров
UPDATE #TableIndex
SET QueryIndex = NULL
WHERE
-- Не пересоздавать, если не безусловное пересоздание и FG совпадает и FillFactor не задан или равен 80
(@RecreateUnconditionally = 0
AND FileGroupName = @model_FileGroupName
AND ([FillFactor] IS NULL OR [FillFactor] = 0 OR [FillFactor] = 80))
-- Исключаем команды с NULL QueryIndex
AND QueryIndex IS NOT NULL;
-- Генерация итогового скрипта
DECLARE @TableInfo_DatabaseName VARCHAR(300)
,@TableInfo_SchemaName VARCHAR(300)
,@TableInfo_TableName VARCHAR(300)
,@TableInfo_QueryDropIndex VARCHAR(MAX)
,@TableInfo_QueryCreateIndex VARCHAR(MAX)
,@TableInfo_QueryAlterIndex VARCHAR(MAX)
,@TableInfo_QueryDropForeignKey VARCHAR(MAX)
,@TableInfo_QueryCreateForeignKey VARCHAR(MAX)
,@Rnk INT;
PRINT 'SET NOCOUNT ON;' + CHAR(13);
DECLARE CursorTableInfo CURSOR FOR
SELECT DISTINCT ti.DatabaseName
,ti.SchemaName
,ti.TableName
,ti.Rnk
,tidx.QueryDropIndex
,tidx.QueryCreateIndex
,tidx.QueryAlterIndex
,tidx.QueryDropForeignKey
,tidx.QueryCreateForeignKey
FROM #TableIndex ti
LEFT JOIN (
SELECT DatabaseName
,SchemaName
,TableName
--QueryDropIndex
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN Command = 'DROP' AND (ConstraintType <> 'FOREIGN KEY' OR ConstraintType IS NULL) AND QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''IndexName = ' + IndexName + ''';' + CHAR(13) +
'PRINT ''Command = DROP'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + DatabaseName + ''',''' + SchemaName +''',''' + TableName +''',''' + IndexName + ''',''' + REPLACE(QueryIndex, '''', '''''''') + ''',''ALTER'', ''DropIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
CHAR(9) + QueryIndex + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultDropIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ';'
END
FROM #TableIndex AS t1 WITH(NOLOCK)
WHERE 1=1
AND t.DatabaseName = t1.DatabaseName AND t.SchemaName = t1.SchemaName AND t.TableName = t1.TableName
AND t1.QueryIndex IS NOT NULL
ORDER BY CASE WHEN t1.IndexType = 'CLUSTERED' THEN 0 ELSE 1 END DESC, t1.IndexName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryDropIndex
--QueryCreateIndex
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN Command = 'CREATE' AND (ConstraintType <> 'FOREIGN KEY' OR ConstraintType IS NULL) AND QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''IndexName = ' + IndexName + ''';' + CHAR(13) +
'PRINT ''Command = CREATE'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + DatabaseName + ''',''' + SchemaName +''',''' + TableName +''',''' + IndexName + ''',''' + REPLACE(QueryIndex, '''', '''''''') + ''',''ALTER'', ''CreateIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
'IF NOT EXISTS (SELECT * FROM sys.indexes WHERE name = ''' + IndexName + ''' AND object_id = OBJECT_ID(''' + SchemaName + '.' + TableName + '''))' + CHAR(13) +
'BEGIN' + CHAR(13) +
QueryIndex + CHAR(13) +
'END' + CHAR(13) +
'ELSE' + CHAR(13) +
'BEGIN' + CHAR(13) +
'PRINT ''Index ' + IndexName + ' already exists, skipping...'';' + CHAR(13) +
'END' + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultCreateIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ';'
END
FROM #TableIndex AS t1 WITH(NOLOCK)
WHERE 1=1
AND t.DatabaseName = t1.DatabaseName AND t.SchemaName = t1.SchemaName AND t.TableName = t1.TableName
AND t1.QueryIndex IS NOT NULL
ORDER BY CASE WHEN t1.IndexType = 'CLUSTERED' THEN 1 ELSE 0 END DESC, t1.IndexName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryCreateIndex
--QueryAlterIndex
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN Command = 'ALTER' AND QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''IndexName = ' + IndexName + ''';' + CHAR(13) +
'PRINT ''Command = ALTER'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + DatabaseName + ''',''' + SchemaName +''',''' + TableName +''',''' + IndexName + ''',''' + REPLACE(QueryIndex, '''', '''''''') + ''',''ALTER'', ''AlterIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
QueryIndex + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultAlterIndex__' + CAST(t1.RowNum AS VARCHAR(1000)) + ';'
END
FROM #TableIndex AS t1 WITH(NOLOCK)
WHERE 1=1
AND t.DatabaseName = t1.DatabaseName AND t.SchemaName = t1.SchemaName AND t.TableName = t1.TableName
AND t1.QueryIndex IS NOT NULL
ORDER BY IndexName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryAlterIndex
--QueryDropForeignKey
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN t_ti.Command = 'DROP' AND t_ti.ConstraintType = 'FOREIGN KEY' AND t_ti.QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + t_ti.IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''ForeignKeyName = ' + t_ti.IndexName + ''';' + CHAR(13) +
'PRINT ''Command = DROP'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(t_ti.QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + t_ti.DatabaseName + ''',''' + t_ti.SchemaName +''',''' + t_ti.TableName +''',''' + t_ti.IndexName + ''',''' + REPLACE(t_ti.QueryIndex, '''', '''''''') + ''',''ALTER'', ''DropIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
CHAR(9) + t_ti.QueryIndex + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultDropIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ';'
END
FROM (
SELECT rt.DatabaseName, rt.SchemaName, oa.value AS ReferencingTableName, rt.TableName
FROM #ReferencingTableNameList AS rt
OUTER APPLY (SELECT * FROM STRING_SPLIT(rt.ReferencingTableNameList, ';')) AS oa
WHERE oa.value IS NOT NULL
) AS rt
INNER JOIN #TableIndex AS t_ti
ON rt.DatabaseName = t_ti.DatabaseName AND rt.SchemaName = t_ti.SchemaName AND rt.ReferencingTableName = t_ti.TableName
WHERE 1=1
AND t.DatabaseName = t_ti.DatabaseName AND t.SchemaName = t_ti.SchemaName AND t.TableName = rt.TableName
AND t_ti.QueryIndex IS NOT NULL
ORDER BY rt.TableName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryDropForeignKey
--QueryCreateForeignKey
,STUFF(
(
SELECT CHAR(13) +
CASE
WHEN t_ti.Command = 'CREATE' AND t_ti.ConstraintType = 'FOREIGN KEY' AND t_ti.QueryIndex IS NOT NULL
THEN '--' + CHAR(13) +
'-- ' + t_ti.IndexName + CHAR(13) +
'--' + CHAR(13) +
'PRINT ''--'';' + CHAR(13) +
'PRINT ''IndexName = ' + t_ti.IndexName + ''';' + CHAR(13) +
'PRINT ''Command = CREATE'';' + CHAR(13) +
'PRINT ''Statement = ' + REPLACE(REPLACE(t_ti.QueryIndex, '''', ''''''''), CHAR(13), ' ') + ''';' + CHAR(13) +
'DECLARE @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' VARCHAR(50) = '''';' + CHAR(13) +
'DECLARE @ErrorNumberCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' INT = 0;' + CHAR(13) +
'DECLARE @ErrorMessageCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' VARCHAR(MAX) = '''';' + CHAR(13) +
'DECLARE @TblCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' TABLE(Id INT NOT NULL);' + CHAR(13) +
CHAR(13) +
'INSERT INTO [DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'([DatabaseName], [SchemaName], [TableName], [IndexName], [Command], [CommandType], [CommandSubType], [StartTime])' + CHAR(13) +
'OUTPUT INSERTED.Id INTO @TblCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
'VALUES(''' + t_ti.DatabaseName + ''',''' + t_ti.SchemaName +''',''' + t_ti.TableName +''',''' + t_ti.IndexName + ''',''' + REPLACE(t_ti.QueryIndex, '''', '''''''') + ''',''ALTER'', ''CreateIndex'', GETDATE());' + CHAR(13) +
CHAR(13) +
'BEGIN TRY' + CHAR(13) +
'IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE name = ''' + t_ti.IndexName + ''' AND parent_object_id = OBJECT_ID(''' + t_ti.SchemaName + '.' + t_ti.TableName + '''))' + CHAR(13) +
'BEGIN' + CHAR(13) +
t_ti.QueryIndex + CHAR(13) +
'END' + CHAR(13) +
'ELSE' + CHAR(13) +
'BEGIN' + CHAR(13) +
'PRINT ''Foreign Key ' + t_ti.IndexName + ' already exists, skipping...'';' + CHAR(13) +
'END' + CHAR(13) +
CHAR(13) +
CHAR(9) + 'SET @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = ''Success'';' + CHAR(13) +
'END TRY' + CHAR(13) +
'BEGIN CATCH' + CHAR(13) +
CHAR(9) + 'SET @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = ''Fail'';' + CHAR(13) +
CHAR(9) + 'SET @ErrorNumberCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_NUMBER());' + CHAR(13) +
CHAR(9) + 'SET @ErrorMessageCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ' = (SELECT ERROR_MESSAGE());' + CHAR(13) +
'END CATCH;' + CHAR(13) +
CHAR(13) +
'UPDATE' + CHAR(13) +
'[DBATools].[dbo].[CheckProjectTables_log]' + CHAR(13) +
'SET StopTime = GETDATE()' + CHAR(13) +
CHAR(9) + ',Result = @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorNumber = @ErrorNumberCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
CHAR(9) + ',ErrorMessage = @ErrorMessageCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + CHAR(13) +
'WHERE Id = (SELECT Id FROM @TblCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ');' + CHAR(13) +
CHAR(13) +
'PRINT ''Result = '' + @ResultCreateIndex__' + CAST(t_ti.RowNum AS VARCHAR(1000)) + ';'
END
FROM (
SELECT rt.DatabaseName, rt.SchemaName, oa.value AS ReferencingTableName, rt.TableName
FROM #ReferencingTableNameList AS rt
OUTER APPLY (SELECT * FROM STRING_SPLIT(rt.ReferencingTableNameList, ';')) AS oa
WHERE oa.value IS NOT NULL
) AS rt
INNER JOIN #TableIndex AS t_ti
ON rt.DatabaseName = t_ti.DatabaseName AND rt.SchemaName = t_ti.SchemaName AND rt.ReferencingTableName = t_ti.TableName
WHERE 1=1
AND t.DatabaseName = t_ti.DatabaseName AND t.SchemaName = t_ti.SchemaName AND t.TableName = rt.TableName
AND t_ti.QueryIndex IS NOT NULL
ORDER BY rt.TableName
FOR XML PATH (''),TYPE).value('.[1]','NVARCHAR(MAX)')
, 1, 1, ''
) AS QueryCreateForeignKey
FROM #TableIndex AS t
WHERE t.QueryIndex IS NOT NULL
GROUP BY DatabaseName, SchemaName, TableName
) AS tidx
ON ti.DatabaseName = tidx.DatabaseName AND ti.SchemaName = tidx.SchemaName AND ti.TableName = tidx.TableName
WHERE ti.QueryIndex IS NOT NULL
ORDER BY ti.DatabaseName, ti.SchemaName, ti.Rnk, ti.TableName
OPEN CursorTableInfo
FETCH NEXT FROM CursorTableInfo INTO @TableInfo_DatabaseName, @TableInfo_SchemaName, @TableInfo_TableName, @Rnk, @TableInfo_QueryDropIndex, @TableInfo_QueryCreateIndex, @TableInfo_QueryAlterIndex, @TableInfo_QueryDropForeignKey, @TableInfo_QueryCreateForeignKey
WHILE (@@FETCH_STATUS = 0)
BEGIN
PRINT '----------------------------------------------------------------------------------------------------------';
PRINT 'USE ' + @TableInfo_DatabaseName + ';';
PRINT '--';
PRINT '-- DataBaseName = ' + @TableInfo_DatabaseName;
PRINT '-- SchemaName = ' + @TableInfo_SchemaName;
PRINT '-- TableName = ' + @TableInfo_TableName;
PRINT '--';
PRINT 'PRINT ''----------------------------------------------------------------------------------------------------------''';
PRINT 'PRINT ''--'';';
PRINT 'PRINT ''DataBaseName = ' + @TableInfo_DatabaseName + ''';';
PRINT 'PRINT ''SchemaName = ' + @TableInfo_SchemaName + ''';';
PRINT 'PRINT ''TableName = ' + @TableInfo_TableName + ''';';
IF(@TableInfo_QueryDropForeignKey <> '')
BEGIN
PRINT '--';
PRINT '-- DROP FOREIGN KEY';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryDropForeignKey;
END;
IF(@TableInfo_QueryDropIndex <> '')
BEGIN
PRINT '--';
PRINT '-- DROP INDEX';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryDropIndex;
END;
IF(@TableInfo_QueryCreateIndex <> '')
BEGIN
PRINT '--';
PRINT '-- CREATE INDEX';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryCreateIndex;
END;
IF(@TableInfo_QueryAlterIndex <> '')
BEGIN
PRINT '--';
PRINT '-- ALTER INDEX';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryAlterIndex;
END;
IF(@TableInfo_QueryCreateForeignKey <> '')
BEGIN
PRINT '--';
PRINT '-- CREATE FOREIGN KEY';
EXEC [statdb].[dbo].[LongPrint] @String = @TableInfo_QueryCreateForeignKey;
END;
PRINT '--';
PRINT 'GO'
PRINT 'PRINT ''--'';';
FETCH NEXT FROM CursorTableInfo INTO @TableInfo_DatabaseName, @TableInfo_SchemaName, @TableInfo_TableName, @Rnk, @TableInfo_QueryDropIndex, @TableInfo_QueryCreateIndex, @TableInfo_QueryAlterIndex, @TableInfo_QueryDropForeignKey, @TableInfo_QueryCreateForeignKey
END
CLOSE CursorTableInfo
DEALLOCATE CursorTableInfo;
/*
-- После формирования #TableIndex и фильтрации
SELECT
DatabaseName,
SchemaName,
TableName,
IndexName,
Command, -- CREATE / DROP / ALTER
QueryIndex
FROM #TableIndex
WHERE QueryIndex IS NOT NULL
ORDER BY DatabaseName, SchemaName, TableName, Command DESC;
*/
Проверить индесы в какой файловой группе
--To determine what object is on which FG SELECT o.[name] AS TableName, i.[name] AS IndexName, fg.[name] AS FileGroupName FROM sys.indexes i INNER JOIN sys.filegroups fg ON i.data_space_id = fg.data_space_id INNER JOIN sys.all_objects o ON i.[object_id] = o.[object_id] WHERE i.data_space_id = fg.data_space_id AND o.type = 'U'
Similar Posts:
- Как создать хранимую процедуру для реиндекса таблиц mssql (reindex)
- Как сделать скрипт для бэкапа mssql что бы следить что фуул лежит в том же месте что и новое место.
- Как в Mssql сделать новый файл файловой группы на новом диске H .
- Как сформировать скрипт для реиндекса индексов mssql. Необходимо для каждого индекса с генерировать соответствующую ALTER INDEX команду
- Сбор статистики по заполнению фрагментации индекса на отдельных таблицах