Delete All Tables MSSQL

Share |

While working on ASP.Net application, I came across a situation where I had to delete all the Tables of the MSSQL 2008 database, and it was really painful process. 

In this case I had to delete all the SPs and all views as well. As I started searching like "Truncate database Sql 2008" or "Delete all tables in MSSQL" I came across two things and it worked for me.

(I am really not sure whether it works in all the cases but for Deleting all tables in SQL, this worked for me.) 

Firstly we need to remove all indexs from SQL table, I found following code when I Google the term 

DECLARE @indexName NVARCHAR(128)
DECLARE @dropIndexSql NVARCHAR(4000)

DECLARE tableIndexes CURSOR FOR
SELECT name FROM sysindexes
WHERE id = OBJECT_ID(N'tableName') AND
  indid > 0 AND indid < 255 AND
  INDEXPROPERTY(id, name, 'IsStatistics') = 0
ORDER BY indid DESC

OPEN tableIndexes
FETCH NEXT FROM tableIndexes INTO @indexName
WHILE @@fetch_status = 0
BEGIN
  SET @dropIndexSql = N'DROP INDEX tableName.' + @indexName
  EXEC sp_executesql @dropIndexSql

  FETCH NEXT FROM tableIndexes INTO @indexName
END

CLOSE tableIndexes
DEALLOCATE tableIndexes

After this we need to delete all the tables in SQL, for this I used following SP

EXEC sp_MSforeachtable @command1 = "DROP TABLE ?"

It seems that this is a HIDDEN stored procedure in MSSQL.