반응형
(1) 해당 DB의 모든 테이블 SELECT, SELECT COUNT(*), DROP, TRUNCATE, DELETE 쿼리 만들기
| DECLARE @v_SQL NVARCHAR(MAX) DECLARE @v_Params NVARCHAR(1000) DECLARE @v_Process NVARCHAR(50) DECLARE @v_DBName SYSNAME SET @v_Process = N'DELETE FROM' -- 'SELECT * FROM', 'SELECT COUNT(*) FROM', 'DROP TABLE', 'TRUNCATE TABLE', 'DELETE FROM' SET @v_DBName = DB_NAME() SET @v_SQL = N'USE ' + @v_DBName + CHAR(10) SET @v_SQL = @v_SQL + N'SELECT @v_InProcess + '' '' + @v_InDBName + ''.'' + a.f_SchemaName + ''.'' + a.f_ObjectName FROM ( SELECT object_id AS f_ObjectID, SCHEMA_NAME(schema_id) AS f_SchemaName, (CASE type WHEN ''V'' THEN ''VIEW'' WHEN ''U'' THEN ''TABLE'' END) AS f_ObjectType, name AS f_ObjectName, CONVERT(VARCHAR(23), create_date, 121) AS f_CrDate, CONVERT(VARCHAR(23), modify_date, 121) AS f_ModDate FROM sys.objects WHERE type IN(''U'', ''V'') ) AS a LEFT OUTER JOIN sys.extended_properties AS p ON a.f_ObjectID = p.major_id AND p.minor_id = 0 AND p.name = ''MS_Description'' ORDER BY a.f_SchemaName ASC, a.f_ObjectType ASC, a.f_ObjectName ASC' SET @v_Params = N'@v_InProcess NVARCHAR(50), @v_InDBName SYSNAME' EXECUTE SP_EXECUTESQL @v_SQL, @v_Params, @v_InProcess = @v_Process, @v_InDBName = @v_DBName |
(2) 모든 테이블의 SELECT 필드 쿼리 만들기
| DECLARE @v_DBName SYSNAME SET @v_DBName = DB_NAME() SELECT ('SELECT ' + a.COLUMNNAMES + ' FROM ' + @v_DBName + '.' + a.SCHEMANAME + '.' + a.TABLENAME) AS Query FROM ( SELECT SCHEMANAME, TABLENAME, STUFF(( SELECT ', ' + [name] FROM sys.columns WHERE object_id = t.object_id ORDER BY column_id ASC FOR XML PATH('') ), 1, 2, '') AS COLUMNNAMES FROM ( SELECT object_id, SCHEMA_NAME(schema_id) AS SCHEMANAME, [name] AS TABLENAME FROM sys.objects WHERE type IN('U', 'V') ) AS t GROUP BY t.object_id, t.SCHEMANAME, t.TABLENAME ) AS a |
(3) 최근에 수정된 저장프로시저 ALTER PROCEDURE 쿼리 만들기
| DECLARE @v_SQL NVARCHAR(MAX) DECLARE @v_ExecParam NVARCHAR(200) DECLARE @v_CreateDate NCHAR(10) DECLARE @v_ModifyDate NCHAR(10) DECLARE @v_ObjectName NVARCHAR(50) DECLARE @v_TblSPContent TABLE( SPContent NVARCHAR(MAX) ) SET @v_CreateDate = CONVERT(CHAR(10), GETDATE() - 1, 23) SET @v_ModifyDate = CONVERT(CHAR(10), GETDATE() - 1, 23) DECLARE cur CURSOR FAST_FORWARD FOR -- 저장프로시저 명세 SELECT name FROM sys.procedures WHERE create_date >= @v_CreateDate OR modify_date >= @v_ModifyDate ORDER BY modify_date DESC OPEN cur FETCH NEXT FROM cur INTO @v_ObjectName WHILE @@FETCH_STATUS = 0 BEGIN -- 해당 저장프로시져 내용 SET @v_SQL = 'sp_helptext @v_InObjectName' SET @v_ExecParam = '@v_InObjectName VARCHAR(100)' INSERT @v_TblSPContent(SPContent) EXEC SP_EXECUTESQL @v_SQL, @v_ExecParam, @v_InObjectName = @v_ObjectName FETCH NEXT FROM cur INTO @v_ObjectName END CLOSE cur DEALLOCATE cur SELECT REPLACE(SPContent, 'CREATE PROCEDURE', 'ALTER PROCEDURE') FROM @v_TblSPContent |
(4) 모든 인덱스 삭제 쿼리 만들기
| DECLARE @v_TblSPContent TABLE( SQLContent NVARCHAR(MAX) ) DECLARE @v_SchemaName NVARCHAR(256) DECLARE @v_TableName NVARCHAR(256) DECLARE @v_IndexName NVARCHAR(256) DECLARE @v_IsPrimaryKey TINYINT DECLARE @v_SQL VARCHAR(MAX) DECLARE Cur CURSOR FOR SELECT SCHEMA_NAME(t.schema_id), t.name, ind.name, ind.is_primary_key FROM sys.indexes AS ind INNER JOIN sys.tables AS t ON t.object_id = ind.object_id WHERE ind.[type] > 0 AND t.is_ms_shipped = 0 AND t.name <> 'sysdiagrams' ORDER BY SCHEMA_NAME(t.schema_id) ASC, t.name ASC OPEN Cur FETCH NEXT FROM Cur INTO @v_SchemaName, @v_TableName, @v_IndexName, @v_IsPrimaryKey WHILE @@FETCH_STATUS = 0 BEGIN IF @v_IsPrimaryKey = 1 SET @v_SQL = 'ALTER TABLE ' + QUOTENAME(@v_SchemaName) + '.' + QUOTENAME(@v_TableName) + ' DROP CONSTRAINT ' + QUOTENAME(@v_IndexName) ELSE SET @v_SQL = 'DROP INDEX ' + QUOTENAME(@v_SchemaName) + '.' + QUOTENAME(@v_TableName) + '.' + QUOTENAME(@v_IndexName) INSERT @v_TblSPContent(SQLContent) SELECT @v_SQL FETCH NEXT FROM Cur INTO @v_SchemaName, @v_TableName, @v_IndexName, @v_IsPrimaryKey END CLOSE Cur DEALLOCATE Cur SELECT SQLContent FROM @v_TblSPContent |
(5) 모든 인덱스 생성 쿼리 만들기
| DECLARE @v_SchemaName NVARCHAR(256) DECLARE @v_TableName NVARCHAR(256) DECLARE @v_IndexName NVARCHAR(256) DECLARE @v_ColumnName NVARCHAR(100) DECLARE @v_IsPrimaryKey TINYINT DECLARE @v_IsUnique NVARCHAR(100) DECLARE @v_IndexTypeDesc NVARCHAR(100) DECLARE @v_FileGroupName NVARCHAR(100) DECLARE @v_IsDisabled NVARCHAR(100) DECLARE @v_IndexOptions NVARCHAR(MAX) DECLARE @v_IndexColumnId TINYINT DECLARE @v_IsDescendingKey TINYINT DECLARE @v_IsIncludedColumn TINYINT DECLARE @v_TSQLScripCreationIndex NVARCHAR(MAX) DECLARE @v_TSQLScripDisableIndex NVARCHAR(MAX) DECLARE Cur01 CURSOR FOR SELECT SCHEMA_NAME(t.schema_id) AS [SCHEMA_NAME], t.name, ind.name, ind.is_primary_key, CASE WHEN ind.is_unique = 1 THEN 'UNIQUE ' ELSE '' END, ind.type_desc, (CASE WHEN ind.is_padded = 1 THEN 'PAD_INDEX = ON, ' ELSE 'PAD_INDEX = OFF, ' END + CASE WHEN INDEXPROPERTY(t.object_id, ind.name, 'IsStatistics') = 1 THEN 'STATISTICS_NORECOMPUTE = ON, ' ELSE 'STATISTICS_NORECOMPUTE = OFF, ' END + 'SORT_IN_TEMPDB = OFF, ' + CASE WHEN ind.ignore_dup_key = 1 THEN 'IGNORE_DUP_KEY = ON, ' ELSE 'IGNORE_DUP_KEY = OFF, ' END + CASE WHEN ind.allow_row_locks = 1 THEN 'ALLOW_ROW_LOCKS = ON, ' ELSE 'ALLOW_ROW_LOCKS = OFF, ' END + CASE WHEN ind.allow_page_locks = 1 THEN 'ALLOW_PAGE_LOCKS = ON' ELSE 'ALLOW_PAGE_LOCKS = OFF' END + CASE WHEN ind.fill_factor > 0 THEN ', FILLFACTOR = ' + CAST(ind.fill_factor AS NVARCHAR(3)) ELSE '' END ) AS IndexOptions, ind.is_disabled, FILEGROUP_NAME(ind.data_space_id) FileGroupName FROM sys.tables AS t INNER JOIN sys.indexes AS ind ON t.object_id = ind.object_id WHERE ind.[type] > 0 AND t.is_ms_shipped = 0 AND t.name <> 'sysdiagrams' ORDER BY SCHEMA_NAME(t.schema_id) ASC, t.name ASC, ind.name ASC OPEN Cur01 FETCH NEXT FROM Cur01 INTO @v_SchemaName, @v_TableName, @v_IndexName, @v_IsPrimaryKey, @v_IsUnique, @v_IndexTypeDesc, @v_IndexOptions, @v_IsDisabled, @v_FileGroupName WHILE @@FETCH_STATUS = 0 BEGIN DECLARE @v_IndexColumns NVARCHAR(MAX) DECLARE @v_IncludedColumns NVARCHAR(MAX) SET @v_IndexColumns = '' SET @v_IncludedColumns = '' DECLARE Cur02 CURSOR FOR SELECT col.name, indcol.is_descending_key, indcol.is_included_column FROM sys.tables AS t INNER JOIN sys.indexes AS ind ON t.object_id = ind.object_id INNER JOIN sys.index_columns AS indcol ON ind.object_id = indcol.object_id AND ind.index_id = indcol.index_id INNER JOIN sys.columns col ON indcol.object_id = col.object_id AND indcol.column_id = col.column_id WHERE ind.[type] > 0 AND (ind.is_primary_key = 0 OR ind.is_unique_constraint = 0) AND SCHEMA_NAME(t.schema_id) = @v_SchemaName AND t.name = @v_TableName AND ind.name = @v_IndexName ORDER BY indcol.index_column_id OPEN Cur02 FETCH NEXT FROM Cur02 INTO @v_ColumnName, @v_IsDescendingKey, @v_IsIncludedColumn WHILE @@FETCH_STATUS = 0 BEGIN IF @v_IsIncludedColumn = 0 SET @v_IndexColumns = @v_IndexColumns + @v_ColumnName + CASE WHEN @v_IsDescendingKey = 1 THEN ' DESC, ' ELSE ' ASC, ' END ELSE SET @v_IncludedColumns = @v_IncludedColumns + @v_ColumnName + ', ' FETCH NEXT FROM Cur02 INTO @v_ColumnName, @v_IsDescendingKey, @v_IsIncludedColumn END CLOSE Cur02 DEALLOCATE Cur02 SET @v_IndexColumns = SUBSTRING(@v_IndexColumns, 1, LEN(@v_IndexColumns) - 1) SET @v_IncludedColumns = CASE WHEN LEN(@v_IncludedColumns) > 0 THEN SUBSTRING(@v_IncludedColumns, 1, LEN(@v_IncludedColumns) - 1) ELSE '' END SET @v_TSQLScripCreationIndex = '' SET @v_TSQLScripDisableIndex = '' IF @v_IsPrimaryKey = 1 BEGIN SET @v_TSQLScripCreationIndex = 'ALTER TABLE ' + QUOTENAME(@v_SchemaName) + '.' + QUOTENAME(@v_TableName) + ' ADD CONSTRAINT ' + QUOTENAME(@v_IndexName) + ' PRIMARY KEY' + CHAR(13) + '(' + CHAR(13) + CHAR(9) + @v_IndexColumns + CHAR(13) + ')' + CASE WHEN LEN(@v_IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @v_IncludedColumns + ')' ELSE '' END + ' WITH (' + @v_IndexOptions + ') ON ' + QUOTENAME(@v_FileGroupName) END ELSE BEGIN SET @v_TSQLScripCreationIndex = 'CREATE ' + @v_IsUnique + @v_IndexTypeDesc + ' INDEX ' + QUOTENAME(@v_IndexName) + ' ON ' + QUOTENAME(@v_SchemaName) + '.' + QUOTENAME(@v_TableName) + CHAR(13) + '(' + CHAR(13) + CHAR(9) + @v_IndexColumns + CHAR(13) + ')' + CASE WHEN LEN(@v_IncludedColumns) > 0 THEN CHAR(13) + 'INCLUDE (' + @v_IncludedColumns + ')' ELSE '' END + ' WITH (' + @v_IndexOptions + ') ON ' + QUOTENAME(@v_FileGroupName) END IF @v_IsDisabled = 1 SET @v_TSQLScripDisableIndex = CHAR(13) + 'ALTER INDEX ' + QUOTENAME(@v_IndexName) + ' ON ' + QUOTENAME(@v_SchemaName) + '.' + QUOTENAME(@v_TableName) + ' DISABLE' + CHAR(13) PRINT @v_TSQLScripCreationIndex PRINT @v_TSQLScripDisableIndex FETCH NEXT FROM Cur01 INTO @v_SchemaName, @v_TableName, @v_IndexName, @v_IsPrimaryKey, @v_IsUnique, @v_IndexTypeDesc, @v_IndexOptions,@v_IsDisabled, @v_FileGroupName END CLOSE Cur01 DEALLOCATE Cur01 |
반응형