List All Dropped Databases

Posted on

Question :

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?

Answer :

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 
    CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
  FROM sys.traces WHERE is_default = 1);

SELECT ObjectType,
  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.

You can use the Schema Change History Report in SSMS.

Another option is querying the default trace directly, as described here

Please note that these make use of default internal information and that information is only around for a period of time. It will not help you answer the “ever existed” or “all dropped databases” in the sense of since the server began its existence. It will tell you what has been done recently.

In the future if this information is important, you’ll want to look into things such as server and database audits.

Leave a Reply

Your email address will not be published. Required fields are marked *