Cybercrux

Everything is achievable through technology

Drop or Truncate Schema

Delete in Proper order

SET NOCOUNT ON
GO
SELECT 'USE [' + db_name() +']';
;WITH a AS
(
SELECT 0 AS lvl,
t.object_id AS tblID
FROM sys.TABLES t
WHERE t.is_ms_shipped=0
AND t.object_id NOT IN (SELECT f.referenced_object_id FROM sys.foreign_keys f)
UNION ALL
SELECT a.lvl + 1 AS lvl,
f.referenced_object_id AS tblId
FROM a
INNER JOIN sys.foreign_keys f
ON a.tblId=f.parent_object_id
AND a.tblIDf.referenced_object_id
)
SELECT 'Delete from ['+ object_schema_name(tblID) + '].[' + object_name(tblId) + ']'
FROM a
GROUP BY tblId
ORDER BY MAX(lvl),1
-- finish table delete

--Droup a Schema is 2 step Process
--Truncate a schema is 3 step Process
SET NOCOUNT ON;
DECLARE @SchemaName nvarchar(250)
SET @SchemaName='ammu'
--Step 1: remove all CONSTRAINT
SELECT 'ALTER TABLE ' +
'[' + s.name + '].[' + t.name + ']' +
' DROP CONSTRAINT [' + f.name +']'
FROM sys.foreign_keys f
INNER JOIN sys.TABLES t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0
and t.schema_id = schema_id(@SchemaName);
--Step 2: remove all CONSTRAINT
SELECT 'drop TABLE ' + '[' + s.name + '].[' + t.name + ']'
FROM sys.TABLES t
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0 and t.schema_id = schema_id(@SchemaName);
--Step 3: remove all CONSTRAINT
SELECT 'ALTER TABLE ' +
'[' + s.name + '].[' + t.name + ']' +
' ADD CONSTRAINT [' + f.name + ']' +
' FOREIGN KEY (' +
Stuff( (SELECT ', ['+col_name(fk.parent_object_id, fk.parent_column_id) +']'
FROM sys.foreign_key_columns fk
WHERE constraint_object_id = f.object_id
ORDER BY constraint_column_id
FOR XML Path('')
), 1,2,'') + ')' +
' REFERENCES [' +
object_schema_name(f.referenced_object_id)+'].['+object_name(f.referenced_object_id) + '] (' +
Stuff((SELECT ', ['+col_name(fc.referenced_object_id, fc.referenced_column_id)+']'
FROM sys.foreign_key_columns fc
WHERE constraint_object_id = f.object_id
ORDER BY constraint_column_id
FOR XML Path('')),
1,2,'') +
')' +
' ON DELETE ' + REPLACE(f.delete_referential_action_desc, '_', ' ') +
' ON UPDATE ' + REPLACE(f.update_referential_action_desc , '_', ' ') collate database_default
FROM sys.foreign_keys f
INNER JOIN sys.TABLES t ON f.parent_object_id=t.object_id
INNER JOIN sys.schemas s ON t.schema_id=s.schema_id
WHERE t.is_ms_shipped=0 and t.schema_id = schema_id(@SchemaName);

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s