Drop External Table, Data Source, File Format and List Metadata for Auditing | Azure Synapse Analytics Tutorial

📘 Azure Synapse - Drop & Inspect External Objects Script

Author: Aamir Shahzad

Purpose: Drop external table, data source, file format and list metadata for auditing

Usage: Use in Synapse Serverless or Dedicated SQL pools for external object maintenance

---

🔻 Drop External Objects

Drop External Table (if exists)

IF OBJECT_ID('dbo.euuser', 'U') IS NOT NULL
    DROP EXTERNAL TABLE dbo.users;

Drop External File Format (if exists)

IF OBJECT_ID('SynapseDelimitedTextFormat', 'U') IS NOT NULL
    DROP EXTERNAL FILE FORMAT SynapseDelimitedTextFormat;

Drop External Data Source (if exists)

IF OBJECT_ID('synpasecontainer_techbrotherssynapsestg_dfs_core_windows_net', 'U') IS NOT NULL
    DROP EXTERNAL DATA SOURCE synpasecontainer_techbrotherssynapsestg_dfs_core_windows_net;

---

📋 Inspect External Object Metadata

🔍 List All External Tables

SELECT * 
FROM sys.external_tables;

🔍 List All External Data Sources

SELECT * 
FROM sys.external_data_sources;

🔍 List All External File Formats

SELECT * 
FROM sys.external_file_formats;

🔍 List Columns of a Specific External Table

SELECT 
    TABLE_NAME     AS TableName,
    COLUMN_NAME    AS ColumnName,
    ORDINAL_POSITION,
    DATA_TYPE,
    CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'users';

---

📺 Watch the Full Tutorial

Explore the full walkthrough in this YouTube video:

This blog post was created with assistance from ChatGPT and Gemini AI to ensure accuracy and clarity.