Users: 15,000+ | Page Views: 50,000+ | PPS: 2.5
TechDevOps.com
Explore Tools
{Experts in Microsoft SQL Server Operations, Performance, HA/DR, Data Migrations & Cloud Computing}



T-SQL - List Databases, Logins & SQL Jobs
by BF (Senior MSSQL Operations - Enterprise, Cloud, Strategy)
2018-05-14








T-SQL:


Select
@@SERVERNAME as 'Server',
[name] as 'Database',
database_id as 'DBID',
lower(suser_sname([owner_sid])) as 'Owner',
recovery_model_desc,
state_desc,
user_access_desc,
is_read_only,
log_reuse_wait_Desc,
[compatibility_level],
create_date,
collation_name,
is_query_store_on,
is_published,
is_distributor,
is_encrypted,
target_recovery_time_in_seconds,
delayed_durability_desc,
is_mixed_page_allocation_on,
replica_id,
is_auto_create_stats_on,
is_auto_update_stats_on
from master.sys.databases

Select
[Name],
[Type_Desc],
default_database_name,
[sid],
is_disabled,
create_date,
modify_date
from master.sys.server_principals
order by [Type_Desc] desc

Select
lower(@@SERVERNAME) as 'Server',
[name] 'Job Name',
lower(suser_sname([owner_sid])) as 'Job Owner',
[enabled],
date_created,
date_modified
from msdb.dbo.sysjobs