Query SQL database to obtain information about Azure blob and selectively delete

Say I have some files: file1.txt file2.jpg file3.mp4 etc

I have some code which does the following.

1. Renames the file to something with a unique GUID and without the suffix.
   e.g. file1.txt -> 970ba8ec6e6e4571a3865aa46b13d51fba19cfc0956b4a7eb20b2e7f535e99a

2. Creates an entry into a specific table (inventory) in a MS Azure SQL database (mappings) with the following columns:

   • ID
   • Date
   • GUID - Primary Key
   • File Name
   • Storage Location_

   ...where Storage Location is the path to a Blob Container in a Blob of a Storage Account in MS Azure.

3. Once the entry exists in SQL the file is moved to the specified Azure Storage Location where it exists just as the GUID.

From time to time we need to perform some file actions. For instance, a user might leave and we want to delete all of the files that they own from the Azure storage. What we want to do is this.

4. Run T-SQL query WHERE ID is the Users ID to create a temporary table with two columns containing the GUID and the Storage Location.
5. For each row in the temporary table, to delete the file from the Storage Location.

Is there a way to perform the Storage Location delete file operation from within a T-SQL query, please? We can write all of the T-SQL needed to create the temporary table, but we are struggling to know whether we can include the blob storage file delete operation in the same T-SQL code.

You cannot directly delete files from Azure Blob Storage using T-SQL alone, but you can use T-SQL to generate a table of the relevant data (GUID and Storage Location) and then export that to a file (e.g., CSV), which a PowerShell script can consume to perform the deletions.

1. Generate the temp table in T-SQL
2. Export the result to a CSV file with PowerShell script like below example:

Invoke-Sqlcmd -Query "SELECT GUID, [Storage Location] FROM inventory WHERE ID = 12345" -ServerInstance "YourServer" -Database "YourDB" | Export-Csv -Path "C:\Temp\BlobPurge.csv"

3. Run a PowerShell script to delete blobs

Here's a sample example PowerShell script using Azure PowerShell module:

$filesToDelete = Import-Csv "C:\Temp\BlobPurge.csv"

foreach ($file in $filesToDelete) {
    $container = "containername"  ---this you need to get it from Storage Location column of your file
    $blobName = "bobname"  ---this you need to get it from Storage Location column of your file

    Remove-AzStorageBlob -Container $container -Blob $blobName -Context (Get-AzStorageAccount -ResourceGroupName "YourRG" -Name "YourStorageAccount").Context
}

You may need to parse the storage path carefully depending on how your storageLocation is formatted — e.g., whether it's the full blob URL or just container name.