Results 1 to 3 of 3

Thread: Views

  1. #1
    Join Date
    May 2004
    Location
    Ohio
    Posts
    61

    Unanswered: Views

    Hi,

    I'm creating a stored procedure that will Drop all views when executed, this is what i have...

    DECLARE crsViews CURSOR FOR
    SELECT
    name AS strViews
    FROM

    __________ <what do i put here?

    WHERE
    type ='U'
    AND NAME LIKE 'V%'


    Thanks for any help!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    I have to wonder why you would need to do such a thing (as drop all views), but you can use this:

    Code:
    select table_name
    from information_schema.views

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    How do you plan to recreate them....better have them all scripted....

    Here's the rope...

    Code:
    
    USE Northwind
    GO
    
    CREATE VIEW myView99
    AS
    SELECT * FROM Orders
    GO
    
    SELECT TOP 10 * FROM MyView99
    GO
    
    DECLARE @SQL varchar(8000)
    
    DECLARE myCursor99 CURSOR
    FOR
    SELECT 'DROP VIEW ' + TABLE_NAME 
      FROM INFORMATION_SCHEMA.Tables
     WHERE TABLE_TYPE = 'VIEW'
       AND TABLE_NAME LIKE 'myV%'
    
    OPEN myCursor99
    
    FETCH NEXT FROM myCursor99 INTO @SQL
    
    WHILE @@FETCH_STATUS = 0
      BEGIN
    	EXEC(@SQL)
    	FETCH NEXT FROM myCursor99 INTO @SQL
      END
    
    CLOSE myCursor99
    DEALLOCATE myCursor99
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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