Tag: example

SQL get list of db tables and fields

December 10, 2021 /

This is how to get a list of all tables and fields in a database.

SELECT T.name AS Table_Name ,
C.name AS Column_Name ,
P.name AS Data_Type ,
P.max_length AS Size ,
CAST(P.precision AS VARCHAR) + ‘/’ + CAST(P.scale AS VARCHAR) AS Precision_Scale
FROM sys.objects AS T
JOIN sys.columns AS C ON T.object_id = C.object_id
JOIN sys.types AS P ON C.system_type_id = P.system_type_id
WHERE T.type_desc = ‘USER_TABLE’
and not P.name = ‘sysname’

Alternatively for a single table:

SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = ‘expense2’

SQL Delete duplicate rows based on column

May 30, 2019 /

To remove duplicate rows, use a CTE and partition using the data you want to check for duplicates to assign a row_number against. Then remove any where the row_number > 1 (removing multiple duplicates)

WITH CTE AS
(SELECT ROW_NUMBER() OVER
(PARTITION BY computername ORDER BY ( SELECT 0)) RN
FROM temp_devicerefresh)
DELETE FROM CTE WHERE RN > 1

Get List of Files In a Directory

January 30, 2019 /

This example shows how to get a list of all executables in a specified directory.  The System.IO reference must be imported.

Imports System.IO

Dim d As DirectoryInfo = New DirectoryInfo("C:\")

For Each File As FileInfo In d.GetFiles("*.exe")
  Console.WriteLine(File.Name)
Next

TrimEnd & Like in VB.NET

January 25, 2019 /

This example illustrates trimming the “!” character from the end of the string

 
    Dim str As String = "This is a string, with a comma!"
    str = str.TrimEnd(CChar("!"))
    MsgBox(str)

This example look for 4 numbers after 2 specific characters

 
    Dim phoneNum As String = "CT8964"
    If (phoneNum Like "CT####") Then
      MsgBox("Found it")
    Else
      MsgBox("Nope")
    End If

Compare Files

January 15, 2019 /

This example illustrates how to compare two files.

Imports System.IO
Imports System.Security.Cryptography

Public Class Form1

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

If CompareFiles("C:\Temp\2filecopy.bat", "C:\Temp\4filecopy.bat") = True Then
  MsgBox("The same")
Else
  MsgBox("Different")
End If

  End
End Sub

Public Function CompareFiles(ByVal FileFullPath1 As String, ByVal FileFullPath2 As String) As Boolean
'returns true if two files passed to is are identical, false otherwise
'does byte comparison; works for both text and binary files

'Throws exception on errors; you can change to just return 
'false if you prefer

Dim objMD5 As New MD5CryptoServiceProvider()
Dim objEncoding As New System.Text.ASCIIEncoding()
Dim aFile1() As Byte, aFile2() As Byte
Dim strContents1, strContents2 As String
Dim objReader As StreamReader
Dim objFS As FileStream
Dim bAns As Boolean

'If Not File.Exists(FileFullPath1) Then _
' Throw New Exception(FileFullPath1 & " doesn't exist")
'If Not File.Exists(FileFullPath2) Then _
' Throw New Exception(FileFullPath2 & " doesn't exist")

If Not File.Exists(FileFullPath1) Or Not File.Exists(FileFullPath2) Then
  Return False
  Exit Function
End If

Try
  objFS = New FileStream(FileFullPath1, FileMode.Open)
  objReader = New StreamReader(objFS)
  aFile1 = objEncoding.GetBytes(objReader.ReadToEnd)
  strContents1 = _
  objEncoding.GetString(objMD5.ComputeHash(aFile1))
  objReader.Close()
  objFS.Close()

  objFS = New FileStream(FileFullPath2, FileMode.Open)
  objReader = New StreamReader(objFS)
  aFile2 = objEncoding.GetBytes(objReader.ReadToEnd)
  strContents2 = _
  objEncoding.GetString(objMD5.ComputeHash(aFile2))

  bAns = strContents1 = strContents2
  objReader.Close()
  objFS.Close()
  aFile1 = Nothing
  aFile2 = Nothing

Catch ex As Exception
  Throw ex
End Try

Return bAns
End Function
End Class