Safely Dropping a Set of Views by Pattern (T‑SQL)

by

Bulk‑generate and (optionally) execute DROP VIEW statements for a subset of views in a given schema

When to use this

  • Cleaning up date‑suffixed or pattern‑generated views (e.g., project_value_20240501).
  • Deleting a family of views after a deprecation notice.

Safety checklist

  1. Scope correctly: Limit to the exact schema (svw) and a tight LIKE pattern.
  2. Dry‑run first: Always PRINT the generated SQL before executing.
  3. Session guards: Confirm there are no dependent objects that will break (reports, stored procs).
  4. Permissions: Requires DROP VIEW permission (or ALTER on schema).
    -- Drop views from list 
DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql = @sql + 'DROP VIEW ' + QUOTENAME(s.name) + '.' + QUOTENAME(v.name) + ';' + CHAR(10)
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id
WHERE 
s.name = 'svw' and -- choose schema
    v.name LIKE 'project_value%'  -- Ensure only relevant viewsnames are selected
    -- AND TRY_CONVERT(DATE, RIGHT(v.name, 8), 112) >= '2024-05-01'; -- Filter views before May 2024

PRINT @sql;  -- Check the generated SQL before execution


-- EXEC sp_executesql @sql;

Leave a Comment