List All Dropped Databases

I need to confirm if a particular table ever existed in our SQL Server. Is there an existing script or method one can use to list all dropped databases in an SQL Server instance?

Well, I don't know about ever, since SQL Server doesn't keep that information around forever.

Also, it's unclear if you are asking about tables or databases. Since you mentioned both, here is a query against the default trace (based on this question) that will return either, but only going back as far as the default trace goes. It shows who dropped what, when, and from where.

DECLARE @path nvarchar(260) = (SELECT 
    REVERSE(SUBSTRING(REVERSE([path]), 
    CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
  FROM sys.traces WHERE is_default = 1);

SELECT ObjectType,
       ObjectName, 
       DatabaseName, 
       LoginName, 
       HostName, 
       ApplicationName, 
       StartTime
  FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE EventClass = 47 AND EventSubClass = 1
  ORDER BY StartTime DESC;

The enum for ObjectType is documented here, but if you want to filter, you can use ObjectType = 16964 for databases and ObjectType = 8277 for tables.

The default trace rolls off and purges the oldest rows/files as it adds new ones. If you need to go back further than that, you will need to resort to restoring older backups, if you still have them.

StackExchange Database Administrators Q#213791, answer score: 18