1

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?

10
  • 1
    Why are you deleting from sys.views? Have you tried DROP VIEW ..?
    – vercelli
    Aug 31, 2016 at 14:54
  • @vercelli - would syntax be Drop View Where View.Name Like.... Aug 31, 2016 at 14:55
  • No, Drop view DonotNeed1; Drop view DonotNeed2; ...
    – vercelli
    Aug 31, 2016 at 14:56
  • There is no way to drop all views in one pass? Aug 31, 2016 at 14:57
  • 1
    perfectly! That "DE" statement will work, thank you! Aug 31, 2016 at 14:58

3 Answers 3

5

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.

3

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 !
1

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"
        }
    }
    }

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.