T-SQL :: DELETE All Data in Schema

Your SQL Server Colleagues
…your colleagues, around your desk, waiting for you to press F5…

I never empathize enough how much the welfare of my family relies on StackOverflow.

Every time I receive help I feel so thankful and so proud to participate to the community. But more thankful than proud, of course.

All this to say that Aaron Bertrand lately replied to one of my questions on StackOverflow.

The solution is amazingly easy and allow you to delete all data from a particular schema that you can setup in a variable on line 3:

Here I’m testing it on AdventureWorks2012:

DECLARE @TargetSchema AS VARCHAR(50)

SET @TargetSchema = 'Person' -- Put here your target schema

DROP TABLE

IF EXISTS #x
	CREATE TABLE #x -- feel free to use a permanent table
		(
		drop_script NVARCHAR(max)
		,create_script NVARCHAR(max)
		);

DECLARE @drop NVARCHAR(max) = N''
	,@create NVARCHAR(max) = N'';

-- drop is easy, just build a simple concatenated list from sys.foreign_keys:
SELECT @drop += N'
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];

INSERT #x (drop_script)
SELECT @drop;

-- 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.
SELECT @create += N'
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'./text()[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'./text()[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;

UPDATE #x
SET create_script = @create;

PRINT @drop;
PRINT @create;
	/* Uncomment this to DELETE

EXEC sys.sp_executesql @drop

-- clear out data etc. here
DECLARE @truncate NVARCHAR(max) = N'';

SELECT @truncate += N'DELETE FROM ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N';'
FROM sys.schemas AS s
INNER JOIN sys.tables AS t ON s.[schema_id] = t.[schema_id]
WHERE s.name = @TargetSchema;

EXEC sys.sp_executesql @truncate;

EXEC sys.sp_executesql @create;

*/

And I would like to say more about it but unfortunately I have upgraded to Windows 11 and I’m fighting with all my forces to have at least the design to look more like Windows 10.

When Satya Nadella said that Windows 10 was the last version of Windows, I was so happy…

…what happened? Why have you changed your mind? That was a great idea!

Don’t try to be fancy, Microsoft…

And please, don’t even think about setting this up on Windows Server.

 

Related

Leave a Reply

You have to agree to the comment policy.