How to get the dropped/deleted tables list from Azure Synapse SQL server database

Question

Hello ,I am currently using Azure synapse dedicated SQL pools
When I am trying to get the list of dropped tables list from Azure SQL sever and with below query but troubleshooting

SELECT [Begin Time] ,[Transaction Name] ,SUSER_SNAME ([Transaction SID]) AS [User] FROM fn_dblog (NULL, NULL) WHERE [Transaction Name] = N'DROPOBJ'; **GO
**
Msg 111252, Level 16, State 1, Line 1 The function 'DBLOG' is not supported.

Not sure if we can archive this , Required your assistance

Thanks & Regards
Venkat

Answer 1

@venkat rao

Thank you for reaching out.

My understanding is that you are trying to get the list of deleted and drop Synapse DBs.

I would suggest enabling auditing from the azure portal, Set up Auditing for Azure SQL Database and Azure Synapse Analytics as first step then Use Auditing to analyze audit logs and reports.

You can start with a simple query, such as: search "SQLSecurityAuditEvents" to view the audit logs. From here, you can also use Azure Monitor logs to run advanced searches on your audit log data. Azure Monitor logs gives you real-time operational insights using integrated search and custom dashboards to readily analyze millions of records across all your workloads and servers.

Please use the query in the blog and modify it to get the correct result.

https://techcommunity.microsoft.com/t5/azure-database-support-blog/learning-from-expertise-2-who-dropped-my-azure-sql-db/ba-p/2149276

Helpful link https://learn.microsoft.com/en-us/azure/azure-monitor/logs/log-analytics-tutorial#view-table-information

Regards,

Oury

Answer 2

Hi, Please go through below link, which explains to get the same through SSMS & query: https://www.mssqltips.com/sqlservertip/6849/sql-drop-table-investigation-options/#SnippetTab Hope this may help.

Answer 3

I don't work with Azure Synapse Analytics myself, so I don't know what is available there.

But I note that what you ask for is nothing which is supported on the classical on-prem version of SQL Server. Yes, I see your query with fn_dblog, but this is an undocumented and unsupported function.