Automatic delete tables in dB.

WeTi
WeTi used Ask the Experts™
on
Dear expert

I would like to delete a dB table using a query, I would like to delete this bkcopy_* table. I already got one procedure to create one:
USE [TEST_dB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [bkcopy_test]
AS
DECLARE @datum CHAR(10) = CONVERT(CHAR(8), GETDATE(), 112);
exec ('SELECT * INTO [TEST_dB_'+ @datum +'] FROM [Test]' 

Open in new window

Above query create one table with a copy content of that test table, now I would like to first check if the table exist, if it exist then delete that table with name: Test_bB* Is this possible to do?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Look into the IF EXISTS clause of DROP TABLE:
https://docs.microsoft.com/en-us/sql/t-sql/statements/drop-table-transact-sql?view=sql-server-ver15

Should be something like:
exec ('DROP TABLE IF EXISTS [TEST_dB_'+ @datum +']' )

Author

Commented:
Well the [TEST_dB_'+ @datum +']' is not the same date when we create and delete, for exemple: week no1. Bkcopy runs, and Week no2 runs again and to delete the last week table. Is there a way to write: DROP TABLE IF EXISTS [TEST_dB_*]?
Partha MandayamTechnical Director

Commented:
Please note that "IF EXISTS" clause only works for sql server 2016 and above.
Which sql server do you have?
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Is there a way to write: DROP TABLE IF EXISTS [TEST_dB_*]?

Imagine the danger of allowing dropping of objects using wildcards?

You can query INFORMATION_SCHEMA.TABLES to get a list of table names using wildcards then in a loop drop them.

Author

Commented:
Its 2016 and Yes I am aware that its danger, but is it possibe to do?
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Sorry I didn't explicitly say this but:  No.

You'll need to use the loop I suggested.

Author

Commented:
IF EXIST, didn't work out because the version of server, wildcard not  working as well. INFORMATION_SCHEMA is a good thing :) That worked. Thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial