Collection and processing information about your system.
This article/post contains many method to create list of installed program in your windows system and this article is conceived as shallow review of future of MS SQL Server and my personal SQL server experience, and the other side this article is expanding of my old article and opensource program from 2012 year WinDump - creation system snapshot by WMI., what is expanding of my earlier article and opensource program WinDump - save system state. And at common, my ancient article in this site was writing in 2003 year about Windows Installer and accounting of installed program.
Also this is my first article in English on SQL part of my blog SQL, to opposite of another part of my blog, for example ASP NET MVC programming that contains many english articles. But I work in SQL server since 1998 year and my first post about SQL Server I was created in 2000 year and now I can do difficulties as possible task in SQL Server, for example see this my video My project for refactoring huge database to prepare using it in Entity Framework, on the other side I work in in English since 1986 year as programmer IBM/360 in National Cosmic Agency of Ukraine - why I have nothing articles about SQL in English? When I had understanding how many separate edges is combine to one article/post I immediately decided to write this article.
In global view there are three way to investigate list of installed program:
- 1. "My" extension of VB.NET environment.
- 2. WMI function of Windows.
- 2.1 Call WMI programmatically.
- 2.2 Two main GUI program exploring Windows WMI.
- 2.3 Processing NFO data exported from Msinfo32 as XML.
- 2.4 Processing NFO data as JSON.
- 2.5 Observing all WMI class by Powershell.
- 2.6 Extending SQL server opportunities by SQL CLR Assemblies to call WMI.
- 2.7 Collect to SQL Server all information with SQL CLR Assembly GetWmiInfo.
- 2.8 Process information about installed program, collection by GetWmiInfo
- 2.9 Getting information from one class WMI by Powershell.
- 3. Directly inspection of windows registry.
1. "My" extension of VB.NET environment.
VB.NET is a special proprietary languages, human oriented and created by Microsoft for RAPID development, fast as possible. VB.NET is much older than C# and has many special addition comparing C# Difference between VB and C#, since 2002 year this language support special object MY (and early VB6 has similar functional as separate library). I don't want repeat my article from 2006 and 2012 year and you may see in this articles how to use "MY" library in VB.NET and we are immediately jump to WMI.
2. WMI function of Windows.
2.1 Call WMI programmatically.
My first program to call WMI programmatically through WSH (window script host) I publish in this blog in 2001 year (Скрипты WSH) and below I show how to call WMI by simplest as possible way.
This method use Microsoft .NET wrapper about WMI function System.Management.dll
This is not ideal method of using WMI, because it's absurd to serialize hierarchical WMI data to plain text. But this is console utility is a step for more useful WMI function as SQL CLR Assemblies (we will showing it below).
1: Module Module1
2: Sub Main()
3: Dim WMI1 As New Wmi
4: Dim WmiNamespace As String = "\\XEON\ROOT\CIMV2"
5: Dim WmiRSClass As String = "WIN32_BIOS"
6: Console.WriteLine("WmiExplorer test")
7: While True
8: Console.WriteLine("Please Input WmiNamespace (like \\XEON\ROOT\CIMV2) or Enter to exit")
9: WmiNamespace = Console.ReadLine()
10: If WmiNamespace.Trim.Length = 0 Then Exit Sub
11: Dim Response As String = WMI1.Connect(WmiNamespace)
12: Console.WriteLine(Response)
13: If Response <> "Invalid namespace " Then
14: While True
15: Console.WriteLine("Please Input WmiClass (like WIN32_BIOS) or Enter to exit")
16: WmiRSClass = Console.ReadLine()
17: If WmiRSClass.Trim.Length = 0 Then Exit Sub
18: Dim Result As String
19: Result = WMI1.BindClass(WmiRSClass)
20: Console.WriteLine(Result)
21: If Result.StartsWith("Has") Then
22: Console.WriteLine(WMI1.ListProperty())
23: End If
24: End While
25: End If
26: End While
27: End Sub
28: End Module
1: Public Class Wmi
2: Dim ServerClass As Management.ManagementClass
3: Dim Scope As Management.ManagementScope
4: Dim Instances As Management.ManagementObjectCollection
5:
6: Function Connect(WmiNamespace As String) As String
7: Try
8: Scope = New Management.ManagementScope(WmiNamespace)
9: Scope.Connect()
10: If Scope.IsConnected Then Return "OK" Else Return "Not found"
11: Catch ex As Exception
12: Return ex.Message
13: End Try
14: End Function
15:
16: Function BindClass(WmiRSClass As String) As String
17: Try
18: ServerClass = New Management.ManagementClass(WmiRSClass)
19: ServerClass.Get()
20: Instances = ServerClass.GetInstances()
21: Return "Has " & ServerClass.Properties.Count & " properties"
22: Catch ex As Exception
23: Return ex.Message
24: End Try
25: End Function
26:
27: Function ListProperty()
28: Dim Out1 As New Text.StringBuilder
29: Try
30: For Each Instance As Management.ManagementObject In Instances
31: Out1.AppendLine(Instance.ToString)
32: Out1.AppendLine()
33: Dim InstProps As Management.PropertyDataCollection = Instance.Properties
34: Dim Prop As Management.PropertyData
35: For Each Prop In InstProps
36: Dim Name As String = Prop.Name
37: Dim Val As Object = Prop.Value
38: Out1.AppendLine("Property: " + Name)
39: If Val Is Nothing Then
40: Out1.AppendLine(" Value: <null>")
41: Else
42: Dim Str1 As New Text.StringBuilder
43: If Val.ToString = "System.UInt16[]" Then
44: Array.ForEach(Of UInt16)(Val, Sub(One) Str1.Append(One.ToString & ","))
45: Out1.AppendLine(" Value: " + Str1.ToString(0, Str1.Length - 1))
46: ElseIf Val.ToString = "System.String[]" Then
47: Array.ForEach(Of String)(Val, Sub(One) Str1.Append(One.ToString & ","))
48: Out1.AppendLine(" Value: " + Str1.ToString(0, Str1.Length - 1))
49: Else
50: Out1.AppendLine(" Value: " + Val.ToString())
51: End If
52: End If
53: Next
54: Next
55: Return Out1.ToString
56: Catch ex As Exception
57: Out1.AppendLine(ex.Message)
58: Return Out1.ToString
59: End Try
60: End Function
61: End Class
62:
2.2 Two main GUI program exploring Windows WMI.
My first post about WMI I wrote in 2001 year Скрипты WSH and now WmiExplorer is a good stable OpenSource program. You may download this program from https://wmie.codeplex.com/ and learn all system objects through universal WMI interface.
Second good GUI program is Msinfo32:
This is good programs, but this is only GUI interface to WMI. It has no oportunity to export data to SQL Server to strong processing this data. But one way to export data is exists msinfo32 /nfo.
2.3 Processing NFO data exported from Msinfo32 as XML.
Data from Msinfo32 naturally is exported by XML and has extension .NFO:
Since 2000 year I wrote a lot of various articles/posts about processing XML in special section of my blog, best program to working with XML is Altova, but you may download and install to you system XML Notepad from https://xmlnotepad.codeplex.com/ and see or exit your NFO-file.
But how to process this XML file? At common, there are two ways, first way is import this data to SQL Server and father processing data inside SQL Server.
Before processing data we can import data as XML fields of SQL server:
1: CREATE TABLE [dbo].[Xeon-msinfo32](
2: [i] [int] IDENTITY(1,1) NOT NULL,
3: [XML] [xml] NULL,
4: CONSTRAINT [PK_Xeon-msinfo32] PRIMARY KEY CLUSTERED
5: (
6: [i] ASC
7: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
8: ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
9: GO
10:
11: INSERT INTO [dbo].[Xeon-msinfo32](Xml)
12: SELECT * FROM OPENROWSET(BULK 'F:\xeon-nfo.NFO', SINGLE_BLOB) x;
Aftr that we can process data by xml Data Type Methods and XQuery Functions, for example by this function.
1: DECLARE @XML AS XML, @hDoc AS INT, @SQL NVARCHAR (MAX)
2: SELECT @XML = [XML] FROM [dbo].[Xeon-msinfo32]
3: EXEC sp_xml_preparedocument @hDoc OUTPUT, @XML
4: SELECT text
5: FROM OPENXML(@hDoc, 'MsInfo/Category/Data/')
6: --WITH (text nvarchar(100))
7: where not(text is null)
8: EXEC sp_xml_removedocument @hDoc
For more details about processing XML inside SQL please see my articles How to parse XML SOAP inside MS SQL and other in section XML.
2.4 Processing NFO data as JSON.
Second (alternative) way to processing NFO data is transform XML file to JSON, for example with this online service https://www.freeformatter.com/.
After that we may processing JSON data inside MongoDB. I have some details articles about MongoDB, for example MongoDB - noSQL-database for irregular JSON data, but this method of data processing is developing very fast and now we are have another tools, than previous year.
But the common logic remains the same, firstly need to prepare config file and start MongoDB as service.
After that we may load Json to MongoDB
and processing data as JSON. Json selector little bit similar to SQL selector, but strange for SQL base logic - SQL to Mongo mapping, SQL to MongoDB Mapping And I recommended you for creating query to JSON to use commercial query builder from 3T.
2.5 Observing all WMI class by Powershell.
In previous section we are see how to call WMI by special GUI program, in this section we are continue executing WMI, but in the command line. At common, we can call WMI directly by command WMIC (like in first screen above to install MongoDB) or through universal interface Powershell.
In this section we call WMI by Powershell and firstly we will observe all most important WMI class related to collection information about computer environment and installed software (with prefix Win32_). This is common description of this command PowerShell Get-WmiObject
Get-WMIObject -List| Where{$_.name -match "^Win32_"} | Sort Name > F:\Win32_All.txt
For further processing this interesting data we will import this data to SQL server. The simplest way to import text data is Sql Server Import Export Wizard (Download SQL Server Data Tools (SSDT) if you don't have installed it).
This wizard is not correctly separate one column from another in text file and I usually import all text row to one field "Column 0", I usually correct in this wizard only one parameters - length of "Column 0" field.
Next step is create clear data after importer store raw text to SQL Server. This task can be create by this script.
1: CREATE TABLE [dbo].[Win32](
2: [i] [int] IDENTITY(1,1) NOT NULL,
3: [Name] [nvarchar](50) NULL,
4: CONSTRAINT [PK_Win32] PRIMARY KEY CLUSTERED
5: (
6: [i] ASC
7: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
8: ) ON [PRIMARY]
9: GO
10:
11: Insert Win32
12: SELECT left([Column 0],36) FROM [tst1].[dbo].[Win32_All]
13: where [Column 0] like 'Win32_%' and
14: not [Column 0] like 'Win32_PerfRawData_%' and
15: not [Column 0] like 'Win32_PerfFormattedData__%'
2.6 Extending SQL server opportunities by SQL CLR Assemblies to call WMI.
In this step we are expanding my Сommon template of table value SQL CLR Assembly to insert body of code executing WMI pattern (from section 2.1 Call WMI programmatically). But firstly need to add reference to System.Management.dll to project and to MS SQL.
1: CREATE ASSEMBLY [System.Management]
2: FROM 'C:\Windows\Microsoft.NET\Framework\v2.0.50727\System.Management.dll'
3: WITH PERMISSION_SET = UNSAFE
4: GO
I done small refactoring code of part 2.1 Call WMI programmatically. from console utility to Table Value SQL CRL template:
1: Imports System
2: Imports System.Data
3: Imports System.Data.SqlClient
4: Imports System.Data.SqlTypes
5: Imports Microsoft.SqlServer.Server
6: Partial Public Class Wmi
7:
8: <Microsoft.SqlServer.Server.SqlFunction(DataAccess:=DataAccessKind.Read,
9: Name:="GetWmiInfo",
10: FillRowMethodName:="NextRow",
11: TableDefinition:="Prop Nvarchar(250), Val Nvarchar(1000)")>
12: Public Shared Function GetWmiInfo(WmiNamespace As SqlString, WmiClass As SqlString) As Collections.IEnumerable
13: Dim X As New Data.DataTable
14: X.Columns.Add("Prop", GetType(SqlString))
15: X.Columns.Add("Val", GetType(SqlString))
16:
17: If WmiClass.ToString.Trim.Length = 0 Or WmiNamespace.ToString.Trim.Length = 0 Then
18: X.Rows.Add("Error", "Request is empty")
19: Return X.Rows
20: Exit Function
21: End If
22:
23: Dim WMI1 As New WmiWrapper
24: Dim Response As String = WMI1.Connect(WmiNamespace.ToString)
25: If Response <> "Invalid namespace " Then
26: Dim Result As String
27: Result = WMI1.BindClass(WmiClass.ToString)
28: Console.WriteLine(Result)
29: If Result.StartsWith("Has") Then
30: WMI1.ListProperty(X)
31: Return X.Rows
32: Exit Function
33: End If
34: Else
35: X.Rows.Add("Error", "Invalid namespace")
36: Return X.Rows
37: Exit Function
38: End If
39: Return X.Rows
40: End Function
41:
42: Public Shared Sub NextRow(Obj As Object,
43: <Runtime.InteropServices.Out()> ByRef Prop As SqlString,
44: <Runtime.InteropServices.Out()> ByRef Val As SqlString)
45: Try
46: Dim X As Data.DataRow = TryCast(Obj, DataRow)
47: Prop = X("Prop").ToString
48: Val = X("Val").ToString
49: Catch ex As Exception
50: Prop = "Error"
51: Val = ex.Message
52: End Try
53: End Sub
54: End Class
1: Imports System
2:
3: Public Class WmiWrapper
4: Dim ServerClass As Management.ManagementClass
5: Dim Scope As Management.ManagementScope
6: Dim Instances As Management.ManagementObjectCollection
7:
8: Function Connect(WmiNamespace As String) As String
9: Try
10: Scope = New Management.ManagementScope(WmiNamespace)
11: Scope.Connect()
12: If Scope.IsConnected Then Return "OK" Else Return "Not found"
13: Catch ex As Exception
14: Return ex.Message
15: End Try
16: End Function
17:
18: Function BindClass(WmiRSClass As String) As String
19: Try
20: ServerClass = New Management.ManagementClass(WmiRSClass)
21: ServerClass.Get()
22: Instances = ServerClass.GetInstances()
23: Return "Has " & ServerClass.Properties.Count & " properties"
24: Catch ex As Exception
25: Return ex.Message
26: End Try
27: End Function
28:
29: Function ListProperty(ByRef DT As Data.DataTable) As Data.DataTable
30: Try
31: For Each Instance As Management.ManagementObject In Instances
32: Dim InstProps As Management.PropertyDataCollection = Instance.Properties
33: Dim Prop As Management.PropertyData
34: For Each Prop In InstProps
35: Dim Name As String = Prop.Name
36: Dim Val As Object = Prop.Value
37: If Val Is Nothing Then
38: DT.Rows.Add(Name, DBNull.Value)
39: Else
40: Dim Str1 As New Text.StringBuilder
41: If Val.ToString = "System.UInt16[]" Then
42: Array.ForEach(Of UShort)(Val, Sub(One) Str1.Append(One.ToString & ","))
43: DT.Rows.Add(Name, Str1.ToString(0, Str1.Length - 1))
44: ElseIf Val.ToString = "System.String[]" Then
45: Array.ForEach(Of String)(Val, Sub(One As String) Str1.Append(One.ToString & ","))
46: DT.Rows.Add(Name, Str1.ToString(0, Str1.Length - 1))
47: Else
48: DT.Rows.Add(Name, Val.ToString())
49: End If
50: End If
51: Next
52: Next
53: Return DT
54: Catch ex As Exception
55: DT.Rows.Add("Error", ex.Message)
56: Return DT
57: End Try
58: End Function
59: End Class
And test this code by this command:
1: select * from [dbo].[GetWmiInfo]('\\XEON\ROOT\CIMV2','WIN32_BIOS')
All working fine!
2.7 Collect to SQL Server all information with SQL CLR Assembly GetWmiInfo.
In finished step we will collecting all information about computer to table by script below.
1: CREATE TABLE [dbo].[XEON-prop](
2: [i] [int] IDENTITY(1,1) NOT NULL,
3: [Class] [nvarchar](50) NULL,
4: [Prop] [nvarchar](50) NULL,
5: [Val] [nvarchar](250) NULL,
6: CONSTRAINT [PK_XEON-prop] PRIMARY KEY CLUSTERED
7: (
8: [i] ASC
9: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
10: ) ON [PRIMARY]
11: GO
1: truncate table [tst1].[dbo].[XEON-prop]
2: DECLARE @ONE nvarchar(50)
3: DECLARE All_WMI_CLASS CURSOR
4: FOR SELECT [NAME] FROM [dbo].[Win32]
5:
6: OPEN All_WMI_CLASS
7:
8: FETCH NEXT FROM All_WMI_CLASS INTO @ONE
9: WHILE @@FETCH_STATUS = 0 BEGIN
10:
11: Insert [tst1].[dbo].[XEON-prop]([Class],[Prop],[Val])
12: SELECT RTRIM(@ONE),* FROM [dbo].[GetWmiInfo]('\\XEON\ROOT\CIMV2',RTRIM(@ONE))
13: FETCH NEXT FROM All_WMI_CLASS INTO @ONE
14: END
15:
16: CLOSE All_WMI_CLASS
17: DEALLOCATE All_WMI_CLASS
This script had working in my computer about one hour and as result I have more many million records with information about my computer.
2.8 Process information about installed program, collection by GetWmiInfo
Request below collect more than millions records directly to SQL Server.
1: select * from [dbo].[GetWmiInfo]('\\XEON\ROOT\CIMV2','Win32_SoftwareElement')
But how to process it and getting useful info from this bid data?
This is a best way to process this big data:
1: SELECT distinct ROW_NUMBER() OVER (Order by M.Val, P.Val,N.Val) as I, N.Val as [Name], P.Val as [Path], M.Val as [Manufacturer], convert(datetime, left(D.val,8), 111) as [InstallDate]
2: FROM [tst1].[dbo].[XEON-prop] As N
3: join [tst1].[dbo].[XEON-prop] as P On N.i+2=P.I
4: join [tst1].[dbo].[XEON-prop] as M On N.i-1=M.I
5: join [tst1].[dbo].[XEON-prop] as D On N.i-4=D.I
6: where N.Prop='Name' and P.Prop='Path' and M.Prop='Manufacturer' and D.Prop='InstallDate'
7: and not M.Val like 'Microsoft%'
As a result this is a list of my NonMicrosoft SoftwareElement in my computer XEON, Microsoft list more than 20 times bigger NonMicrosoft SoftwareElement in my computer XEON. (Both this list is updated in 2017).
2.9 Getting information from one class WMI by Powershell or WMIC
We may process information about one interesting class by powershell or WMIC and after that processing this plain text by the way in 3.3 Collect information about installed program directly from Registry by PowerShel.
1: wmic /output:F:\programlist.txt product get name, version
2: Get-WmiObject -Class Win32_Product
But this is not interesting way, if you use my SQL CLR Assembly
1: select * from [dbo].[GetWmiInfo]('\\XEON\ROOT\CIMV2','Win32_SoftwareElement')
because it store 1,5 millions of records about your software DIRECTLY TO SQL SERVER.
3. Directly inspection of windows registry.
3.1 Observing by registry editor.
Registry with important information about installed program may export tp standard plain text (".INI"-format) by building GUI Regedit.exe. Also there are many third party utilities to export registry.
3.2 Observing registry programmatically through VB-wrapper "My"
If you want process data from registry the best way is use VB.NET extension library "MY", details pattern please see in WinDump - снимок состояния системы с помощью WMI.
3.3 Collect information about installed program directly from Registry by PowerShell
In this section I show the simplest way to build list of installed program and below I publish lists of programs from some of my working computers. As first step need to upload registry key to plain text file. There are many ways to do this task, one of the way is use PowerShell. Type to cmd.exe "Powershell" and after that type this command:
Get-ItemProperty HKLM:\Software\Wow6432node\Microsoft\Windows\CurrentVersion\Uninstall\* | Select-Object DisplayName, DisplayVersion, Publisher, Size, InstallDate, UninstallString > F:\Installed-program.txt
After that you may proceed second step - to uploading this plain text to SQL DB. There are many way to do this task, for example using internal DB for OpenOffice, but I usually use MS SQL. The simplest way to upload plain text to DB is MS SQL Export/Import wizard. In this wizard need only select a source file as plain text, a destination MS SQL Server and the length of field (need set at least 500 chars). Db need to create manually before import data.
And last step is processing data in SQL server. It contains two substeps. (1) Transfer data from raw table to table with key, with filtering and split data to two column. In strong sound this operation is incorrect because imported rows after importing not has rowindex, and by strong way need firstly add from beginning of each row key with row number. But in practically SQL server not mix uploaded rows.
This is raw data, uploaded by wizard:
And this is first processing of data.
1: CREATE TABLE [dbo].[X22](
2: [i] [int] IDENTITY(1,1) NOT NULL,
3: [Name] [nvarchar](50) NULL,
4: [Val] [nvarchar](500) NULL,
5: CONSTRAINT [PK_X22] PRIMARY KEY CLUSTERED
6: (
7: [i] ASC
8: )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
9: ) ON [PRIMARY]
10: GO
11:
12: Insert XEON
13: select left([Column 0],15), SUBSTRING([Column 0] ,18,len([Column 0])-17) from [dbo].[Xeon-Installed-program]
14: where Len([Column 0])>16
After this step data is strongly structured by SQL rules.
And from this point we have opportunity to any create any list of our installed program, but firstly we must reverse rows to columns with considering order of row repeating. In common, reverse from row to column is most wide task of SQL server and I wrote many posts about this task, for example Выполняем разворот строк в столбцы в MS SQL и PostgreSQL. This is only one interesting technique to use MS SQL Server, if you have interest to MS SQL Server read my article about recursive CTE procedures Хранение в MS SQL сетевой топологии и отборы маршрутов рекурсивными CTE-процедурами..
But in this task our SQL command to reverse row to column will be simple:
1: select A.VAL as DisplayName,
2: B.VAl as DisplayVersion,
3: C.VAL as Publisher,
4: D.VAL as Size,
5: E.VAL as InstallDate,
6: F.VAL as UninstallString
7: from [tst1].[dbo].[X22] as A
8: join [tst1].[dbo].[X22] as B on A.I+1=B.I and B.Name='DisplayVersion'
9: join [tst1].[dbo].[X22] as C on A.I+2=C.I and C.Name='Publisher'
10: join [tst1].[dbo].[X22] as D on A.I+3=D.I and D.Name='Size'
11: join [tst1].[dbo].[X22] as E on A.I+4=E.I and E.Name='InstallDate'
12: join [tst1].[dbo].[X22] as F on A.I+5=F.I and F.Name='UninstallString'
13: where A.Name='DisplayName'
14: and not (len(A.VAL)=0 and len(B.VAl)=0 and len(C.VAL)=0 and len(D.val)=0 and len (E.VAL)=0 and len(F.VAL)=0)
15: and C.VAL <>' Microsoft Corporation'
Structured data is possible to processing by any way, for example this SQL script creates table for publishing list of installed program in your system to html.
1: drop table #tmp1
2: create table #tmp1(
3: [i] [int] IDENTITY(1,1) NOT NULL,
4: DisplayName Nvarchar(200),
5: DisplayVersion Nvarchar(200),
6: Publisher Nvarchar(200),
7: Size Nvarchar(200),
8: InstallDate Nvarchar(200),
9: UninstallString Nvarchar(200)
10: )
11: ;
12: Insert #tmp1
13: select A.VAL as DisplayName,
14: B.VAl as DisplayVersion,
15: C.VAL as Publisher,
16: D.VAL as Size,
17: E.VAL as InstallDate,
18: F.VAL as UninstallString
19: from [tst1].[dbo].[X22] as A
20: join [tst1].[dbo].[X22] as B on A.I+1=B.I and B.Name='DisplayVersion'
21: join [tst1].[dbo].[X22] as C on A.I+2=C.I and C.Name='Publisher'
22: join [tst1].[dbo].[X22] as D on A.I+3=D.I and D.Name='Size'
23: join [tst1].[dbo].[X22] as E on A.I+4=E.I and E.Name='InstallDate'
24: join [tst1].[dbo].[X22] as F on A.I+5=F.I and F.Name='UninstallString'
25: where A.Name='DisplayName'
26: and not (len(A.VAL)=0 and len(B.VAl)=0 and len(C.VAL)=0 and len(D.val)=0 and len (E.VAL)=0 and len(F.VAL)=0)
27: and C.VAL <>' Microsoft Corporation'
28: order by DisplayName
29: ;
30: select '<tr><td>' + str(i)
31: + '</td><td>'+ DisplayName
32: + '</td><td>'+ DisplayVersion
33: + '</td><td>'+ Publisher
34: + '</td><td>'+ Size
35: + '</td><td>'+ InstallDate
36: + '</td><td>'+ UninstallString
37: + '</td></tr>'
38: from #tmp1
39: order by i
This script return table for installed program in my computer, that I named X22 or XEON or Black. Also for fast installation in new place I stored in my site list of main program of my working environment (created by the same way), for example - (2013 years) My working environment in Burgas or Моя девелоперская среда 2006.
