Results 1 to 10 of 10
  1. #1
    Join Date
    Oct 2008
    Posts
    46

    Unanswered: How to drop tables

    Hello,
    Anybody can fixe the problems. I have multiple database. Db name like EE% and each db has tables like RE%.
    Fleas find out the following err.

    RaJ

    -----------------

    USE Master
    GO

    DECLARE @name VARCHAR(50)

    DECLARE @sql NVARCHAR(4000)

    DECLARE dbcursor CURSOR FOR

    SELECT [name]
    FROM sys.Databases
    Where [name] LIKE 'EE%'

    OPEN dbcursor
    FETCH NEXT FROM dbcursor INTO @name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    print 'Db Name is : ' + @name

    WHILE EXISTS (SELECT TOP 1 * FROM @name .sys.tables WHERE [name] LIKE 'RE%')
    BEGIN
    SET @sql = 'DROP TABLE DOB.[' + (SELECT TOP 1 [name] FROM @name .sys.tables WHERE [name] LIKE 'RE%') + ']'
    EXEC (@sql)
    END

    FETCH NEXT FROM dbcursor INTO @name
    END

    CLOSE dbcursor
    DEALLOCATE dbcursor

  2. #2
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    may have something to do with DOB - should be DBO probably. also you should use 3 part naming. also this is may have unintended consequences - do you really want to drop every table starting with RE? even the REALLY_IMPORTANT_TABLE table?

    when I do things like this I use PRINT instead of EXEC. then proofread the result, make sure it's what you want, then paste it into query analyzer and execute it.

  3. #3
    Join Date
    Oct 2008
    Posts
    46
    Thanks for response.

    Yes i have to drop all the tables like RE% in db @name
    I have more than 40-50 databases. I have to backup each db like EE% after backup drop the tables.

  4. #4
    Join Date
    Oct 2008
    Posts
    46

    How to refer to a databasename dynamically on SQL Server 2005

    I have to drop the tables from dynamic database.
    The following @name gives database name
    So i can not get record from @name db. I check i have a tables on the @name.

    WHILE EXISTS (SELECT TOP 1 * FROM @name.sys.tbles WHERE [name] LIKE 'REL%')


    Pleas anybody know how to pass @name in <database_name>.sys.tables

    Thankx
    ------------
    USE Master
    GO

    DECLARE @name VARCHAR(50)
    DECLARE @sql NVARCHAR(4000)



    DECLARE db_cursor CURSOR FOR

    SELECT [name]
    FROM sys.Databases
    Where [name] LIKE 'EDDS%'

    OPEN db_cursor
    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    print 'Db Name is : ' + @name

    (SELECT TOP 1 * FROM @name.sys.objects WHERE [name] LIKE 'REL%')

    WHILE EXISTS (SELECT TOP 1 * FROM @name.sys.tbles WHERE [name] LIKE 'REL%')
    BEGIN
    SET @sql = 'DROP TABLE DBO.[' + (SELECT TOP 1 [name] FROM @name.sys.objects WHERE [name] LIKE 'REL%') + ']'
    EXEC (@sql)
    END

    FETCH NEXT FROM db_cursor INTO @name
    END

    CLOSE db_cursor
    DEALLOCATE db_cursor

  5. #5
    Join Date
    Oct 2008
    Posts
    46
    No body has solution??

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How many databases? Are the number growing? This is much easier not to automate. It would also be an indication of possible bad practice if you need to.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Code:
    DECLARE    @s AS VARCHAR(MAX)
    
    SELECT    @s    = COALESCE(@s, '') + 'DROP TABLE [' + SCHEMA_NAME(schema_id) + '].[' + name + '];'
    FROM    sys.tables
    WHERE    name LIKE 'rel%'
    
    PRINT    @s
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I agree - this is a sign of very poor design...

    But for my 2 cents: sp_msforeachtable is another option.
    George
    Home | Blog

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hmmm. Forgot about that. Whatever the case, looks like it's going to involve nested dynamic sql lol.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Actually, didn't realise this method would be so icky... Obviously change PRINT to DROP TABLE.
    Code:
    EXEC sp_msforeachtable 'IF ''?'' LIKE ''%._rel%'' PRINT ''?'''
    George
    Home | Blog

Posting Permissions

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