3

I needed to drop all external tables since I needed to re-create an external data source. However, you cannot drop an external data source with external tables that use that particular data source. However, I didn't want to type all tables' name to drop them.

I searched through Stack Overflow, and actually were not able to find an answer for this specific question. I found an answer to a more general question for dropping all tables, and was able to modify the solution. I am making this question to document the solution that I was able to create, so you are wondering about this particular question or use for more general way to drop elements of SQL DB, you can utilize this solution.

Yong Jun Kim
  • 374
  • 5
  • 15

1 Answers1

6

I was able to get this solution by modifying a solution from this answer by Pரதீப்.

DECLARE @sql NVARCHAR(max)=''
SELECT @sql += ' DROP EXTERNAL TABLE ' + QUOTENAME(S.name) + '.' + QUOTENAME(E.name) + '; '
FROM sys.external_tables  E
    LEFT JOIN sys.schemas S
ON E.schema_id = S.schema_id
Exec sp_executesql @sql

However, this solution, though works most of the time, can error out with unexpected characters in the external table names. Therefore, this alternative suggested by Dan Guzman in a comment would be a safer alternative.

DECLARE @SQL nvarchar(MAX) = ( SELECT STRING_AGG(N'DROP EXTERNAL TABLE ' + QUOTENAME(name), N';') + N';' FROM sys.external_tables );
EXECUTE sp_executesql @SQL;
JohnnyHK
  • 305,182
  • 66
  • 621
  • 471
Yong Jun Kim
  • 374
  • 5
  • 15
  • Note that you should use STRING_AGG instead as aggegate string concatenation can yield wrong results, even though it often works. A better query for your situation is `DECLARE @SQL nvarchar(MAX) = ( SELECT STRING_AGG(N'DROP EXTERNAL TABLE ' + QUOTENAME(name), N';') + N';' FROM sys.external_tables ); EXECUTE sp_executesql @SQL;` – Dan Guzman Feb 09 '19 at 23:42
  • Thanks, I read through a documentation on STRING_AGG, and that seems to be a better solution. I will add that to my answer for better visibility. – Yong Jun Kim Feb 09 '19 at 23:48