Skip to content

Instantly share code, notes, and snippets.

@hero827

hero827/move-deprecated-objects.sql

Forked from dhmacher/move-deprecated-objects.sql
Created July 26, 2022 21:43
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save hero827/d9233f2669ebf127aa4f794c4981c05f to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save hero827/d9233f2669ebf127aa4f794c4981c05f to your computer and use it in GitHub Desktop.
Download ZIP
Move temp/backup objects to Deprecated schema
Raw
move-deprecated-objects.sql
IF (SCHEMA_ID('Deprecated') IS NULL)
EXEC('CREATE SCHEMA [Deprecated];');
SELECT 'ALTER SCHEMA [Deprecated] TRANSFER '+QUOTENAME(OBJECT_SCHEMA_NAME([object_id]))+'.'+QUOTENAME(OBJECT_NAME([object_id]))+';'
FROM sys.objects
WHERE [schema_id] NOT IN (SCHEMA_ID('Deprecated'), SCHEMA_ID('sys'))
AND [type] NOT IN ('PK', 'F', 'D', 'UQ')
AND ([name] LIKE '%xx%' OR [name] LIKE '%[0-9][0-9][0-9][0-9][0-9][0-9]%'
OR [name] LIKE '%[ _]temp' OR [name] LIKE 'temp[ _]%'
OR [name] LIKE '%[ _]old' OR [name] LIKE 'old[ _]%'
OR [name] LIKE '%backup' OR [name] LIKE 'backup%')
ORDER BY OBJECT_SCHEMA_NAME([object_id]), OBJECT_NAME([object_id]);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment