{"id":6359,"date":"2019-03-11T06:57:07","date_gmt":"2019-03-11T06:57:07","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6359"},"modified":"2019-11-28T07:02:19","modified_gmt":"2019-11-28T07:02:19","slug":"sql-server-collection-inventory-script-3","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-collection-inventory-script-3\/","title":{"rendered":"SQL Server Collection Inventory Script -3"},"content":{"rendered":"<p>Hi,<\/p>\n<p>You want to learn all Inventory of SQL Server when you connect to SQL Server database for the first time.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6247\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server.png\" alt=\"\" width=\"466\" height=\"383\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server.png 466w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server-300x247.png 300w\" sizes=\"auto, (max-width: 466px) 100vw, 466px\" \/><\/p>\n<p><!--more--><\/p>\n\n<p>&nbsp;<\/p>\n<p>This SQL Server Database Inventory consist of following informations.<\/p>\n<ul>\n<li>Machine Name<\/li>\n<li>Instance Name<\/li>\n<li>SQL Server Name<\/li>\n<li>Database Name<\/li>\n<li>Owner<\/li>\n<li>Compatibility<\/li>\n<li>Collation Name<\/li>\n<li>IS_auto_close_on<\/li>\n<li>IS_auto_shrink_on<\/li>\n<li>IS_in_standby<\/li>\n<li>Page_verify_option_desc<\/li>\n<li>State<\/li>\n<li>IS_auto_create_stats_on<\/li>\n<li>IS_auto_update_stats_on<\/li>\n<li>IS_auto_update_stats_async_on<\/li>\n<li>Total Database Log Size<\/li>\n<li>Total Database Log Used<\/li>\n<li>Total Database Datafile size<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>Query is like following,\u00a0it will make your job very simplify when you connect to any SQL Server database for the first time.<\/p>\n<p>&nbsp;<\/p>\n<pre>select \r\n\t  serverproperty('MachineName') 'machine_name'\r\n\t  ,isnull(serverproperty('InstanceName'),'mssqlserver') 'instance_name'\r\n\t  ,@@SERVERNAME 'sql_server_name'\r\n\t  ,d.name 'database_name'\r\n\t  ,suser_sname(d.owner_sid) 'owner'\r\n\t  ,d.compatibility_level\r\n\t  ,d.collation_name\r\n\t  ,d.is_auto_close_on\r\n\t  ,d.is_auto_shrink_on\r\n\t  ,d.state_desc\r\n\t  ,d.snapshot_isolation_state\r\n\t  ,d.is_read_committed_snapshot_on\r\n\t  ,d.recovery_model_desc\r\n\t  ,d.is_auto_create_stats_on\r\n\t  ,d.is_auto_update_stats_on\r\n\t  ,d.is_auto_update_stats_async_on\r\n\t  ,d.is_in_standby\r\n\t  ,d.page_verify_option_desc\r\n\t  ,d.log_reuse_wait_desc\r\n\t  ,ls.cntr_value as [log size (kb)]\r\n\t  ,lu.cntr_value as [log used (kb)]\r\n\t  ,lp.cntr_value as [percent log used]\r\n\t  ,ds.cntr_value as [data file(s) size (kb)]\r\nfrom sys.databases d\r\n\t inner join sys.dm_os_performance_counters as lu on lu.instance_name=d.name and lu.counter_name like N'Log File(s) Used Size (KB)%'\r\n\t inner join sys.dm_os_performance_counters as ls on ls.instance_name=d.name and ls.counter_name like N'Log File(s) Size (KB)%' and ls.cntr_value &gt; 0\r\n\t inner join sys.dm_os_performance_counters as lp on lp.instance_name=d.name and lp.counter_name like N'Percent Log Used%'\r\n\t inner join sys.dm_os_performance_counters as ds on ds.instance_name=d.name and ds.counter_name like N'Data File(s) Size (KB)%'\r\norder by d.name<\/pre>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h4><span style=\"color: #ff0000;\">Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.<\/span><\/h4>\n<p><a href=\"https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/\">https:\/\/ittutorial.org\/sql-server-tutorials-microsoft-database-for-beginners\/<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Hi, You want to learn all Inventory of SQL Server when you connect to SQL Server database for the first time.<\/p>\n","protected":false},"author":1,"featured_media":6287,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[3828,2227,3827],"tags":[1028,1031,1600,1610,2231,2228,2233,2232,2237,2236,2323,2324,2234,2235,2230,2229],"class_list":["post-6359","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-sql-scripts","category-sql-server-2","category-sql-server-dba-scripts","tag-mehmet-deveci","tag-mehmet-salih-deveci","tag-sql-server-2012","tag-sql-server-2014","tag-sql-server-2019","tag-sql-server-collection-inventory-script","tag-sql-server-instance-name","tag-sql-server-inventory-script","tag-sql-server-select-inventory","tag-sql-server-select-machine-name","tag-sql-server-sys-databases","tag-sql-server-sys-dm_os_performance_counters","tag-sql-server-total-database-datafile-size","tag-sql-server-total-database-log-size","tag-sql-server-useful-query","tag-sql-server-useful-script"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/sql-server-database.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6359","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=6359"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6359\/revisions"}],"predecessor-version":[{"id":12962,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6359\/revisions\/12962"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/6287"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6359"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6359"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6359"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}