Check if a database is a snapshot

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.