Deletar todas as tabelas de um banco SQL Server

As vezes, quando estamos desenvolvendo software, precisamos simplesmente apagar todas as tabelas e recriá-las novamente, mas isso é impossível de se fazer caso existam chaves estrangeiras e outras constraints ou dependências dessas tabelas.

Você pode ter tentado o comando EXEC sp_MSForEachtable 'DROP TABLE ?', que é uma stored procedure não documentada que já vem de fábrica com o SQL e que executa um mesmo comando em cada tabela do banco de dados, substituindo ? pelo nome da tabela.
Mais sobre sp_MSForEachtable

Mesmo a sp_MSForEachtable não funcionaria para todas as tabelas. O ideal é que se apague tudo na ordem correta, ou se recrie o banco. Entretanto, se você deseja apagar as tabelas mas permanecer com suas procedures, por exemplo, e rodar o script para recriar as tabelas depois, você pode fazer o seguinte:

1) Conheça a view INFORMATION_SCHEMA.TABLE_CONSTRAINTS. 
Essa é uma  view de sistema que mostra os metadados do banco. No caso, ela lista todas as constraints relacionando-as com as tabelas que as possuem. Nela você pode ver todas as chaves primárias e estrangeiras.
Mais sobre INFORMATION_SCHEMA.TABLE_CONSTRAINTS

2) Conheça a view INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS.
Essa view de sistema lista todas as tabelas referenciadas por constraints, ou seja, é o lado "references" de uma chave estrangeira.
Mais sobre INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS

3) Faça o join das duas pelo campo CONSTRAINT_NAME

SELECT DISTINCT 
  tc2.TABLE_NAME,
  rc1.CONSTRAINT_NAME
 FROM 
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 
    ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME

Isso te dará uma lista de todas as chaves estrangeiras.

4) Gere um script para apagar as fk's Agora você pode construir um script para apagar cada uma das chaves estrangeiras concatenando da seguinte forma:

 SELECT DISTINCT 
  sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
 FROM 
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 
    ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME

Isso gerará uma coluna de resultado chamada SQL com várias linhas de comandos para apagar cada uma das fk's. Você pode dar um copiar/colar nesse script, ou executá-lo via cursor, da seguinte forma:

--deleta todas as FK's

DECLARE @Sql NVARCHAR(500) 
DECLARE @Cursor CURSOR

--cria um cursor para varrer o resultset
SET @Cursor = CURSOR FAST_FORWARD FOR
 SELECT DISTINCT 
  sql = 'ALTER TABLE [' + tc2.TABLE_NAME + '] DROP [' + rc1.CONSTRAINT_NAME + ']'
 FROM 
  INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc1 LEFT JOIN 
   INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc2 
    ON tc2.CONSTRAINT_NAME = rc1.CONSTRAINT_NAME
--atribui, a cada iteração do cursor, o valor do campo SQL na variável varchar @SQL
OPEN @Cursor FETCH NEXT FROM @Cursor INTO @Sql

WHILE (@@FETCH_STATUS = 0)

BEGIN

 --executa a string @SQL
 Exec SP_EXECUTESQL @Sql
 --Avança para o próximo cursor
 FETCH NEXT FROM @Cursor INTO @Sql

END

--fecha e desaloca o cursor
CLOSE @Cursor DEALLOCATE @Cursor

GO

--agora sim consegue executar sp_MSForEachtable e apagar todas as tabelas
EXEC sp_MSForEachtable 'DROP TABLE ?'

GO

Você pode também transformar esse código em uma stored procedure.

Have fun ;)