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
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----