2

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.
Kara
  • 6,115
  • 16
  • 50
  • 57
Relativity
  • 6,690
  • 22
  • 78
  • 128

1 Answers1

5

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!

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • My Doubt is...Sys.Tables is common for all databases right. So ...will it delete from databases ? – Relativity Dec 12 '10 at 21:25
  • @Anish: **no it's not** - `sys.tables` is specific to the database you're in - when you do `USE C` you get only the tables that are in database C. – marc_s Dec 12 '10 at 21:32