Results 1 to 3 of 3

Thread: sql in field

  1. #1
    Join Date
    Feb 2003
    Posts
    11

    Unanswered: sql in field

    i am trying to run the following but my understanding of sql is not that strong.

    declare @deletetable varchar(50)
    DECLARE abc CURSOR FOR
    select + name + ''name from sysobjects where name like '%del'
    OPEN abc
    FETCH NEXT FROM abc
    INTO @deletetable
    drop table @deletetable
    WHILE (@@FETCH_STATUS = 0)
    begin
    FETCH NEXT FROM abc
    INTO @deletetable
    drop table @deletetable
    end
    CLOSE abc
    DEALLOCATE abc
    GO


    im trying to run the first sql to give me a list of tables that match the criteria, then in the loop im trying to drop each table

    is there an easier sql to do this?

  2. #2
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    Why not use INFORMATION_SCHEMA.TABLES instead of using cursors against system tables.
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No, if you want your script to both compose and execute the SQL, I think you need to use a cursor. I also think your cursor logic needs some work, something like:
    PHP Code:
    DECLARE @t NVARCHAR(500)

    DECLARE 
    zList CURSOR FOR SELECT '[' TABLE_SCHEMA '].[' TABLE_NAME ']'
       
    FROM INFORMATION_SCHEMA.TABLES
       WHERE TABLE_NAME LIKE 
    '%del'

    OPEN zList
    FETCH zList INTO 
    @t

    WHILE = @@fetch_status
       BEGIN
          EXECUTE 
    ('DROP TABLE ' + @t)
          
    FETCH zList INTO @t
       END

    CLOSE zList
    DEALLOCATE zList 
    This will get the list of tables whose names end in "del", including the owner names and drop those tables for you.

    -PatP

Posting Permissions

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