If you want to get a list of SQL Server databases and their basic configuration details on your current SQL server instance, you could manually open database property windows on each and every database through SSMS and note down the detail. But it might take long time depend on number of database on the server. But below script will help you to get detailed list of SQL Server databases and details at once.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
SELECT @@SERVERNAME AS Server , name AS [Database Name] , SUSER_NAME(owner_sid) AS [Owner of the database], recovery_model_Desc AS [Recovery Model] , CASE compatibility_level WHEN 65 THEN 'SQL Server 6.5' WHEN 70 THEN 'SQL Server 7.0' WHEN 80 THEN 'SQL Server 2000' WHEN 90 THEN 'SQL Server 2005' WHEN 100 THEN 'SQL Server 2008/R2' WHEN 110 THEN 'SQL Server 2012' WHEN 120 THEN 'SQL Server 2014' END as [SQL Server Version] , create_date AS [Create Date] , state_desc AS [Database State], CASE WHEN is_read_only=0 THEN 'READ_WRITE' WHEN is_read_only=1 THEN 'READ_ONLY' ELSE 'NA' END AS [Is Database read_only ?], page_verify_option_desc AS [PAGE VERIFY option], log_reuse_wait_desc AS [Reuse of transaction log space], user_access_desc AS [User-access setting], CASE WHEN is_auto_shrink_on=0 THEN 'OFF' WHEN is_auto_shrink_on=1 THEN 'ON' ELSE 'NA' END AS [Auto Shrink setting], CASE WHEN is_encrypted=0 THEN 'Not Encrypted' WHEN is_encrypted=1 THEN 'Encrypted' ELSE 'NA' END AS [Database Encryption] FROM sys.databases ORDER BY Name; |
Leave a Comment