Results 1 to 8 of 8

Thread: Deleting tables

  1. #1
    Join Date
    Oct 2002
    Location
    chennai
    Posts
    30

    Question Unanswered: Deleting tables

    Hi all,

    Can any one tell me the query which can delete the tables which start with the same name. I mean, if T1, T2, T3, T4, T5.......Tn are the table names. How to delete all these tables with a single query.
    Thanks,
    sekhar rao k

  2. #2
    Join Date
    Jan 2003
    Location
    Paris, France
    Posts
    320

    Re: Deleting tables

    Originally posted by k_sekhar_rao
    Hi all,

    Can any one tell me the query which can delete the tables which start with the same name. I mean, if T1, T2, T3, T4, T5.......Tn are the table names. How to delete all these tables with a single query.
    You can use sysobjects tables to list all table begining by Tn and delete it.
    Olivier Miossec
    --
    http://www.lasso-developpeur.net/
    --

  3. #3
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    I don't know of a single statment that can drop multipule table but you could use somthing like this:

    Code:
    Declare @TblName sysname
    select @TblName = min(name) from sysobjects where type = 'U' and name like 'T_'
    while (@TblName is not null) begin
      raiserror('Dropping table: %s...',0,1,@TblName)
      exec('drop table ' + @TblName)
      select @TblName = min(name) from sysobjects where type = 'U' and name like 'T_' and name > @TblName
    end
    Paul Young
    (Knowledge is power! Get some!)

  4. #4
    Join Date
    Jan 2003
    Posts
    55
    Hmm im not muhc of an expert compared the many people here but i think tis wud also work

    Delete from sysobjects s
    where s.name ike "T%"

    lemme know if it works..:s..just trying to be of some help so dun kil me if it doesnt!..

    cheers

    -s
    You try and try again..but then give up, there's no sense in being a complete fool about it!!!

  5. #5
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    shuchi did you test your suggestion?
    Paul Young
    (Knowledge is power! Get some!)

  6. #6
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    shuchi, you are probably hardcore programmer, in MSSQL this approach is lethal. You delete primary records of objects. I expect this can be done after allowing direct updates to system catalogs. But what will you do with dependences of deleted objects, raw data on harddisc, ... ?

  7. #7
    Join Date
    Feb 2003
    Posts
    4
    Paul Young gaved a good sugestion. i also sugest using 'and status>0'. I found that there r still tables marked as 'U' (user) that are in fact not user-made, but those have the status<0. The documentation says 'status' is 'for internal purpose and doesn't go more further.

  8. #8
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    You mean table dbo.dtproperties. I assume it is more a bug than a plan.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •