Ciao!
Sì, questo è uno script abbastanza pericoloso che mi è stato chiesto da un collega che aveva questa necessità periodica di TRUNCARE tutte le tabelle al interno di un database.
All’inizio sembra molto semplice, però ci sono delle restrizioni come delle Views e FK che impediscono il commando Truncate di essere eseguito, quindi prima si deve dropare e poi ricreare e va gestito sempre in automatico.
All’interno dello script ci sono anche alcune impostazioni da cambiare e ci sono delle istruzioni (in inglese) da seguire.
Però attenzione: Fallo a tuo proprio rischio e pericolo!
Eccolo qua:
/*
****************************************************************************************************************************************
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
