Tag: database

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 replace string in a field

November 9, 2018 /

The example replaces the string height=”480″ with the string height=”320″.

UPDATE Blog
SET myPost = REPLACE(CAST(myPost AS varchar(MAX)),’height=”480″‘, ‘height=”320″‘)
FROM Blog
WHERE CHARINDEX(‘height=”480″‘,CAST(myPost as varchar(MAX)))>0
AND myKey=2469

VB.NET Module for SQL Database

February 13, 2018 /

Imports System.Data.SqlClient

Module modDatabase

Public conn As New SqlClient.SqlConnection
Public myReader As SqlDataReader

Public dsRecords As New DataSet
Public daRecords As New SqlDataAdapter
Public MaxRecords As Integer

Public Sub OpenDatabase()
CloseSQLReader()
CloseDatabase()

conn.ConnectionString = “Server=.\sqlexpress;Database=DATABASE;Trusted_Connection=True;”
conn.Open()
End Sub

 

Public Sub CloseDatabase()
Try
conn.Close()
conn.Dispose()
Catch ex As Exception
Application.DoEvents()
End Try
End Sub

 

Public Sub launchBrowser(ByVal browser As String, ByVal url As String)
Try
Process.Start(browser, url)
Catch ex As Exception
Application.DoEvents()
‘MessageBox.Show(ex.Message)
End Try
End Sub

 

Public Sub CloseSQLReader()
Try
myReader.Close()
Catch ex As Exception
Application.DoEvents()
End Try
End Sub

 

Public Sub ReadSqlData(ByVal sqlSelect As String)
Dim myCommand As New SqlCommand(sqlSelect, conn)
myReader = myCommand.ExecuteReader()
End Sub

 

Public Function ExecuteSqlCommand(ByVal sqlSelect As String) As Boolean
Dim flg As Boolean = False
Dim myCommand As New SqlCommand(sqlSelect, conn)

Try
flg = myCommand.ExecuteNonQuery
Catch
flg = False
End Try

Return flg
End Function

 

Public Sub HoldYourPants(ByVal Seconds As Integer)
System.Threading.Thread.Sleep(Seconds * 1000)
Application.DoEvents()
End Sub

End Module

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