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_NAMEIsso 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_NAMEIsso 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 ?' GOVocê pode também transformar esse código em uma stored procedure.
Have fun ;)