Cacher is the code snippet organizer for pro developers

-- check if databases is encrypted
select * from sys.dm_database_encryption_keys 
SELECT name FROM sys.databases AS d where d.is_master_key_encrypted_by_server = 1
select  name from sys.databases where is_encrypted = 1

DECLARE @DbName varchar(255)
DECLARE @SqlTxt nvarchar(max)
DECLARE DbCursor CURSOR LOCAL FORWARD_ONLY FOR
	SELECT name FROM sys.databases where state_desc = 'ONLINE'
OPEN DbCursor
FETCH NEXT FROM DbCursor INTO @DbName
WHILE @@FETCH_STATUS = 0
BEGIN 
	SET @SqlTxt = '
			USE [' + @DbName + ']
			IF EXISTS(SELECT name, key_length, algorithm_desc, create_date, modify_date FROM sys.symmetric_keys)
			BEGIN
				PRINT db_name() + '' has symmetric_keys''
			END

			IF EXISTS(SELECT name, algorithm_desc  FROM sys.asymmetric_keys)
			BEGIN
				PRINT db_name() + '' has asymmetric_keys''
			END

			IF EXISTS(SELECT name, subject, start_date, expiry_date  FROM sys.certificates)
			BEGIN
				PRINT db_name() + '' has certificates''
			END'
	EXEC sp_executesql @SqlTxt

	FETCH NEXT FROM DbCursor INTO @DbName
END
CLOSE DbCursor
DEALLOCATE DbCursor