SQL to delete tables that begins with common prefix

sql drop multiple tables wildcard
mysql drop multiple tables wildcard
drop multiple tables sql server
how to drop multiple tables in oracle
delete multiple tables in sql server management studio
sql drop table if exists
drop table where name like postgres
how to drop multiple tables in mysql

I am looking to write a SQL query/stored procedure which would delete tables within a DB that begin with a common prefix like "table".

However the names of the tables begin with the prefix following by a number and I do not want to delete them all, if I could append the table numbers to the prefix.

Thanks

Find all tables names that start with your prefix and create drop query dynamically and insert result into temp table like this :

DECLARE @YourPrefix VARCHAR(MAX) = 'table'
SELECT    
    'DROP TABLE ' + t.name AS DropQuery
INTO #TMP
FROM        
      sys.tables  t   
WHERE
    t.name LIKE @YourPrefix + '%'

Write cursor on temp table and use sp_executesql to execute drop query like this.

DECLARE @Query NVARCHAR(MAX) = NULL
DECLARE C CURSOR FOR
    SELECT 
        T.DropQuery 
    FROM 
        #TMP T
OPEN C
WHILE 1=1
BEGIN
    FETCH NEXT FROM C INTO @Query
    IF @@FETCH_STATUS <> 0 BREAK;
    EXEC sp_executesql @Query
END
CLOSE C
DEALLOCATE C

It,s drop all table that start with your prefix.

How to drop multiple tables with common prefix in one query , You can build up a string using the catalog views, e.g.: DECLARE @sql NVARCHAR(MAX) = N''; SELECT @sql += ' DROP TABLE ' + QUOTENAME(s. name) + '� Solution: yes, it is possible to drop all those tables that have the common prefix in a single query. Using the following query, you can delete all those tables that begin with a certain prefix string. In where condition just put the common prefix string in where condition (Like 'prefix%') DECLARE @query NVARCHAR(MAX) = N'';

look at this: Get table names using SELECT statement in MySQL

you could join that table with constraints on the table prefix

hope that helps and have fun

Drop all tables with some prefix - Databases, What would be the corresponding SQL for: DROP TABLES LIKE 'test_%'; I just want to delete all the tables that have 'test_' as a prefix. Sometimes in your projects, you may be using staging tables for purposes like loading ,parsing and cleaning data. Suppose you name all of these table with the prefix tmp_ and after these tables have served their purpose, you now want to drop all these tables.

Find all tables where column like:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       c.name LIKE '%SubId%'
ORDER BY    TableName
            ,ColumnName;

Find all tables where table name like:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name LIKE '%tbl%'
ORDER BY    TableName
            ,ColumnName;

Find all tables in a specific schema:

SELECT t.name 
  FROM sys.tables AS t
  INNER JOIN sys.schemas AS s
  ON t.[schema_id] = s.[schema_id]
  WHERE s.name = N'cmc';

Once you have the tables you need to delete you can just write delete statements for each one. Or you could use something like the below:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName',
            'drop table ' + t.name
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       t.name LIKE '%tbl%'
ORDER BY    TableName
            ,ColumnName;

EDIT

Below a select with a little more detail on the where clause:

SELECT      c.name  AS 'ColumnName'
            ,t.name AS 'TableName'          
FROM        sys.columns c
JOIN        sys.tables  t   ON c.object_id = t.object_id
WHERE       
            t.name LIKE 'tbl%' -- where the table name starts with the letters 'tbl'
            OR t.name LIKE 'tbl%123%' -- where the table name starts with the letters 'tbl' and has the numbers '123' in the table name
            OR c.name LIKE '%colName%' -- where a column has a name that contains the letters 'colName'

ORDER BY    TableName
            ,ColumnName;

Sql prefix table name, COMMON_USER_PREFIX specifies a prefix that the names of common users, roles, TABLES where table_name like 'tmp_%' The above query generates drop SQL Server will look for a stored procedure that begins with a sp_ prefix in the� Find all tables names that start with your prefix and create drop query dynamically and insert result into temp table like this : DECLARE @YourPrefix VARCHAR(MAX) = 'table' SELECT 'DROP TABLE ' + t.name AS DropQuery INTO #TMP FROM sys.tables t WHERE t.name LIKE @YourPrefix + '%'

How do I delete (drop) tables? - Manage data, You can drop one or more tables by using the DROP TABLE command, which can be executed from the Analyze page or any connected SQL client. to drop all tables with a specific prefix: WHERE tablename like 'prefix%' . The SQL DROP TABLE statement is used to remove a table definition and all the data, indexes, triggers, constraints and permission specifications for that table. NOTE − You should be very careful while using this command because once a table is deleted then all the information available in that table will also be lost forever.

MySQL Tutorial - MySQL By Examples for Beginners, For detailed syntax, check MySQL manual "SQL Statement Syntax" DROP DATABASE IF EXISTS databaseName -- Delete if it exists CREATE DATABASE Let us begin with a simple example - a product sales database. The most common, important and complex task is to query a database for a subset of data that� SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL

Drop or Keep Variables with Same Suffix in SAS, Simply use the colon operator along with the common prefix in a drop or keep Then, I use the dictionary.columns table to extract all variable names, that has this %let suffix=var; proc sql noprint; select name into :vars separated by ' ' from � Query below finds tables which names start with specific prefix, e.g. tables with names starting with 'hr'. Query select schema_name(t.schema_id) as schema_name, t.name as table_name from sys.tables t where t.name like 'hr%' order by table_name, schema_name; Columns. schema_name - name of schema table was found in

Comments
  • Can you expand on 'I do not want to delete them all'
  • I don't want to delete every single table I have which contains the prefix, as there are hundreds of them, I want to delete the tables which contain the prefix and the numbers appended
  • Is there a rule to establish which to delete or which to keep?
  • How would these work if I wanted to find the table names using this however filtering the tables which have an ID that matches one I specify found within one of the columns as well
  • I am not sure if I understand you completely. If the edit to my answer does not help you then please clarify your question but adding some more information and examples of what you are trying to do.