GuruHub: How to get all columns in SQL Server

How to check if a column exists in a SQL Server database using VB.NET. This code uses ADO.NET to execute a query that checks for the existence of a column in a specified table.

To return the list of column names that exist in a SQL table, you can use the following SQL query along with a VB.NET implementation. Here’s a complete example to achieve that.

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'YourTableName';

Example in VB.NET

Here’s how to implement this in VB.NET, retrieving the column names for a specific table:

Imports System.Data.SqlClient

Module Module1

Sub Main()

Dim connectionString As String = "YourConnectionStringHere"

Dim tableName As String = "YourTableName" ' Replace with your actual table name

Using connection As New SqlConnection(connectionString)

connection.Open()

Dim query As String = "SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS " & _

"WHERE TABLE_NAME = @TableName"

Using command As New SqlCommand(query, connection)

command.Parameters.AddWithValue("@TableName", tableName)

Using reader As SqlDataReader = command.ExecuteReader()

If reader.HasRows Then

Console.WriteLine("Columns in table '" & tableName & "':")

While reader.Read()

Console.WriteLine(reader("COLUMN_NAME").ToString())

End While

Else

Console.WriteLine("No columns found in the specified table.")

End If

End Using

End Sub

End Module

Explanation

Connection String: Replace "YourConnectionStringHere" with your actual connection string.
Table Name: Set tableName to the name of the table you want to check (replace YourTableName).
SQL Query: The query retrieves the column names from the INFORMATION_SCHEMA.COLUMNS view.
Parameters: Uses a parameter to safely pass the table name.
Data Reader: Executes the query and reads the results. If columns are found, it prints each column name.

This code will output the list of column names for the specified table.