Delete From sys.views With Wildcards

Question

I can run a delete statement on my server, but anytime I add a where clause to my delete statement it throws an error of:

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

This syntax throws the error ->

DELETE 
FROM sys.views
WHERE sys.views.name LIKE '%DoNotNeed_%'

What am I setting up incorrectly in order to delete all views that are prefaced with Do Not Need?

Answer 1

An alternative dynamic SQL approach (which also includes schema, often important!):

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N',' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name)
FROM sys.schemas AS s
INNER JOIN sys.views AS v
ON s.[schema_id] = v.[schema_id]
WHERE v.[name] LIKE N'%DoNotNeed[_]%';

SET @sql = N'DROP VIEW ' + STUFF(@sql, 1, 1, N'') + N';';

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

This will produce a command like the following, which is legal (you don't need a DROP VIEW command for every view):

DROP VIEW [dbo].[view1], [dbo].[view2], [dbo].[view3], ...;

When you believe it is correct (and note that PRINT might not output the entire command if it exceeds 8k), uncomment the EXEC.

Note that whatever solution you use, you might run into difficulties dropping certain views (for example, they could be referencing by objects that use WITH SCHEMABINDING). In which case you may want instead to use something a little more elaborate:

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'
BEGIN TRY
  DROP VIEW ' + name + N';
  PRINT N''   Dropped ' + name + N'!'';
END TRY
BEGIN CATCH
  PRINT N''Could not drop ' + name + N'!'';
  PRINT ERROR_MESSAGE();
END CATCH'
FROM 
(
  SELECT name = QUOTENAME(s.name) + N'.' + QUOTENAME(v.name)
   FROM sys.schemas AS s
   INNER JOIN sys.views AS v
   ON s.[schema_id] = v.[schema_id]
   WHERE v.[name] LIKE N'%DoNotNeed[_]%'
) AS x;

PRINT @sql;
-- EXEC sys.sp_executesql @sql;

Parts of this solution can be complicated if you have views with ' in their names. Don't do that.

Answer 2

You are deleting from sys.views which is a system view and you are not allowed to do that ...

Msg 259, Level 16, State 1, Line 3 Ad hoc updates to system catalogs are not allowed.

You cannot delete a view, you can drop it.

select 'drop view '+quotename(name) +';'  as [DROP COMMANDS] from sys.views 
where name like '%DoNotNeed_%' -- filter !

Answer 3

You can drop all views on the instance via the following PowerShell script. Edit YourInstanceName before running. This will drop all views with DoNotNeed_ in the name from all databases on the instance. This script could easily be extended to do the same against hundreds of instances by adding another level of iteration.

Import-Module SQLSERVER

$ViewsToDrop = @()
Get-SqlDatabase -ServerInstance <YourInstanceName>| % {
    foreach ($View in $_.Views) {
        if ($View.IsSystemObject -eq 0 -and $View.Name -like '*DoNotNeed_*') {
        $ViewsToDrop += $View.Name
        }
    }
    foreach ($ViewToDrop in $ViewsToDrop) {
        if($_.Views.Contains($ViewToDrop)) {
            $_.Views[$ViewToDrop].Drop()
            Write-Host "$ViewToDrop has been dropped from the $_ database"
        }
    }
    }