Create "loop" to drop tables with specific names/criteria-CodePudding

Currently I am trying to drop multiple tables from my database. But only those which names beginning with specific string, or tables names match exactly my wildcard/underscore criteria.

In Oracle I used something like that and I am looking for something equivalent to it in SQL Server Management Studio:

begin
  for i in (select TABLE_NAME 
              from  USER_TABLES 
              where TABLE_NAME = 'TABLE_____NAME'
             )
  loop
    execute immediate 'DROP TABLE '||i.TABLE_NAME||' CASCADE CONSTRAINTS PURGE';
  end loop;             
end;

As I started to use SQL Server I began to realize, that SQL Server does not offer simples loops.

I managed to create something like that:

SELECT 'IF OBJECT_ID('''  TABLE_NAME   ''') IS NOT NULL BEGIN DROP TABLE ['   TABLE_NAME   '] END;' FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME LIKE 'TABLE_____NAME'

...which gave me at least a list to copy-paste. I assume that solution above is a but overkill in term of checking the object id, but it works fine. I'm not sure if checking for the Object id is necessary at all.

In the end, I am seeking for a simpler solution, mainly for a loop-like solution like I use to do in Oracle. I never know how many table it will be, so a counter solution (which I find often during research) is not an option (at least as far I understand).

Any ideas? Thank you in advance.

Does anyone know how to realise the Oracle loop in SQL Server to work

CodePudding user response：

Dropping tables is presumably a one-time process and something you really want to be sure about.

I normally just dynamically generate a list of drop statements which you can then paste directly into a Management Studio query window to visually confirm they are correct and then you can just execute the script.

select Concat_Ws(' ','drop table', QuoteName(name), ';')
from sys.tables
where name like 'TABLE_____NAME'

CodePudding user response：

I would use "while" to iterate through the script texts extracted by the identity column in the prepared script table.

Declare @RowNumber As Int = 1
Declare @Cnt As Int
Declare @schema_name sysname = '<your schema>'
Declare @QueryText nVarChar(max)

Create Table #Script (ID Int Identity(1,1), QueryText nVarChar(max))

Insert Into #Script (QueryText)
Select Concat(N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''',
                          Quotename(@schema_name), N'.',Quotename([name]),N''') AND type in (N''U'')) DROP TABLE ',
                          Quotename(@schema_name), N'.',Quotename([name]))
From sys.tables 
Where [name] Like 'TABLE_____NAME'

Select @Cnt = Count(*) From #Script

While (@RowNumber<=@Cnt)
    Begin
        Select @QueryText = QueryText From #Script Where ID=@RowNumber

        Exec sp_executesql @QueryText

        RAISERROR ('%s', 0, 1, @QueryText) WITH NOWAIT

        Select @RowNumber=@RowNumber 1
    End

or you can use string_agg to get a script to delete all tables and run it in one go without using a loop.

Declare @QueryText nVarChar(max)
Declare @max nVarChar(max) = ''
Declare @schema_name sysname = '<your schema>'

Select @QueryText=String_Agg(Concat(@max, N'IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N''',
                                           Quotename(@schema_name), N'.',Quotename([name]),N''') AND type in (N''U'')) DROP TABLE ',
                                           Quotename(@schema_name), N'.',Quotename([name])), ' ')
From sys.tables 
Where [name] Like 'TABLE_____NAME'

Execute sp_executesql @QueryText