Deleting Multiple Views/Tables by Pattern in Microsoft SQL Server

If you have ever used Microsoft SQL Server tools to work with tables and/or views you will know that you cannot select multiple items for deletion (or at least not that I have found) through the UI. A good feature to ensure a unsuspecting user doesn't accidentally delete all the data from your production server, right?

Well, suppose you needed to delete 4000+ views from an application database that didn't belong there (long story).

The views all start with the same beginning text so they are right next to each other in the UI for SQL. Now the great feature is the bane of your existence as you hit delete, then confirm, delete, then confirm...

Here is one solution you can use with some simple VB.NET code.

...

   Dim PATTERNS() As String = {"prefix1%", "prefix2%", "prefix3%"}
   Dim strDatasource As String = _
"Data Source=dbsvr;Initial Catalog=db;User ID=usr;Password=pwd"
   Dim strSqlStatement As String = _
"SELECT name FROM sys.sysobjects WHERE (name LIKE '" & PATTERNS(0) & _
"' OR name LIKE '" & PATTERNS(1) & "' OR name LIKE '" & PATTERNS(2) & _
"') AND (xtype = 'V')"
'To make this flexible,
'you can loop through count of patterns items instead of hard coded usage
   Dim sqlConnection As New SqlClient.SqlConnection(strDatasource)
   Dim sqlCommand As New SqlClient.SqlCommand(strSqlStatement, sqlConnection)

   sqlConnection.Open()
   Dim rs As SqlClient.SqlDataReader = sqlCommand.ExecuteReader()
   Dim namelist As ArrayList = New ArrayList()

   While rs.Read()
       namelist.Add(rs("name").ToString)
   End While

   rs.Close()

   Dim i As Integer = 0
   Dim count As Integer = 0
   Dim names() As String = _
CType(namelist.ToArray(GetType(String)), String())

   For i = 0 To (names.Length - 1)
       If names(i).ToString <> "" Then
           sqlCommand.CommandText = _
"DROP VIEW [dbo].[" & names(i) & "]"
           count += sqlCommand.ExecuteNonQuery()
       End If
   Next i

   sqlConnection.Close()
   sqlCommand = Nothing
   rs = Nothing
   sqlConnection = Nothing
   System.Console.WriteLine(count & " affected!")
   System.Threading.Thread.Sleep(5000)
   Return

...

This was done quick and dirty as a command line application and so I used the console to write the number of objects actually affected by script. The Thread.Sleep(5000) was used to delay the console screen 5 secs on screen before going away so I could see the result.

This was quick and dirty, so for a production utility, you may want to add other features like exception handling, input validation, and backout capabilities. Input validation would be very key if the program is modified to take a user supplied pattern versus the hard-coded method above. The worry is that someone will send '%' and wipe entire set of views/tables by accident.

Anyway, the script uses the .NET objects for SQL connections to execute a bit of T-SQL which searches the sysobjects table for all the objects that match have a name like 'pattern' and xtype = 'V', restricting search to just views.

Since it is not known the total number to be found, I have opted to use an ArrayList (Vector) approach to allow me to add 0 to many results. Later, the ArrayList is converted to an array of strings which is in turn used in a loop to create dynamic drop statements that are executed on the same SQL server that was queried for the object list in the first place.

Hopefully this helps some poor DBA hitting the delete button 100 times at least once and I will be happy.