Para realizar este desafio, criei o seguinte script:
/*
****************************************************************************************************************************************
Script Name: DB_Truncate_Automatic.sql
Client's Name:
Create Date: 16/10/2018 - 08:00
Created By: Bruno Bragatto - DBA
Objective: Automatically truncate every table in a specific Database
----------------------------------------------------------------------------------------------------------------------------------------
Modification Log
Date: 19/10/2018
Modified By: Bruno Bragatto
Modification Details: User's Variables Defined. Also defined a best method to increase the performance
Initial Created by: Bruno Bragatto
****************************************************************************************************************************************
--References: https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/
https://www.mssqltips.com/sqlservertip/1460/sql-server-script-to-create-windows-directories/
https://www.mssqltips.com/sqlservertip/2537/sql-server-row-count-for-all-tables-in-a-database/
INSTRUCTIONS!
FIRST OF ALL: BE SURE OF WHAT ARE YOU DOING!
1- It's necessary to specify the Database Name. DOUBLE CHECK IT!!
2- You MUST change the @Truncate_Ready only when you're really ready to do the truncate. It's just change from 0 to 1.
2.1- If possible, execute it for the first time with the @Truncate_Ready variable set as 0 (just to be sure if everything is okay).
3- IF you want to generate some log of this process, just set the variable @Log_Ready from 0 to 1.
3.1- Check if your user has the right permission to write on the choosed Directory. (Obviously)
3.2- It's not necessary, but you can change the file_name if you want...
Default Values:
@Truncate_Ready = 0
@Truncate_Ready = 0
*/
--YOU MUST CHANGE THE DATABASE NAME HERE!!
USE [AdventureWorks2012_teste] --CHANGE IT! AND BE SURE ABOUT IT!!!
GO
SET NOCOUNT ON
--USER VARIABLES
--MANDATORY
DECLARE @Truncate_Ready binary = 0 --0 is the default. Change it to 1 when you're ready
--OPTIONAL
DECLARE @Log_Ready binary = 0 --0 is the default. Change it to 1 if you want to generate txt log files.
DECLARE @LogDir nvarchar(500) = 'C:\Truncate_Log' --Change it to the desired Directory.
DECLARE @Drop_VW_Log nvarchar(500) = '3_Drop_VW_log'
DECLARE @Create_VW_Log nvarchar(500) = '2_Create_VW_log'
DECLARE @Drop_FK_Log nvarchar(500) = '5_Drop_FK_log'
DECLARE @Create_FK_Log nvarchar(500) = '4_Create_FK_log'
DECLARE @cnt_rows_ini_Log nvarchar(500) = '1_Total_Rows_before_log'
DECLARE @cnt_rows_end_Log nvarchar(500) = '6_Total_Rows_after_log'
-------------------------------------------------------------------------------------------------------------------------------------
--NON-USER VARIABLES -- Just don't change it!
DECLARE @Recreate_VW TABLE (Id INT IDENTITY(1,1), name varchar(max), Code nvarchar(max))
DECLARE @Drop_VW TABLE (Id INT IDENTITY(1,1), Code nvarchar(max))
DECLARE @Drop_FK TABLE (Id INT IDENTITY(1,1), Code nvarchar(max))
DECLARE @Recreate_FK TABLE (Id INT IDENTITY(1,1), Code varchar(max))
DECLARE @truncate_count TABLE (count_value int)
DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
DECLARE @file_results TABLE (file_exists int, file_is_a_directory int, parent_directory_exists int)
DECLARE @DBNAME sysname = (select DB_NAME())
DECLARE @crlf VARCHAR(2) = CHAR(13) + CHAR(10)
DECLARE @lnCurrent int
DECLARE @lnMax int
DECLARE @LongName NVARCHAR(MAX) = N''
DECLARE @csql varchar(2000)
DECLARE @cnt_rows_bgn NVARCHAR(MAX) = N''
DECLARE @cnt_rows_end NVARCHAR(MAX) = N''
DECLARE @cnt_global nvarchar(50)
DECLARE @cnt_vw_begin nvarchar(50)
DECLARE @cnt_vw_end nvarchar(50)
DECLARE @cnt_fk_end nvarchar(50)
DECLARE @LogPath nvarchar(500) = @LogDir + (select DB_NAME())
DECLARE @Drop_VW_FileName nvarchar(500) = @Drop_VW_Log + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + LEFT(CAST(GETDATE() AS TIME),0) + '_' + REPLACE(LEFT(CAST(GETDATE() AS TIME), 5), ':', '') + '.txt'
DECLARE @Create_VW_FileName nvarchar(500) = @Create_VW_Log + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + LEFT(CAST(GETDATE() AS TIME),0) + '_' + REPLACE(LEFT(CAST(GETDATE() AS TIME), 5), ':', '') + '.txt'
DECLARE @Drop_FK_FileName nvarchar(500) = @Drop_FK_Log + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + LEFT(CAST(GETDATE() AS TIME),0) + '_' + REPLACE(LEFT(CAST(GETDATE() AS TIME), 5), ':', '') + '.txt'
DECLARE @Create_FK_FileName nvarchar(500) = @Create_FK_Log + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + LEFT(CAST(GETDATE() AS TIME),0) + '_' + REPLACE(LEFT(CAST(GETDATE() AS TIME), 5), ':', '') + '.txt'
DECLARE @cnt_rows_ini_FileName nvarchar(500) = @cnt_rows_ini_Log + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + LEFT(CAST(GETDATE() AS TIME),0) + '_' + REPLACE(LEFT(CAST(GETDATE() AS TIME), 5), ':', '') + '.txt'
DECLARE @cnt_rows_end_FileName nvarchar(500) = @cnt_rows_end_Log + '_' + CONVERT(VARCHAR(8), GETDATE(), 112) + LEFT(CAST(GETDATE() AS TIME),0) + '_' + REPLACE(LEFT(CAST(GETDATE() AS TIME), 5), ':', '') + '.txt'
DECLARE @truncate_test VARCHAR(MAX) = (SELECT SUM(count_value) from @truncate_count)
--Check if \ is missing
--LOGFILE NAMES
IF (RIGHT(@LogDir, 1) <> '\')
BEGIN
PRINT '"\" was missing on the specified directory and added automatically.'
SET @LogPath = @LogDir + '\' + (select DB_NAME())
GOTO ini
END
--------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------- BEGINNING -----------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
ini:
----DROP TEMPORARY TABLES IF ANY
--##Scripts_FK
IF OBJECT_ID('tempdb..##Scripts_FK') IS NOT NULL
BEGIN
DROP TABLE ##Scripts_FK
END
--##Scripts_VW
IF OBJECT_ID('tempdb..##Scripts_VW') IS NOT NULL
BEGIN
DROP TABLE ##Scripts_VW
END
--##cnt_drop_VW
IF OBJECT_ID('tempdb..##cnt_drop_VW') IS NOT NULL
BEGIN
DROP TABLE ##cnt_drop_VW
END
--##cnt_drop_FK
IF OBJECT_ID('tempdb..##cnt_drop_FK') IS NOT NULL
BEGIN
DROP TABLE ##cnt_drop_FK
END
--##Log_Total_Rows_Begin
IF OBJECT_ID('tempdb..##Log_Total_Rows_Begin') IS NOT NULL
BEGIN
DROP TABLE ##Log_Total_Rows_Begin
END
--##Log_Total_Rows_End
IF OBJECT_ID('tempdb..##Log_Total_Rows_End') IS NOT NULL
BEGIN
DROP TABLE ##Log_Total_Rows_End
END
--#Total_Rows_Begin
IF OBJECT_ID('tempdb..#Total_Rows_Begin') IS NOT NULL
BEGIN
DROP TABLE #Total_Rows_Begin
END
--#Total_Rows_End
IF OBJECT_ID('tempdb..#Total_Rows_End') IS NOT NULL
BEGIN
DROP TABLE #Total_Rows_End
END
-----INSERT INTO CREATE AND DROP VIEWS VARIABLES
--Insert data into @Recreate_VW
INSERT INTO @Recreate_VW (name, Code)
SELECT name, OBJECT_DEFINITION(OBJECT_ID) + @crlf
FROM sys.objects
WHERE type_desc in ('VIEW')
-----Insert data into @Drop_VW
INSERT INTO @Drop_VW (Code)
SELECT 'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(v.name) +';' + @crlf
FROM sys.objects v
WHERE type_desc in ('VIEW')
--CREATE ##cnt_drop_VW
IF OBJECT_ID('tempdb..##cnt_drop_VW') IS NOT NULL
BEGIN
DROP TABLE ##cnt_drop_VW
END
CREATE TABLE ##cnt_drop_VW
(
cnt_drop NVARCHAR(MAX)
);
--INSERT Views Drop Scripts on ##cnt_drop_VW
INSERT INTO ##cnt_drop_VW(cnt_drop) SELECT count(Code) from @Drop_VW;
----FK
--GENERATE SCRIPTs TO DROP FKs
-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
INSERT INTO @Drop_FK (Code)
SELECT 'ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id];
--GENERATE SCRIPTs TO RECREATE FKs
-- create is a little more complex. We need to generate the list of
-- columns on both sides of the constraint, even though in most cases
-- there is only one column.
INSERT INTO @Recreate_FK (Code)
SELECT 'ALTER TABLE '
+ QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name)
+ ' ADD CONSTRAINT ' + QUOTENAME(fk.name)
+ ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
-- get all the columns in the constraint table
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.parent_column_id = c.column_id
AND fkc.parent_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
+ ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
+ '(' + STUFF((
SELECT ',' + QUOTENAME(c.name) -- get all the referenced columns
FROM sys.columns AS c
INNER JOIN sys.foreign_key_columns AS fkc
ON fkc.referenced_column_id = c.column_id
AND fkc.referenced_object_id = c.[object_id]
WHERE fkc.constraint_object_id = fk.[object_id]
ORDER BY fkc.constraint_column_id
FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs
ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs
ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;
--CREATE GLOBAL TEMPORARY to count the total of Drops in the BEGINNING
IF OBJECT_ID('tempdb..##cnt_drop_FK') IS NOT NULL
BEGIN
DROP TABLE ##cnt_drop_FK
END
CREATE TABLE ##cnt_drop_FK
(
cnt_drop NVARCHAR(MAX)
);
INSERT INTO ##cnt_drop_FK(cnt_drop) SELECT count(Code) from @Drop_FK;
PRINT '
-----------------------------------------------------------------------------------------------------
GENERAL INFORMATIONS
-----------------------------------------------------------------------------------------------------
'
PRINT 'SERVER NAME: '+ @@SERVERNAME
PRINT 'DATABASE NAME: '+ @DBNAME
--INSERT INTO CREATE AND DROP VIEWS GLOBAL TEMPORARY TABLE
IF OBJECT_ID('tempdb..##Scripts_VM') IS NOT NULL
BEGIN
DROP TABLE ##Scripts_VM
END
CREATE TABLE ##Scripts_VW
(
drop_script NVARCHAR(MAX),
create_script NVARCHAR(MAX)
);
INSERT into ##Scripts_VW(drop_script) SELECT code from @Drop_VW;
INSERT into ##Scripts_VW(create_script) SELECT code from @Recreate_VW;
--INSERT INTO CREATE AND DROP FOREIGN KEYS TEMPORARY TABLE
IF OBJECT_ID('tempdb..##Scripts_FK') IS NOT NULL
BEGIN
DROP TABLE ##Scripts_FK
END
CREATE TABLE ##Scripts_FK
(
drop_script NVARCHAR(MAX),
create_script NVARCHAR(MAX)
);
INSERT INTO ##Scripts_FK(drop_script) SELECT Code from @Drop_FK;
INSERT INTO ##Scripts_FK(create_script) SELECT Code from @Recreate_FK;
--INSERT TOTAL LINES WITHOUT COUNT EVERY TABLE (Ideal for Performance)
SELECT
QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) AS [Table_Name]
, SUM(sdmvPTNS.row_count) AS [Row_Count] INTO #Total_Rows_Begin
FROM
sys.objects AS sOBJ
INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
ON sOBJ.object_id = sdmvPTNS.object_id
WHERE
sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND sdmvPTNS.index_id < 2
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY [Table_Name]
----------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------ L O G -------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
IF @Log_Ready=0
BEGIN
PRINT '
LOG NOT CREATED BY USER CHOICE
Variable @Log_Ready = 0
If you want any log, the variable @Log_Ready should be 1
_______________________________________________________________
Log not created
'
GOTO execution
END
ELSE
BEGIN
PRINT '
-----------------------------------------------------------------------------------------------------
CREATING FOLDERS AND LOGFILES (Scripts bellow)
-----------------------------------------------------------------------------------------------------
'
-- @LogPath values
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @LogPath
-- Create the @LogPath directory
IF NOT EXISTS (SELECT 1 FROM @DirTree WHERE subdirectory = @DBName)
EXEC master.dbo.xp_create_subdir @LogPath
insert into @file_results
(file_exists, file_is_a_directory, parent_directory_exists)
EXEC master.dbo.xp_fileexist @LogPath
--VERIFICATION FOLDER PATH
IF (select file_is_a_directory from @file_results) = 0
BEGIN
select file_exists from @file_results
PRINT '
VERIFY THAT THE ROOT DIRECTORY PATH EXISTS OR THAT YOU HAVE THE RIGHT PERMISSIONS
TRUNCATE SCRIPT NOT EXECUTED'
GOTO quit
END
PRINT '
EXECUTION LOG DIRECTORY: '
PRINT @LogPath
--INSERT TOTAL LINES WITHOUT COUNT EVERY TABLE (Ideal for Performance)
SELECT Table_Name + ' - ' + CAST(Row_Count as VARCHAR(MAX)) + ' Rows' as "Log"
INTO ##Log_Total_Rows_Begin
FROM #Total_Rows_Begin
SET @cnt_rows_bgn = (select SUM(Row_Count) from #Total_Rows_Begin)
--GENERATE LOG FILES
PRINT '
EXECUTION LOG FILES: '
SELECT @cSQL= 'bcp "SELECT * from ##Log_Total_Rows_Begin" queryout '+ @LogPath + '\' + @cnt_rows_ini_FileName + ' -e -t -T -c -S' + @@SERVERNAME
EXEC xp_cmdshell @csql, no_output
PRINT + @LogPath + '\' + @cnt_rows_ini_FileName
PRINT + @LogPath + '\' + @cnt_rows_end_FileName
SELECT @cSQL= 'bcp "SELECT create_script from ##Scripts_VW WHERE create_script IS NOT NULL" queryout ' + @LogPath + '\' + @Create_VW_FileName + ' -e -t -T -c -S' + @@SERVERNAME
EXEC xp_cmdshell @csql, no_output
PRINT + @LogPath + '\' + @Create_VW_FileName
SELECT @cSQL= 'bcp "SELECT drop_script from ##Scripts_VW WHERE drop_script IS NOT NULL" queryout ' + @LogPath + '\' + @Drop_VW_FileName + ' -e -t -T -c -S' + @@SERVERNAME
EXEC xp_cmdshell @csql, no_output
PRINT + @LogPath + '\' + @Drop_VW_FileName
SELECT @cSQL= 'bcp "SELECT create_script from ##Scripts_FK WHERE create_script IS NOT NULL" queryout ' + @LogPath + '\' + @Create_FK_FileName + ' -e -t -T -c -S' + @@SERVERNAME
EXEC xp_cmdshell @csql, no_output
PRINT + @LogPath + '\' + @Create_FK_FileName
SELECT @cSQL= 'bcp "SELECT drop_script from ##Scripts_FK WHERE drop_script IS NOT NULL" queryout ' + @LogPath + '\' + @Drop_FK_FileName + ' -e -t -T -c -S' + @@SERVERNAME
EXEC xp_cmdshell @csql, no_output
PRINT + @LogPath + '\' + @Drop_FK_FileName
--ENDDING OF THE LOG SECTION
END
----------------------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------ E X E C U T I O N -------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------
--EXECUTE DROP VIEW
execution:
PRINT '
-----------------------------------------------------------------------------------------------------
DROP ALL VIEWS
-----------------------------------------------------------------------------------------------------
'
--IF TRUNCATE_READY FALSE
IF @Truncate_Ready=0
BEGIN
PRINT '
Views not dropped
Variable @Truncate_Ready = 0
This value should be 1
_______________________________________________________________
Views not dropped yet by user choice
'
END
ELSE
BEGIN
SET @cnt_vw_begin = (select count(drop_script) from ##Scripts_VW where drop_script IS NOT NULL)
SELECT @lnMax = MAX(Id) FROM @Drop_VW
SET @lnCurrent = 1
SET @cnt_global =0
WHILE @lnCurrent <= @lnMax
BEGIN
SELECT @LongName = Code FROM @Drop_VW WHERE Id = @lnCurrent
WHILE @LongName <> ''
BEGIN
EXEC sp_executesql @LongName
--print LEFT(@LongName,8000) ----just uncomment if you want to see all the "DROP VIEW..." statements in the Results (Messages)
SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
END
SET @lnCurrent = @lnCurrent + 1
SET @cnt_global = @cnt_global + 1
END
PRINT 'TOTAL DROPED VIEWS: ' +@cnt_global
END
PRINT '
-----------------------------------------------------------------------------------------------------
DROP FK, Disabling triggers, NOCHECK Constraints
-----------------------------------------------------------------------------------------------------
'
--IF TRUNCATE_READY FALSE
IF @Truncate_Ready=0
BEGIN
PRINT '
By user choice:
FKs Not dropped
Triggers not Disabled
Constraints not changed
(TRUNCATE WILL NOT BE EXECUTED YET)
Variable @Truncate_Ready = 0
This value should be 1
_______________________________________________________________
Nothing executed by user choice
'
END
ELSE
BEGIN
SELECT @lnMax = MAX(Id) FROM @Drop_FK
SET @lnCurrent = 1
SET @cnt_global =0
WHILE @lnCurrent <= @lnMax
BEGIN
SELECT @LongName = Code FROM @Drop_FK WHERE Id = @lnCurrent
WHILE @LongName <> ''
BEGIN
EXEC sp_executesql @LongName
--print LEFT(@LongName,8000) ----just uncomment if you want to see all the "ALTER TABLE... DROP CONSTRAINT..." statements in the Results (Messages)
SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
END
SET @lnCurrent = @lnCurrent + 1
SET @cnt_global = @cnt_global + 1
END
--EXEC sp_executesql @Drop_FK
PRINT '
DROP FKs -------------------- OK'
PRINT 'TOTAL DROPED FKs: ' +@cnt_global
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
PRINT '
NOCHECK CONSTRAINT ALL ------ OK
'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
PRINT '
DISABLE TRIGGER ALL --------- OK
'
PRINT '
______________________________________________________________________
Activities successfully executed
'
END
PRINT '
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
T R U N C A T E T I M E !
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
'
--DECLARE @truncate_test VARCHAR(MAX) = (SELECT SUM(count_value) from @truncate_count)
--IF TRUNCATE_READY FALSE
IF @Truncate_Ready=0
BEGIN
PRINT '
TRUNCATE NOT EXECUTED YET
Variable @Truncate_Ready = 0
This value should be 1
_______________________________________________________________
Truncate not executed by user choice
'
GOTO truncate_test
END
--IF TRUNCATE_READY TRUE
ELSE
BEGIN
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
PRINT '
TRUNCATE COMMAND EXECUTED FOR EACH TABLE'
END
--AFTER TRUNCATE TEST
truncate_test:
PRINT '
-----------------------------------------------------------------------------------------------------
TRUNCATE TEST
-----------------------------------------------------------------------------------------------------
'
insert into @truncate_count (count_value)
EXEC sp_MSForEachTable 'select count(*) from ?'
SET @truncate_test = (SELECT SUM(count_value) from @truncate_count)
IF @truncate_test=0
BEGIN
PRINT '
TRUNCATE SUCCESSFULLY EXECUTED
'+@truncate_test+' Rows Remaining in the Database
OPTIONAL STEP:
If you want to verify for your own the result, please execute the following script:
--Script to verify the Truncate action
USE ['+@DBNAME+']
GO
EXEC sp_MSForEachTable ''select * from ? ''
_______________________________________________________________
Truncate Succeded
'
END
ELSE
BEGIN
PRINT '
MANDATORY STEP:
You SHOULD control what went wrong and solve it.
--Script to verify the Truncate action
USE ['+@DBNAME+']
GO
EXEC sp_MSForEachTable ''select * from ? ''
'+@truncate_test+'
_______________________________________________________________
Truncate FAILED (or not executed yet)
'
END
--RECRIAR VIEWS
after_truncate:
PRINT '
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
RE-CREATE ALL OBJECTS
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
'
--IF TRUNCATE_READY FALSE
IF @Truncate_Ready=0
BEGIN
PRINT '
By user choice:
It was not necessary recreate or reactivate any object
Variable @Truncate_Ready = 0
This value should be 1
_______________________________________________________________
Nothing executed yet by user choice
'
END
ELSE
BEGIN
PRINT '
-----------------------------------------------------------------------------------------------------
RE-CREATE ALL VIEWS
-----------------------------------------------------------------------------------------------------
'
SELECT @lnMax = MAX(Id) FROM @Recreate_VW
SET @lnCurrent = 1
SET @cnt_global = 0
WHILE @lnCurrent <= @lnMax
BEGIN
SELECT @LongName = Code FROM @Recreate_VW WHERE Id = @lnCurrent
WHILE @LongName <> ''
BEGIN
EXEC sp_executesql @LongName
--print LEFT(@LongName,8000) ----just uncomment if you want to see all the "CREATE VIEW..." statements in the Results (Messages)
SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
END
SET @lnCurrent = @lnCurrent + 1
SET @cnt_global = @cnt_global + 1
END
PRINT '
TOTAL RECREATED VIEWS: ' +@cnt_global
PRINT '_____________________________________________________________________
Views Recriadas com sucesso
'
PRINT '
-----------------------------------------------------------------------------------------------------
Reativando triggers e CHECK Constraints
-----------------------------------------------------------------------------------------------------
'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
PRINT '
CHECK CONSTRAINT ALL --------- OK
'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'
PRINT '
ENABLE TRIGGER ALL ----------- OK
'
EXEC sp_MSforeachtable 'SET QUOTED_IDENTIFIER ON;
IF NOT EXISTS (
SELECT
*
FROM
SYS.IDENTITY_COLUMNS
JOIN SYS.TABLES ON SYS.IDENTITY_COLUMNS.Object_ID = SYS.TABLES.Object_ID
WHERE
SYS.TABLES.Object_ID = OBJECT_ID(''?'') AND SYS.IDENTITY_COLUMNS.Last_Value IS NULL
)
AND OBJECTPROPERTY( OBJECT_ID(''?''), ''TableHasIdentity'' ) = 1
DBCC CHECKIDENT (''?'', RESEED, 0) WITH NO_INFOMSGS'
--RECREATE FKs
PRINT '
-----------------------------------------------------------------------------------------------------
RE-CREATE FKs (Scripts Bellow)
-----------------------------------------------------------------------------------------------------
'
SELECT @lnMax = MAX(Id) FROM @Recreate_FK
SET @lnCurrent = 1
SET @cnt_global = 0
WHILE @lnCurrent <= @lnMax
BEGIN
SELECT @LongName = Code FROM @Recreate_FK WHERE Id = @lnCurrent
WHILE @LongName <> ''
BEGIN
EXEC sp_executesql @LongName
--print LEFT(@LongName,8000) ----just uncomment if you want to see all the "ALTER TABLE ... ADD CONSTRAINT" statements in the Results (Messages)
SET @LongName = SUBSTRING(@LongName, 8001, LEN(@LongName))
END
SET @lnCurrent = @lnCurrent + 1
SET @cnt_global = @cnt_global + 1
END
PRINT '
TOTAL RECREATED FKs: ' +@cnt_global
PRINT '_______________________________________________________________
FKs Recriadas com sucesso
'
END
--5. VERIFYING EVERYTHING
IF @Log_Ready = 1
BEGIN
--INSERT ENDING TOTAL ROWS WITHOUT COUNT EVERY TABLE (Ideal for Performance)
--CREATE #Total_Rows_end
SELECT QUOTENAME(SCHEMA_NAME(sOBJ.schema_id)) + '.' + QUOTENAME(sOBJ.name) + ' - ' +
CAST(SUM(sdmvPTNS.row_count) as VARCHAR(MAX)) + ' Rows' AS [Log]
INTO ##Log_Total_Rows_end
FROM sys.objects AS sOBJ
INNER JOIN sys.dm_db_partition_stats AS sdmvPTNS
ON sOBJ.object_id = sdmvPTNS.object_id
WHERE sOBJ.type = 'U'
AND sOBJ.is_ms_shipped = 0x0
AND sdmvPTNS.index_id < 2
GROUP BY
sOBJ.schema_id
, sOBJ.name
ORDER BY sOBJ.schema_id,sOBJ.name
--CREATE #Total_Rows_end
--SELECT Table_Name + ' - ' + CAST(Row_Count as VARCHAR(MAX)) + ' Rows' as "Log"
-- INTO ##Log_Total_Rows_end
-- FROM #Total_Rows_end
--CREATE LOG FILE - TOTAL ROWS AT THE ENDING
SELECT @cSQL= 'bcp "SELECT * from ##Log_Total_Rows_end" queryout '+ @LogPath + '\' + @cnt_rows_end_FileName + ' -e -t -T -c -S' + @@SERVERNAME
EXEC xp_cmdshell @csql, no_output
END
--END IF
SET @cnt_rows_bgn = (select SUM(Row_Count) from #Total_Rows_Begin)
SET @cnt_vw_end = (SELECT count(v.name) FROM sys.objects v WHERE type_desc in ('VIEW'))
SET @cnt_global = (select cnt_drop from ##cnt_drop_VW)
SET @cnt_vw_end = (SELECT count(v.name) FROM sys.objects v WHERE type_desc in ('VIEW'))
PRINT '
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
FINAL REPORT (Execute the script Bellow if necessary)
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
'
PRINT '
ROWSs
-----------'
PRINT 'Total Rows in the beginning: ' +@cnt_rows_bgn
PRINT 'Total Rows after the Truncate: ' +@truncate_test
PRINT '
VIEWS
-----------'
PRINT 'Total Views in the BEGINNING = ' +@cnt_global--+@cnt_vw_begin
PRINT 'Total Views in the End = ' +@cnt_vw_end
PRINT '
FKs
-----------'
SET @cnt_rows_bgn = (select SUM(Row_Count) from #Total_Rows_Begin)
SET @cnt_global = (select cnt_drop from ##cnt_drop_FK)
SET @cnt_fk_end = (SELECT count(fk.name) FROM sys.foreign_keys AS fk)
PRINT 'Total FKs in the BEGINNING = ' +@cnt_global
PRINT 'Total FKs in the End = ' +@cnt_fk_end
--DROP TEMPORARY TABLES IN THE END, IF ANY
quit:
IF OBJECT_ID('tempdb..##Scripts_FK') IS NOT NULL
BEGIN
DROP TABLE ##Scripts_FK
END
IF OBJECT_ID('tempdb..##Scripts_VW') IS NOT NULL
BEGIN
DROP TABLE ##Scripts_VW
END
IF OBJECT_ID('tempdb..##cnt_drop_VW') IS NOT NULL
BEGIN
DROP TABLE ##cnt_drop_VW
END
IF OBJECT_ID('tempdb..##cnt_drop_FK') IS NOT NULL
BEGIN
DROP TABLE ##cnt_drop_FK
END
IF OBJECT_ID('tempdb..#Total_Rows_Begin') IS NOT NULL
BEGIN
DROP TABLE #Total_Rows_Begin
END
IF OBJECT_ID('tempdb..#Total_Rows_End') IS NOT NULL
BEGIN
DROP TABLE #Total_Rows_End
END
IF OBJECT_ID('tempdb..##Log_Total_Rows_Begin') IS NOT NULL
BEGIN
DROP TABLE ##Log_Total_Rows_Begin
END
IF OBJECT_ID('tempdb..##Log_Total_Rows_End') IS NOT NULL
BEGIN
DROP TABLE ##Log_Total_Rows_End
END
