SQL Server Performance

Generating Database objects script

Discussion in 'ALL SQL SERVER QUESTIONS' started by AJITH123, Feb 7, 2012.

  1. AJITH123 Member

    Guys,
    How do I script out all the database objects with IF EXISTS clause. I need the script like below

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Table]') AND type in (N'U'))
    DROP TABLE [dbo].[Table]
    GO
  2. Luis Martin Moderator

    Any problem using Management Studio (script database wizard)?
  3. AJITH123 Member

    I don't think i can get like "IF EXISTS (...." it gives IF NOT EXISTS (
  4. davidfarr Member

    Note that your syntax example above will not drop all database objects, it will only drop user-created tables ( because you specified type in (N'U') ). Everything else (system objects, stored procs, views,...) will remain.
    Also note that any tables that are referenced by a foreign key will not drop if the referencing table still exists.

    I'm a little puzzled by what you are trying to achieve by dropping objects in that fashion.
    It might be easier to just drop the database and recreate it.
    Anyway, to answer your question, see below.

    --- DISCLAIMER: The script below drops all user-created tables in the database where it is executed.
    -- Use with caution and at your own risk --

    DECLARE @ObjName nvarchar(256)
    DECLARE one_cursor CURSOR LOCAL FORWARD_ONLY READ_ONLY FOR
    select [name] from sysobjects where [type]='U'
    OPEN one_cursor
    FETCH NEXT FROM one_cursor INTO @ObjName
    WHILE @@FETCH_STATUS = 0
    BEGIN
    -- EXECUTE ('DROP TABLE ['+@ObjName+']') --this line has been commented for safety.
    FETCH NEXT FROM one_cursor INTO @ObjName
    END
    CLOSE one_cursor
    DEALLOCATE one_cursor
    GO
    ------End of script----

Share This Page