Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save EitanBlumin/8b4115696684fd21c2cc67ab47ff1105 to your computer and use it in GitHub Desktop.
Save EitanBlumin/8b4115696684fd21c2cc67ab47ff1105 to your computer and use it in GitHub Desktop.
Generate commands to drop the "jobs" and "jobs_internal" schemas and all of their objects
SELECT
'ALTER TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(OBJECT_NAME(parent_object_id))
+ ' DROP CONSTRAINT ' + QUOTENAME(name)
FROM sys.foreign_keys
WHERE schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
CASE schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
SELECT
'DROP VIEW ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.views
WHERE schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
CASE schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
SELECT
'DROP TABLE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.tables
WHERE schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
CASE schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
SELECT
'DROP PROCEDURE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.procedures
WHERE schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
CASE schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
SELECT 'DROP ' + o.type_desc COLLATE database_default + ' ' + QUOTENAME(SCHEMA_NAME(o.schema_id)) + '.' + QUOTENAME(o.name COLLATE database_default)
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.object_id = o.object_id
WHERE o.schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
CASE o.schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
SELECT
'DROP TYPE ' + QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name)
FROM sys.types
WHERE schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
CASE schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
SELECT
'DROP SCHEMA ' + QUOTENAME(name)
FROM sys.schemas
WHERE schema_id IN ( SCHEMA_ID('jobs'), SCHEMA_ID('jobs_internal') )
ORDER BY
CASE schema_id WHEN SCHEMA_ID('jobs') THEN 1 ELSE 2 END ASC
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment