drop user tables

• ramyours2003

  SSChampion

  Points: 12268

  More actions

  October 26, 2010 at 2:54 am

  #228343

  how to drop all user tables with respect to a particular database

• Adiga

  One Orange Chip

  Points: 27284

  More actions

  October 26, 2010 at 3:56 am

  #1242116

  Please take a FULL database backup before dropping the tables.

  You may use the below query to delete USER tables from the database.

  DECLARE @TABLENAME VARCHAR(256)

  DECLARE @QRY VARCHAR(256)

  DECLARE TABCURSOR CURSOR FOR

  SELECT NAME FROM SYS.TABLES

  OPEN TABCURSOR

  FETCH NEXT FROM TABCURSOR INTO @TABLENAME

  WHILE @@FETCH_STATUS=0

  BEGIN

  SET @QRY= 'DROP TABLE ' + @TABLENAME

  PRINT @QRY

  EXEC @QRY

  FETCH NEXT FROM TABCURSOR INTO @TABLENAME

  END

  CLOSE TABCURSOR

  DEALLOCATE TABCURSOR

  Pradeep Adiga
  Blog: sqldbadiaries.com
  Twitter: @pradeepadiga

• Lowell

  SSC Guru

  Points: 323546

  More actions

  October 26, 2010 at 5:01 am

  #1242138

  Adiga's code example should be tweaked just a little bit; if that was run in master(where way to many user tables get accidentally created), you could delete some spt^ tables that the system uses.

  the cursor should avoid tables where is_ms_shipped=1

  select name from sys.tables where is_ms_shipped=0

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!