Tag: syntax

Identify all user keys in HKEY_USERS

January 5, 2018 /

This example shows how to enumerate the user keys in HKEY_USERS

Public Sub OutputRegKey(Key As RegistryKey)
Imports Microsoft.Win32

Public Class Form1
  Public arrUsers(100) As String, arrUsersMax As Integer = 0

  Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
    Dim i As Integer
    Dim txt As String = ""

    '-------------------------------------------------------------------
    ' identify the HKEY_Users
    '-------------------------------------------------------------------
    arrUsers(0) = ".DEFAULT"

    Dim regkey As RegistryKey = Registry.Users.OpenSubKey("")
    EnumerateHkeyUsers(regkey)
    regkey.Close()

    For i = 0 To arrUsersMax
      txt += arrUsers(i) & vbCrLf
    Next

    MsgBox(txt)
    End
  End Sub

  Sub EnumerateHkeyUsers(ByVal rkey As RegistryKey)
    Dim names As String() = rkey.GetSubKeyNames()
    Dim txt As String = ""

    For Each subkey In names
      txt = subkey.ToString

      If Len(txt) > 10 And InStr(LCase(txt), "_classes") = 0 Then
        arrUsersMax = arrUsersMax + 1
        arrUsers(arrUsersMax) = txt
      End If
    Next
  End Sub

End Class

SQL Left Function

September 27, 2017 /

The LEFT function allows you to extract a substring from a string, starting from the left-most character.

LEFT( string, number_of_characters )

Example:

SELECT LEFT('TechOnTheNet.com', 4);
Result: 'Tech'

SQL Delete Duplicate Records

September 19, 2017 /

This example shows how to remove duplicate uid records from a table named AD_Users

WITH CTE AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY uid ORDER BY uid) AS Row, * FROM AD_Users
)
DELETE FROM CTE
WHERE ROW <> 1

Read Values From ListBox and Reference a 2-Dimensional Array

July 20, 2017 /

This example shows how to read entries from a ListBox and reference an array which contains numeric values corresponding to the selection.  This is useful if you want to do logic AND statements with the selection from the ListBox.

I used this method to assign numeric tags to the ListBox items.  This allowed me to assign a unique value to each combination of selections made.

Public Class Form1
 Dim arrTag(2, 5) As String, arrTagMax As Integer

Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
 LoadArray()
 End Sub

Private Sub ListBox1_SelectedIndexChanged(sender As System.Object, e As System.EventArgs) Handles lstACLGroups.SelectedIndexChanged
 Dim txt As String = ""
 Dim acc As Integer = 0

For i = 0 To lstACLGroups.SelectedItems.Count - 1
 txt += lstACLGroups.SelectedItems(i).ToString & vbCrLf

For j = 1 To 5
 If lstACLGroups.SelectedItems(i).ToString = arrTag(2, j) Then
 acc = acc + CInt(arrTag(1, j))
 End If
 Next
 Next

 TextBox1.Text = txt
 TextBox2.Text = acc.ToString
 End Sub

Sub LoadArray()
 Dim i As Integer

arrTag(1, 1) = 1
arrTag(2, 1) = "1 apple"

arrTag(1, 2) = 2
arrTag(2, 2) = "2 banana"

arrTag(1, 3) = 4
arrTag(2, 3) = "4 coconut"

arrTag(1, 4) = 8
arrTag(2, 4) = "8 dog"

arrTag(1, 5) = 16
arrTag(2, 5) = "16 elephant"

lstACLGroups.Items.Clear()
 For i = 1 To 5
   lstACLGroups.Items.Add(arrTag(2, i))
 Next

End Sub
End Class

SQL Database Table Sizes

May 25, 2017 /

The following SQL command lists each table in a database and shows total row counts and disk spaced used.

SELECT 
  t.NAME AS TableName,
  s.Name AS SchemaName,
  p.rows AS RowCounts,
  SUM(a.total_pages) * 8 AS TotalSpaceKB, 
  SUM(a.used_pages) * 8 AS UsedSpaceKB, 
  (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB
FROM 
  sys.tables t
INNER JOIN 
  sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN 
  sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN 
  sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN 
  sys.schemas s ON t.schema_id = s.schema_id
WHERE 
  t.NAME NOT LIKE 'dt%' 
  AND t.is_ms_shipped = 0
  AND i.OBJECT_ID > 255 
GROUP BY 
  t.Name, s.Name, p.Rows
ORDER BY 
  t.Name