How to find SQL Server database is in use or not? What query do I need to write to know if the database is in use or not? I tried following query

SELECT
    name 
FROM 
    sys.databases 
WHERE 
    state != 0;


but it is not working.

I want to check there should not have a open connection with that database and should not executing a query against that DB
In order to use a database any session must first obtain a lock on it, usually a shared lock. So simply look for such locks:

select * 
from sys.dm_tran_locks 
where resource_type = 'DATABASE' 
and resource_database_id = &lt;dbid&gt;;