You can quickly check if a database is a Snapshot or not by examining the source_database_id column of the sys.databases view
if the database is a snapshot, the source_database_id will contain the database_id of the original database. but if the database is a normal database, this field will be NULL.
-- Check if a database has a snapshot
DECLARE @DBname sysname
SET @DBname='MyDatabaseName'
-- check if a database is a snapshot or not
SELECT CASE WHEN source_database_id IS NULL THEN 'Not a snapshot' ELSE 'Snapshot' END AS Database_type , source_database_id, database_id
FROM sys.databases
WHERE Name=@DBname
You can use the same field to find all the snapshots for a specific database.
SELECT * FROM sys.databases
WHERE source_database_id =
(SELECT database_Id FROM sys.databases WHERE Name=@DBname)
This is useful if you have a script that regularly restore a database (say on a dev server) and needs to drop any snapshots before performing the restore.
