The database has tables with names like shablon_4YVC , shablon_6HGFT , shablon_9GGNF , etc.

How to make DROP of all such tables, without listing them by name?
Need something like:

 DROP TABLE LIKE 'shablon_%'

    3 answers 3

    In SQL Server, there is such an undocumented procedure:

      exec sp_MSforeachtable @command1 = 'drop table ?', @whereand = 'and o.name like ''%pattern%'' and xtype=''U'''

    However, foreign keys may interfere, as, indeed, in any other way. So first you need to drop reference constraints, and then tables.

      You did not specify which DBMS you are using, therefore I will give an example for MS SQLServer.

      First you need to get a list of tables:

       SELECT name FROM sys.obejcts WHERE name LIKE '%pattern%' AND type = 'U' --пользовательская таблица

      Then go through everything and generate a line for each with the necessary instructions. Then execute this line as a script. In SQLServer, you can do EXEC('...') , but it is better to use sp_execuql .

      Just be extremely careful with such operations, so as not to bang too much.

      • Microsoft SQL Server 2005 - mixerden
      • @mixerden, judging by MSDN, sp_executesql is also in 2005, so try it. - Donil

      I did this:

       USE MyTable SELECT CONCAT('DROP TABLE ', name) AS Result_Command -- Объединил текст и имя таблицы FROM sys.objects WHERE name LIKE '%old' -- выбираю таблицы у которых имя оканчивается на "old" and type = 'U' -- выбираю только пользовательские таблицы ORDER BY name -- сортирую результат по именам таблиц для красоты

      As a result, we get strings of the type DROP TABLE table1_old

      Copy them to the new request window at the beginning by putting the USE MyTable .

      We get something like:

       USE MyTable DROP TABLE table1_old DROP TABLE table2_old DROP TABLE table999_old

      Well, run the script