Skip to content

Instantly share code, notes, and snippets.

@JerryNixon
Last active April 28, 2021 22:29
Show Gist options
  • Save JerryNixon/ca4906f7f31972a5e6e2a43a63891dd0 to your computer and use it in GitHub Desktop.
Save JerryNixon/ca4906f7f31972a5e6e2a43a63891dd0 to your computer and use it in GitHub Desktop.
WITH tables AS
(
SELECT
schemas.name AS schema_name
, tables.name AS table_name
, CONCAT(schemas.name, '.', tables.name) AS full_name
FROM sys.tables
JOIN sys.schemas
ON tables.schema_id = schemas.schema_id
WHERE tables.type = 'U'
)
SELECT
full_name
, CONCAT('DELETE FROM ', full_name) AS delete_script
, CONCAT('TRUNCATE TABLE ', full_name) AS truncate_script
, CONCAT('DROP TABLE IF EXISTS ', full_name) AS drop_script
, CONCAT('UPDATE STATISTICS ', full_name) AS update_script
, CONCAT('ALTER TABLE ', full_name, ' NOCHECK CONSTRAINT ALL') AS disable_constraints_script
, CONCAT('ALTER TABLE ', full_name, ' WITH CHECK CHECK CONSTRAINT ALL') AS enable_constraints_script
, CONCAT('IF NOT EXISTS(SELECT TOP 1 1 FROM ', full_name, ') THROW 51000, TABLE ''', full_name, ''' has zero rows.'', 1;') AS require_data_script
FROM tables
ORDER BY full_name
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment