Retrieving the first n number of Columns without using Column names of a particular table ?????

hi! All

This is a Tricky Question Asked during Interviews to test a candidate Proficiency in SQL Server....

Question is "How Can i Display the records of first 10 Columns of a table without writing the Column names in our select statement????"
What if we don't know the columns names of a table ????????

Firstly Find out the Column Names of first 10 Columns of a Table

Use Northwind

SELECT column_name
from INFORMATION_SCHEMA.Columns
where table_name = 'customers'
and ordinal_position <= 10

Above Query will display names of All the Columns of a table so we need to use this and implement the code below to display the data contained in these columns

set nocount on
Declare @TableName varchar(200), @ColumnList varchar(1000)
Declare @SQL varchar(1000)
set @TableName = 'customers'

SELECT @ColumnList = COALESCE(@ColumnList + ', ', '') + column_name
FROM INFORMATION_SCHEMA.Columns
WHERE table_name = @TableName and Ordinal_position <= 10

Set @SQL = 'Select ' + @ColumnList + ' from ' + @TableName

Exec (@SQL)

Just paste the Above Query in your Query Analyser and see the Results.

Enjoy!!!