e.etl-unit.etlunit-sql-server-database.1.6.6.source-code.purge_db.vm Maven / Gradle / Ivy
DECLARE @SQL_DROP VARCHAR(1024)
PRINT('DROPPING Constraints again')
-- drop constraints
DECLARE con_cursor CURSOR
FOR
SELECT
'ALTER TABLE ' + S.NAME + '.' + T.NAME + ' DROP CONSTRAINT ' + FK.NAME
FROM
SYS.TABLES T
JOIN
SYS.SCHEMAS S
ON
T.SCHEMA_ID = S.SCHEMA_ID
JOIN
sys.foreign_keys FK
ON
T.OBJECT_ID = FK.parent_object_id
WHERE
S.NAME NOT IN ('SYS', 'INFORMATION_SCHEMA')
OPEN con_cursor
FETCH NEXT FROM con_cursor INTO @SQL_DROP
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT(@SQL_DROP)
EXEC(@SQL_DROP)
FETCH NEXT FROM con_cursor INTO @SQL_DROP
END
CLOSE con_cursor
DEALLOCATE con_cursor
;;
DECLARE @SQL VARCHAR(MAX)
SET @SQL = 'SET QUOTED_IDENTIFIER ON' + CHAR(10)
SELECT
@SQL = @SQL + 'BEGIN TRY TRUNCATE TABLE ' + S.NAME + '.' + T.NAME + ' END TRY BEGIN CATCH DELETE FROM ' + S.NAME + '.' + T.NAME + ' END CATCH' + CHAR(10)
FROM
SYS.TABLES T
JOIN
SYS.SCHEMAS S
ON
T.SCHEMA_ID = S.SCHEMA_ID
WHERE
S.NAME NOT IN ('SYS', 'INFORMATION_SCHEMA')
EXEC (@SQL)