|
| 1 | +/* |
| 2 | +Author: Jason Brimhall |
| 3 | +Source link: http://www.sqlservercentral.com/blogs/sqlrnnr/2017/10/27/drop-that-schema/ |
| 4 | +*/ |
| 5 | + |
| 6 | +DECLARE @schemaname SYSNAME = 'dbo'; |
| 7 | +IF ( SELECT OBJECT_ID('tempdb.dbo.#dropschema') |
| 8 | + ) IS NOT NULL |
| 9 | + BEGIN |
| 10 | + DROP TABLE #dropschema; |
| 11 | + END; |
| 12 | +SELECT s.name AS SchName |
| 13 | + , o.name AS ObjName |
| 14 | + , o.create_date |
| 15 | + , o.type_desc |
| 16 | + , o.type |
| 17 | + , o.parent_object_id |
| 18 | + , CASE WHEN o.type IN ( 'F', 'D', 'UQ' ) |
| 19 | + THEN 'ALTER TABLE [' + s.name + '].[' |
| 20 | + + OBJECT_NAME(o.parent_object_id) |
| 21 | + + '] DROP CONSTRAINT [' + o.name + '];' |
| 22 | + WHEN o.type IN ( 'P', 'PC' ) |
| 23 | + THEN 'DROP PROCEDURE [' + s.name + '].[' + o.name + '];' |
| 24 | + WHEN o.type IN ( 'FT', 'FN', 'TF', 'AF', 'FS', 'IF' ) |
| 25 | + THEN 'DROP FUNCTION [' + s.name + '].[' + o.name + '];' |
| 26 | + WHEN o.type = 'V' |
| 27 | + THEN 'DROP VIEW [' + s.name + '].[' + o.name + '];' |
| 28 | + WHEN o.type = 'SO' |
| 29 | + THEN 'DROP SEQUENCE [' + s.name + '].[' + o.name + '];' |
| 30 | + WHEN o.type = 'U' |
| 31 | + THEN 'DROP TABLE [' + s.name + '].[' + o.name + '];' |
| 32 | + WHEN o.type = 'PG' |
| 33 | + THEN 'EXEC sp_control_plan_guide N''DROP'', N''' + o.name |
| 34 | + + ''';' |
| 35 | + END AS 'DropText' |
| 36 | + , CASE WHEN o.type IN ( 'P', 'FN', 'FT', 'TF', 'PC', 'FS', 'AF', 'IF' ) |
| 37 | + THEN 1 |
| 38 | + WHEN o.type = 'V' THEN 2 |
| 39 | + WHEN o.type IN ( 'F', 'D' ) THEN 3 |
| 40 | + WHEN o.type = 'SO' THEN 4 |
| 41 | + WHEN o.type = 'UQ' THEN 5 |
| 42 | + WHEN o.type = 'PG' THEN 6 |
| 43 | + WHEN o.type = 'U' THEN 7 |
| 44 | + END AS ProcessOrder |
| 45 | + INTO #dropschema |
| 46 | + FROM sys.objects o |
| 47 | + INNER JOIN sys.schemas s |
| 48 | + ON o.schema_id = s.schema_id |
| 49 | + --left outer join sys.default_constraints dc |
| 50 | + -- on o.object_id = dc.parent_object_id |
| 51 | + WHERE o.type NOT IN ( 'PK', 'IT', 'S' ) |
| 52 | + AND s.name = @schemaname |
| 53 | + ORDER BY o.parent_object_id DESC |
| 54 | + , o.type; |
| 55 | + |
| 56 | +IF EXISTS ( SELECT 1 / 0 |
| 57 | + FROM #dropschema |
| 58 | + WHERE type IN ( 'PC', 'FS', 'AF', 'FT' ) ) |
| 59 | + BEGIN |
| 60 | + INSERT INTO #dropschema |
| 61 | + ( SchName |
| 62 | + , ObjName |
| 63 | + , create_date |
| 64 | + , type_desc |
| 65 | + , type |
| 66 | + , parent_object_id |
| 67 | + , DropText |
| 68 | + , ProcessOrder |
| 69 | + ) |
| 70 | + SELECT '' AS ScheName |
| 71 | + , ass.name AS ObjName |
| 72 | + , ass.create_date |
| 73 | + , 'Assembly' |
| 74 | + , 'AS' |
| 75 | + , asm.assembly_id |
| 76 | + , 'DROP ASSEMBLY [' + ass.name + '];' AS DropText |
| 77 | + , 4 AS ProcessOrder |
| 78 | + FROM sys.assemblies ass |
| 79 | + INNER JOIN sys.assembly_modules asm |
| 80 | + ON ass.assembly_id = asm.assembly_id |
| 81 | + INNER JOIN sys.objects o |
| 82 | + ON asm.object_id = o.object_id; |
| 83 | + END; |
| 84 | + |
| 85 | +INSERT INTO #dropschema |
| 86 | + ( DropText |
| 87 | + , ProcessOrder |
| 88 | + , SchName |
| 89 | + , ObjName |
| 90 | + , create_date |
| 91 | + , parent_object_id |
| 92 | + ) |
| 93 | + VALUES |
| 94 | + ( 'DROP SCHEMA ' + QUOTENAME(@schemaname) + ';' |
| 95 | + , 99 |
| 96 | + , '' |
| 97 | + , '' |
| 98 | + , '' |
| 99 | + , '' |
| 100 | + ); |
| 101 | + |
| 102 | +SELECT * |
| 103 | + FROM #dropschema |
| 104 | + ORDER BY ProcessOrder ASC; |
0 commit comments