How to Drop Multiple Tables in Postgresql Using a Wildcard

How to drop multiple tables in PostgreSQL using a wildcard

Use a comma separated list:

DROP TABLE foo, bar, baz;

If you realy need a footgun, this one will do it's job:

CREATE OR REPLACE FUNCTION footgun(IN _schema TEXT, IN _parttionbase TEXT) 
RETURNS void 
LANGUAGE plpgsql
AS
$$
DECLARE
    row     record;
BEGIN
    FOR row IN 
        SELECT
            table_schema,
            table_name
        FROM
            information_schema.tables
        WHERE
            table_type = 'BASE TABLE'
        AND
            table_schema = _schema
        AND
            table_name ILIKE (_parttionbase || '%')
    LOOP
        EXECUTE 'DROP TABLE ' || quote_ident(row.table_schema) || '.' || quote_ident(row.table_name) || ' CASCADE ';
        RAISE INFO 'Dropped table: %', quote_ident(row.table_schema) || '.' || quote_ident(row.table_name);
    END LOOP;
END;
$$;

SELECT footgun('public', 'tablename');

drop all tables sharing the same prefix in postgres

To do this in one command you need dynamic SQL with EXECUTE in a DO statement (or function):

DO
$do$
DECLARE
   _tbl text;
BEGIN
FOR _tbl  IN
    SELECT quote_ident(table_schema) || '.'
        || quote_ident(table_name)      -- escape identifier and schema-qualify!
    FROM   information_schema.tables
    WHERE  table_name LIKE 'prefix' || '%'  -- your table name prefix
    AND    table_schema NOT LIKE 'pg\_%'    -- exclude system schemas
LOOP
   RAISE NOTICE '%',
-- EXECUTE
  'DROP TABLE ' || _tbl;  -- see below
END LOOP;
END
$do$;

This includes tables from all schemas the current user has access to. I excluded system schemas for safety.

If you do not escape identifiers properly the code fails for any non-standard identifier that requires double-quoting.

Plus, you run the risk of allowing SQL injection. All user input must be sanitized in dynamic code - that includes identifiers potentially provided by users.

Potentially hazardous! All those tables are dropped for good. I built in a safety. Inspect the generated statements before you actually execute: comment RAISE and uncomment the EXECUTE.

If any other objects (like views etc.) depend on a table you get an informative error message instead, which cancels the whole transaction. If you are confident that all dependents can die, too, append CASCADE:

  'DROP TABLE ' || _tbl || ' CASCADE;

Closely related:

• Update column in multiple tables
• Changing all zeros (if any) across all columns (in a table) to... say 1

Alternatively you could build on the catalog table pg_class, which also provides the oid of the table and is faster:

...
FOR _tbl  IN
    SELECT c.oid::regclass::text  -- escape identifier and schema-qualify!
    FROM   pg_catalog.pg_class c
    JOIN   pg_catalog.pg_namespace n ON n.oid = c.relnamespace
    WHERE  n.nspname NOT LIKE 'pg\_%'     -- exclude system schemas
    AND    c.relname LIKE 'prefix' || '%' -- your table name prefix
    AND    c.relkind = 'r'                -- only tables
...

System catalog or information schema?

• How to check if a table exists in a given schema

How does c.oid::regclass defend against SQL injection?

• Table name as a PostgreSQL function parameter

Or do it all in a single DROP command. Should be a bit more efficient:

DO
$do$
BEGIN
   RAISE NOTICE '%', (
-- EXECUTE (
   SELECT 'DROP TABLE ' || string_agg(format('%I.%I', schemaname, tablename), ', ')
   --  || ' CASCADE' -- optional
   FROM   pg_catalog.pg_tables t
   WHERE  schemaname NOT LIKE 'pg\_%'     -- exclude system schemas
   AND    tablename LIKE 'prefix' || '%'  -- your table name prefix
   );
END
$do$;

Related:

• Is there a postgres command to list/drop all materialized views?

Using the conveniently fitting system catalog pg_tables in the last example. And format() for convenience. See:

• How to check if a table exists in a given schema
• Table name as a PostgreSQL function parameter

How to merge multiple tables in postgreSQL (using wildcard)

As others have mentioned, there is no mechanism to prepend columns without dropping/recreating tables or individual columns.

To append columns to every table that starts with "m", you can use an anonymous plpgsql code block:

DO
$$
DECLARE tbl_ident regclass;
BEGIN
FOR tbl_ident IN
SELECT
  oid::regclass fqn --this will give you schema qualified, properly quoted table name
FROM
   pg_class
WHERE
  'my_schema'::regnamespace::oid = relnamespace --pick schema(s) you care about
  and relkind = 'r' --ordinary tables only. Does not include views (v),materialized views (m),partitioned tables (p) or foreign tables (f)
  and relname ilike 'm%'
LOOP
  -- append the columns you want to the tables
  EXECUTE 'ALTER TABLE ' || tbl_ident || ' ADD COLUMN col1 integer, ADD COLUMN col2 varchar(1337)';
END LOOP;
END$$

How to drop multiple tables in Big query using Wildcards TABLE_DATE_RANGE()?

I just used python to loop this and solve it using Graham example:

 from subprocess import call

   return_code = call('bq  rm -f -t dataset.' + table_name +'_'+  period + '', shell=True)

PostgreSQL, drop tables with query

You need to use dynamic SQL for this, which in turn can only be used in a procedural language like PL/pgSQL, something like this:

do
$$
declare
   stmt text;
   table_rec record;
begin
   for table_rec in (SELECT c.relname as tname
                     FROM pg_catalog.pg_class c 
                       LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace 
                     WHERE c.relkind IN ('r','') 
                       AND n.nspname NOT IN ('pg_catalog', 'pg_toast') 
                       AND pg_catalog.pg_table_is_visible(c.oid))
   loop
     execute 'drop table '||table_rec.tname||' cascade';
   end loop;
end;
$$

Create loop to drop tables with specific names/criteria

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

Drop all tables whose names begin with a certain string

You may need to modify the query to include the owner if there's more than one in the database.

DECLARE @cmd varchar(4000)
DECLARE cmds CURSOR FOR
SELECT 'drop table [' + Table_Name + ']'
FROM INFORMATION_SCHEMA.TABLES
WHERE Table_Name LIKE 'prefix%'

OPEN cmds
WHILE 1 = 1
BEGIN
    FETCH cmds INTO @cmd
    IF @@fetch_status != 0 BREAK
    EXEC(@cmd)
END
CLOSE cmds;
DEALLOCATE cmds

This is cleaner than using a two-step approach of generate script plus run. But one advantage of the script generation is that it gives you the chance to review the entirety of what's going to be run before it's actually run.

I know that if I were going to do this against a production database, I'd be as careful as possible.

Edit Code sample fixed.

Drop multiple tables in one shot in MySQL

We can use the following syntax to drop multiple tables:

DROP TABLE IF EXISTS B,C,A;

This can be placed in the beginning of the script instead of individually dropping each table.

---

Related Topics