So you want to quickly find out what the recovery model is for your database but you don’t want to start clicking and right-clicking in SSMS/Enterprise Manager to get that information. Here are some examples of how to get the recovery model for your database(s) via T-SQL. Replace ‘msdb’ with the database name of interest.
If you want it for all databases in one shot
SELECT name,DATABASEPROPERTYEX(name,’Recovery’) FROM sysdatabases
SELECT name,recovery_model_desc FROM sys.databases