{"id":6246,"date":"2019-03-07T07:36:12","date_gmt":"2019-03-07T07:36:12","guid":{"rendered":"https:\/\/ittutorial.org\/?p=6246"},"modified":"2019-11-28T07:07:22","modified_gmt":"2019-11-28T07:07:22","slug":"sql-server-collection-inventory-script-1","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/sql-server-collection-inventory-script-1\/","title":{"rendered":"SQL Server Collection Inventory Script -1"},"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>Total Database Log Size<\/li>\n<li>Total Database Log Used<\/li>\n<li>Total Database Datafile size<\/li>\n<li>Logical CPU Count<\/li>\n<li>Physical CPU Count<\/li>\n<li>Physical Memory on Server<\/li>\n<li>Total Disk Size on Server<\/li>\n<li>Total Free Disk space on Server<\/li>\n<\/ul>\n<p>Query result will be like following screenshots.<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6248\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory1.png\" alt=\"\" width=\"1734\" height=\"202\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory1.png 1734w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory1-300x35.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory1-768x89.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory1-1024x119.png 1024w\" sizes=\"auto, (max-width: 1734px) 100vw, 1734px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-6249\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory2.png\" alt=\"\" width=\"1715\" height=\"170\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory2.png 1715w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory2-300x30.png 300w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory2-768x76.png 768w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/03\/Inventory2-1024x102.png 1024w\" sizes=\"auto, (max-width: 1715px) 100vw, 1715px\" \/><\/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>sp_configure 'show advanced options', 1;\r\nGO\r\nRECONFIGURE;\r\nGO\r\nsp_configure 'Ole Automation Procedures', 1;\r\nGO\r\nRECONFIGURE;\r\nGO\r\n\/*******************************************************\/\r\nSET NOCOUNT ON\r\nDECLARE @hr int\r\nDECLARE @fso int\r\nDECLARE @drive char(1)\r\nDECLARE @odrive int\r\nDECLARE @TotalSize varchar(20) DECLARE @MB Numeric ; SET @MB = 1048576\r\n\r\nCREATE TABLE #drives (drive char(1) PRIMARY KEY, FreeSpace int NULL,\r\nTotalSize int NULL) INSERT #drives(drive,FreeSpace) EXEC\r\nmaster.dbo.xp_fixeddrives EXEC @hr=sp_OACreate\r\n'Scripting.FileSystemObject',@fso OUT IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo\r\n@fso\r\n\r\nDECLARE dcur CURSOR LOCAL FAST_FORWARD\r\nFOR SELECT drive from #drives ORDER by drive\r\nOPEN dcur FETCH NEXT FROM dcur INTO @drive\r\n\r\nWHILE @@FETCH_STATUS=0\r\nBEGIN\r\nEXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive\r\nIF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @fso EXEC @hr =\r\nsp_OAGetProperty\r\n@odrive,'TotalSize', @TotalSize OUT IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo\r\n@odrive UPDATE #drives SET TotalSize=@TotalSize\/@MB WHERE\r\ndrive=@drive FETCH NEXT FROM dcur INTO @drive\r\nEnd\r\nClose dcur\r\nDEALLOCATE dcur\r\n\r\nEXEC @hr=sp_OADestroy @fso IF @hr &lt;&gt; 0 EXEC sp_OAGetErrorInfo @fso\r\n--SELECT @@Servername\r\n--SELECT\r\n--drive, TotalSize as 'Total(MB)', FreeSpace as 'Free(MB)' FROM #drives\r\n--ORDER BY drive\r\n\r\nCREATE TABLE #CPUInfo\r\n( Logical_CPU_Count bigint,\r\nHyperthread_Ratio bigint,\r\nPhysical_CPU_Count bigint,\r\nPhysical_Memory_MB bigint\r\n)\r\n\r\nINSERT INTO #CPUInfo(\r\nLogical_CPU_Count,\r\nHyperthread_Ratio,\r\nPhysical_CPU_Count,\r\nPhysical_Memory_MB\r\n)\r\nSELECT \r\ncpu_count AS [Logical_CPU_Count] \r\n,hyperthread_ratio AS [Hyperthread_Ratio]\r\n,cpu_count\/hyperthread_ratio AS [Physical_CPU_Count]\r\n, physical_memory_kb\/1024 AS [Physical_Memory_MB]\r\nFROM sys.dm_os_sys_info\r\n\r\nCREATE TABLE #DatabaseInfo\r\n( Machine_Name varchar(50),\r\nInstance_Name varchar(50),\r\nSql_Server_Name varchar(50),\r\nTotal_Database_log_size_MB bigint,\r\nTotal_Database_log_used_MB bigint,\r\nTotal_Database_Data_File_Size_MB bigint\r\n)\r\nINSERT INTO #DatabaseInfo\r\n(\r\nMachine_Name,\r\nInstance_Name,\r\nSql_Server_Name,\r\nTotal_Database_log_size_MB,\r\nTotal_Database_log_used_MB,\r\nTotal_Database_Data_File_Size_MB\r\n)\r\n\r\n\r\nselect convert(varchar(50),serverproperty('MachineName')) 'Machine_Name'\r\n,convert(varchar(50),isnull(serverproperty('InstanceName'),'mssqlserver')) 'Instance_Name'\r\n,convert(varchar(50),@@SERVERNAME) 'Sql_Server_Name'\r\n,sum(ls.cntr_value\/1024) as [Total_Database_log_size_MB]\r\n,sum(lu.cntr_value\/1024)as [Total_Database_log_used_MB]\r\n,sum(ds.cntr_value\/1024) as [Total_Database_Data_File_Size_MB]\r\nfrom sys.databases d\r\nleft outer 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\nleft outer 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\nleft outer join sys.dm_os_performance_counters as lp on lp.instance_name=d.name and lp.counter_name like N'Percent Log Used%'\r\nleft outer 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\nwhere d.database_id&gt;4; -- sistem database ler harc\r\n\r\n\r\nWITH SizeDisc AS\r\n( -- sunucu \u00fczerindeki t\u00fcm drive size ve free size bilgisi\r\nSELECT SUM(TotalSize) as 'TotalDiscSizeonServer(MB)', \r\nSUM(FreeSpace) as 'TotalFreeDiscSizeOnServer(MB)' \r\nFROM #drives\r\n)\r\nSELECT *\r\nFROM #DatabaseInfo,#CPUInfo,SizeDisc\r\n\r\nDROP TABLE #Drives \r\nDROP TABLE #DatabaseInfo\r\nDROP TABLE #CPUInfo \r\nGO\r\n\/*******************************************************\/\r\n\/* Disabling Ole Automation Procedures *\/\r\nsp_configure 'show advanced options', 1;\r\nGO\r\nRECONFIGURE;\r\nGO\r\nsp_configure 'Ole Automation Procedures', 0;\r\nGO\r\nRECONFIGURE;\r\n\/*******************************************************\/\r\nGO<\/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":6247,"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,2234,2235,2230,2229],"class_list":["post-6246","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-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.png","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6246","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=6246"}],"version-history":[{"count":2,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6246\/revisions"}],"predecessor-version":[{"id":12979,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/6246\/revisions\/12979"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/6247"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=6246"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=6246"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=6246"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}