Drop a table form the hive metastore

delete_table_from_metastore.sql

# DELETE A TABLE IN THE HIVE METASTORE
# BE CAREFUL! BACKUP THE DB BEFORE PROCEEDING!

set @table_name = '';

SELECT @tbl_id := TBl_ID FROM TBLS WHERE TBL_NAME = @table_name;

-- Delete partition key vals
DELETE pvk
FROM PARTITION_KEY_VALS pvk
JOIN PARTITIONS p
ON p.PART_ID=pvk.PART_ID
WHERE p.TBL_ID = @tbl_id;

-- Delete partition params
DELETE pp
FROM PARTITION_PARAMS pp
JOIN PARTITIONS p
ON p.PART_ID=pp.PART_ID
WHERE p.TBL_ID = @tbl_id;

-- Delete partition col stats
DELETE pcs
FROM PART_COL_STATS pcs
JOIN PARTITIONS p
ON p.PART_ID=pcs.PART_ID
WHERE p.TBL_ID = @tbl_id;

-- Delete partition keys
DELETE FROM PARTITION_KEYS WHERE TBL_ID = @tbl_id;

-- Delete bucketing cols
DELETE bc
FROM BUCKETING_COLS bc
JOIN PARTITIONS p
ON bc.SD_ID=p.SD_ID
WHERE p.TBL_ID = @tbl_id;

-- Delete serde params
DELETE SERDE_PARAMS
FROM SERDE_PARAMS
JOIN SDS ON SERDE_PARAMS.SERDE_ID=SDS.SERDE_ID
JOIN PARTITIONS p
ON SDS.SD_ID=p.SD_ID
WHERE p.TBL_ID = @tbl_id;

-- Delete serdes
DELETE SERDES
FROM SERDES
JOIN SDS ON SERDES.SERDE_ID=SDS.SERDE_ID
JOIN PARTITIONS p
ON SDS.SD_ID=p.SD_ID
WHERE p.TBL_ID = @tbl_id;

-- Delete sds AND the partitions
-- Dropped together because partitions reference sds, so sds can't be deleted in isolation
DELETE SDS, p
FROM SDS
JOIN PARTITIONS p
ON SDS.SD_ID=p.SD_ID
WHERE p.TBL_ID = @tbl_id;

-- And the table
DELETE FROM TABLE_PARAMS where TBL_ID = @tbl_id;
DELETE FROM TBL_PRIVS where TBL_ID = @tbl_id;
DELETE FROM TBLS WHERE TBL_ID = @tbl_id;

TBL_COL_PRIVS need to be deleted too:
DELETE FROM TBL_COL_PRIVS where TBL_ID=@tbl_id;