CODEWITHSUNDEEP
sql-server-2005drop-table
Relativity
Relativity

Reputation: 6868

How to delete all tables with prefix "bkp" from a given database?

I have a SQL server 2005. In that server I have 3 databases -> a,b,c.

If I want to delete tables

  1. Tables only from database "c".
  2. The table's name should start with "bkp"
  3. Table should be created one day before.

Upvotes: 2

Views: 3039

Answers (1)

marc_s
marc_s

Reputation: 754468

Try this:

USE C
GO

SELECT
'DROP TABLE ' + name
FROM sys.tables
WHERE create_date >= '20101211'   -- substitute your date you're interested in
AND name like 'bkp%'

This will create as output a list of DROP TABLE:.... statement - copy those and paste them into a new SSMS window and execute those - and you're done!

Upvotes: 5

Related Questions