SQL SERVER - How To Retrieve Records From a Table with out Specifying the Column Names

Let us Consider a case where we want to List all the records of all columns of a table called Organization. In general, we will make use the following Query.

Select * From Organization

Here we may or may not know the Columns Names of the table Organization. The ‘*’ in the above query will take care of everything and displays all the records of all the columns as shown below.

Now Let us assume that we are not Aware of the Column Names of the table Organization and we want to list the First 5 Column records from the table Organization. How to do this??

Here is the Sample Code to achieve this.

Method 1 :

Here we have used the SysColumns table to Retrieve First 5 column names from the table Oraganization.

Sample Code :

Declare @Columns nvarchar(4000)
Declare @Query nvarchar(4000)

-- To get the Column Names each separated by comma
Select @Columns = Coalesce(@Columns + ',','') + Name From SysColumns where ID = Object_ID('Organization') And
ColOrder <=5

--Build Select Statement
Set @Query = 'Select ' + @Columns + ' From Organization'

Exec SP_ExecuteSql @Query

Method 2 :

Here we have used the Information_schema.Columns to Retrieve First 10 column names from the table Oraganization.

Sample Code :

Declare @Columns nvarchar(4000)
Declare @Query nvarchar(4000)

-- To get the Column Names each separated by comma
Select @Columns = Coalesce(@Columns + ',','') + Column_Name From Information_schema.Columns where Table_name Like 'Organization' And
Ordinal_Position <=5

--Build Select Statement
Set @Query = 'Select ' + @Columns + ' From Organization'

Exec SP_ExecuteSql @Query

The Final Output is :