Delete From sys.views With Wildcards 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?
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.