SQL Server alap információk

Az alábbi script alap információkat gyűjt össze az SQL Server-ről. SQL Server 2005-től kompatibilis.

/*===============================================================================
  File: base_01.sql     
  Dátum: 2012.05.31
  Módositva: 2012.12.05
  Leírás: alap sql server információk
  SQL Server verziók: 2005 és újabb
  Szerző: Berke János -  IamBerke.com
---------------------------------------------------------------------------------
  (cc) 2012, IamBerke.com
 
  Szabadon másolható, módosítható, bemutatható a kód, amenniyben nem kereskedelmi
  célokat szolgál. A kód megjelenhet nyomtatott vagy elektronikus formában, 
  amennyiben a forrás megjelenítésre kerül, de a megjelnéshez a szerző 
  előzetes jóváhagyása is szükséges.
 
  A KÓD ÉS AZ INFORMÁCIÓK MINDENFÉLE GARANCIA NÉLKÜL "AS-IS" ÁLLNAK RENDELKEZÉSRE,
  A SZERZŐ SEMMIFÉLE - SEM KÖZVETLEN, SEM KÖZVETETT - FELELŐSSÉGET NEM VÁLLAL.  
================================================================================*/

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#version') IS NOT NULL
	DROP TABLE #version;
GO

CREATE TABLE #version
(
	[version] varchar(50),
	[build] varchar(20),
	[splevel] varchar(100),
	[latest_version] bit
);
GO
-- http://support.microsoft.com/kb/321185
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('Unknown', NULL, 'Unknown', 0);
--SQL Server 2000
/*
INSERT INTO #version ([version], [build], [splevel], [latest_version])	VALUES ('SQL Server 2000', '8.00.194', 'SQL Server 2000 RTM or MSDE 2.0', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])	VALUES ('SQL Server 2000', '8.00.384', 'SQL Server 2000 SP1', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.534', 'SQL Server 2000 SP2', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.701', 'Hotfix Installer v.1 released', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.760', 'SQL Server 2000 SP3 or SP3a (8.00.766 ssnetlib.dll)', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.765', 'Post SP3 hotfix rollup', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.818', '(821277)', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.977', 'Update.exe Hotfix Installer Baseline 1 (884856)', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.1007', 'Update.exe Hotfix Installer Baseline 2 (891640)', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.2039', 'SQL Server 2000 SP4', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.2040', 'Post-SP4 AWE fix (899761)', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.2273', 'MS08-040 - KB 948111 July 8, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.2282', 'MS09-004: KB959420 October 29,2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2000', '8.00.2283', 'Post-SP4 hotfix for MS09-004 (971524)', 1);
*/

--SQL Server 2005
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.1399.00', 'RTM', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.2047.00', 'SP1', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3042.00', 'SP2 (937137)', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3068.00', 'Security update (948109) Jul 8, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3152.00', 'SP2 Rollup (933097) May 15, 2007', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3161.00', 'SP2 CU #1 (935356) April 16, 2007', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3175.00', 'SP2 CU #2 (936305) June 18, 2007', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3186.00', 'SP2 CU #3 (939537) Aug 20, 2007', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3200.00', 'SP2 CU #4 (941450) Oct 15, 2007', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3215.00', 'SP2 CU #5 (943656) Dec 17, 2007', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3228.00', 'SP2 CU #6 (946608) Feb 18, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3239.00', 'SP2 CU #7 (949095) Apr 14, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3257.00', 'SP2 CU #8 (951217) Jun 16, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3282.00', 'SP2 CU #9 (953752) Aug 18, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3294.00', 'SP2 CU #10 (956854) Oct 21, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3301.00', 'SP2 CU #11 (958735) Dec 15, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3315.00', 'SP2 CU #12 (960485) Feb 16, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3325.00', 'SP2 CU #13 (967908) Apr 20, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3328.00', 'SP2 CU #14 (970278) June 15, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3330.00', 'SP2 CU #15 (972510) Aug 17, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3355.00', 'SP2 CU #16 (974647) Oct 19, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.3356.00', 'SP2 CU #17 (976952) Dec 21, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4035.00', 'SP3 RTW (955706) Dec 15, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4053.00', 'Security update (970892) Oct 12, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4207.00', 'SP3 CU #1 (959195) Dec 19, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4211.00', 'SP3 CU #2 (961930) Feb 16, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4220.00', 'SP3 CU #3 (967909) Apr 20, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4226.00', 'SP3 CU #4 (970279) June 15, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4230.00', 'SP3 CU #5 (972511) Aug 17, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4266.00', 'SP3 CU #6 (974648) Oct 19, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4273.00', 'SP3 CU #7 (976951) Dec 21, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4285.00', 'SP3 CU #8 (978915) Feb 16, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4294.00', 'SP3 CU #9 (980176) Apr 19, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4305.00', 'SP3 CU #10 (983329) Jun 21, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4309.00', 'SP3 CU #11 (2258854) Aug 16, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4311.00', 'SP3 CU #12 (2345449) Oct 18, 2010SP3', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4315.00', 'SP3 CU #13 (2438344) Dec 20, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4317.00', 'SP3 CU #14 (2489375) Feb 21, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.4325.00', 'SP3 CU #15 (2507766) Mar 22 , 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.5000.00', 'SP4 RTW (PCU4) Dec 16, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.5254.00', 'SP4 CU #1 (2464079) Dec 23, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.5259.00', 'SP4 CU #2 (2489409) Feb 21, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2005', '9.00.5266.00', 'SP4 CU #3 (2507769) Mar 22, 2011', 1);

--SQL Server 2008
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1600.22', 'RTM – Aug 6, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1763.00', 'RTM CU #1 (956717) Sept 22, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1779.00', 'RTM CU #2 (958186) Nov 19, 2008', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1787.00', 'RTM CU #3 (960484) Jan 19, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1798.00', 'RTM CU #4 (963036) Mar 16, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1806.00', 'RTM CU #5 (969531) May 18, 2009 SP1', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1812.00', 'RTM CU #6 (971490) Jul 20, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1818.00', 'RTM CU #7 (973601) Sep 21, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1823.00', 'RTM CU #8 (975976) Nov 16, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1828.00', 'RTM CU #9 (977444) Jan 18, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.1835.00', 'RTM CU #10 (979064) Mar 15, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2531.00', 'SP1 RTW / PCU 1 April, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2573.00', 'GDR Security update (2494096), June 14, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2710.00', 'SP1 CU #1 (969099) Apr 16, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2714.00', 'SP1 CU #2 (970315) May 18, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2723.00', 'SP1 CU #3 (971491) Jul 20, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2734.00', 'SP1 CU #4 (973602) Sep 21, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2746.00', 'SP1 CU #5 (975977) Nov 16, 2009', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2757.00', 'SP1 CU #6 (977443) Jan 18, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2766.00', 'SP1 CU #7 (979065) Mar 26, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2775.00', 'SP1 CU #8 (981702) May 17, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2789.00', 'SP1 CU #9 (2083921) July 19, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2799.00', 'SP1 CU #10 (2279604) Sep 20, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2804.00', 'SP1 CU #11 (2413738) Nov 15, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2808.00', 'SP1 CU #12 (2467236) Jan 17, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2816.00', 'SP1 CU #13 (2497673) Mar 17, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2821.00', 'SP1 CU #14 (2527187) May 16, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2847.0', 'SP1 CU #15 (2555406) Jul 18, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.2850.0', 'SP1 CU #16 (2582282) Sep 19, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4000.00', 'SP2 RTW / PCU 2 (2285068) Sep 29, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4064.00', 'GDR Security Update (2494089), June 14, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4266.00', 'SP2 CU #1 ( 2289254) Nov 15, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4272.00', 'SP2 CU #2 (2467239) Jan 17, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4279.00', 'SP2 CU #3 (2498535) Mar 17, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4285.00', 'SP2 CU #4 (2527180) May 16, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4316.00', 'SP2 CU #5 (2555408) Jul 18, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4321.00', 'SP2 CU #6 (2582285) Sep 19, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4323.00', 'SP2 CU #7 (2617148) Nov 21, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4326.00', 'SP2 CU #8 (2648096) Jan 16, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4330.00', 'SP2 CU #9 (2673382) Mar 19, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.4332.00', 'SP2 CU #10 (2696625) May 21, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.5500.00', 'SP3 RTW / PCU 3 (2546951) Oct 6, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.5766.00', 'SP3 CU #1 (2617146) Oct 17, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.5768.00', 'SP3 CU #2 (2633143) Nov 21, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.5770.00', 'SP3 CU #3 (2648098) Jan 16, 201', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.5775.00', 'SP3 CU #4 (2673383) Mar 19, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008', '10.00.5785.00', 'SP3 CU #5 (2696626) May 21, 2012', 1);


--SQL Server 2008 R2
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1600.1', 'RTM – May 10, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1617.00', 'GDR Security Update (2494088), June 21, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1702.00', 'RTM CU #1 (981355) May 18, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1720.00', 'RTM CU #2 (2072493) Jun 21, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1734.00', 'RTM CU #3 (2261464) Aug 16, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1746.00', 'RTM CU #4 (2345451) Oct 18, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1753.00', 'RTM CU #5 (2438347) Dec 20, 2010', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1765.00', 'RTM CU #6 (2489376) Feb 21, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1777.00', 'RTM CU #7 (2507770) Apr 18, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1797.00', 'RTM CU #8 (2534352) June 20, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1804.00', 'RTM CU #9 (2567713) Aug 15, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1807.00', 'RTM CU #10 (2591746) Oct 17, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1809.00', 'RTM CU #11 (2633145) Dec 19, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1810.00', 'RTM CU #12 (2659692) Feb 21, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1815.00', 'RTM CU #13 (2679366) Apr 16, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.1817.00', 'RTM CU #14 (2703280) June 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2500.0', 'SP1 RTW/PCU 1(2528583), July 12, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2769.00', 'SP1 CU #1 (2544793) Jul 18, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2772.00', 'SP1 CU #2 (2567714) Aug 15, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2789.00', 'SP1 CU #3 (2591748) Oct 17, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2796.00', 'SP1 CU #4 (2633146) Dec 19, 2011', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2806.00', 'SP1 CU #5 (2659694) Feb 22, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2811.00', 'SP1 CU #6 (2679367) Apr 16, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2817.00', 'SP1 CU #7 (2703282) June 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2822.00', 'SP1 CU #8 (2723743) August 31, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.2866.00', 'SP1 CU #9 (2756574) October 15, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.4260.00', 'SP2 CU #1 (2720425) July 24, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.4263.00', 'SP2 CU #2 (2740411) August 31, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2008 R2', '10.50.4266.0', 'SP2 CU #3 (2754552) October 15, 2012', 1);



--SQL Server 2012
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2012', '11.0.2100.60', 'RTM', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2012', '11.0.2316.0', 'CU #1 (2679368) April 12, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2012', '11.0.2325.0', 'CU #2 (2703275) June 18, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2012', '11.0.2332.0', 'RTM CU #3 (2723749) August 31, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2012', '11.0.2383.0', 'RTM CU #4 (2758687) October 15, 2012', 0);
INSERT INTO #version ([version], [build], [splevel], [latest_version])  VALUES ('SQL Server 2012', '11.0.3000.0', 'SP1 RTW/PCU 1(2674319), November 07, 2012', 0);




SELECT 
	(SELECT [version] FROM #version WHERE SERVERPROPERTY('ProductVersion') = [build]) AS [Version],
	(SELECT [build] FROM #version WHERE SERVERPROPERTY('ProductVersion') = [build]) AS [Build],
	(SELECT [splevel] FROM #version WHERE SERVERPROPERTY('ProductVersion') = [build]) AS [splevel],
	(SELECT [latest_version] FROM #version WHERE SERVERPROPERTY('ProductVersion') = [build]) AS [latest_version],
	SERVERPROPERTY('ComputerNamePhysicalNetBIOS') AS [ComputerName],
	SERVERPROPERTY('MachineName') AS [MachineName],
	SERVERPROPERTY('LicenseType') AS [LicenseType],
	SERVERPROPERTY('InstanceName') AS [InstanceName],
	SERVERPROPERTY('Edition') AS [Edition],
	SERVERPROPERTY('ProductVersion') AS [ProductVersion],
	SERVERPROPERTY('ProductLevel') AS [ProductLevel],
	SERVERPROPERTY('Collation') AS [Collation],
	SERVERPROPERTY('IsClustered') AS [IsClustered],
	@@VERSION AS [VersionInfo],
	--SQL 2008+
	SERVERPROPERTY('FilestreamShareName') AS [FilestreamShareName],
	SERVERPROPERTY('FilestreamConfiguredLevel') AS [FilestreamConfiguredLevel],
	SERVERPROPERTY('FilestreamEffectiveLevel') AS [FilestreamEffectiveLevel]

IF ((SELECT [version] FROM #version WHERE SERVERPROPERTY('ProductVersion') = [build]) IN ('SQL Server 2008 R2', 'SQL Server 2012'))
	BEGIN
		--Windows Information
		SELECT 'Windows Information', * FROM sys.dm_os_windows_info;
		--Servcie information
		SELECT 'Service Information', * FROM sys.dm_server_services;
	END;

--Hardware information
SELECT 
	[cpu_count], 
	[hyperthread_ratio],  
	[cpu_count] / [hyperthread_ratio] AS [physical_cpu_count],
	[physical_memory_in_bytes] / 1073741824 AS [physical_memory_in_gb],
	[max_workers_count]
	--affinity information / virtual machine info is available in SQL 2008 or newer.
	/*,CASE 
		WHEN [affinity_type] = 1 AND THEN 'Manual'
		WHEN [affinity_type] = 2 THEN 'AUTO'
	END AS [affinity_type],
	[virtual_machine_type]*/
	
FROM 
	sys.dm_os_sys_info;


-- Get processor description from Windows Registry
EXEC xp_instance_regread 
'HKEY_LOCAL_MACHINE', 'HARDWARE\DESCRIPTION\System\CentralProcessor\0',
'ProcessorNameString';