SQL to return the first two columns of a table

Question

Is there a SQL lingo to return JUST the first two columns table WITHOUT knowing the field names ?

Something like

 SELECT Column(1), Column(2) FROM Table_Name 

Or do I need to go a long way and find out the column names first? How can I do it?

Answer 1

You should get the column names first. Most platforms support this:

 select column_name,ordinal_position from information_schema.columns where table_schema = ... and table_name = ... and ordinal_position <= 2 

Answer 2

He is there

 declare @select varchar(max) set @select = 'select ' select @ select=@select +COLUMN_NAME+',' from information_schema.columns where table_name = 'TABLE' and ordinal_position <= 2 set @select=LEFT(@select,LEN(@select)-1)+' from TABLE' exec(@select) 

Answer 3

I wrote a stored procedure some time ago to do this exact job. Although there is no technical column order in relationship theory, SSMS is not completely relational. The system stores the order in which the columns were inserted and assigns an identifier to them. This ordering is done using a typical SELECT * statement, so your SELECT statements seem to return the same order every time. In practice, there has never been a good idea to SELECT * with anything, since it does not block the order of the result in terms of columns or rows. However, I think that people are so obsessed with “that you shouldn't do this” that they don’t write scripts that actually can do this. The fact is the predictable behavior of the system, so why not use it if the task is not very important.

This SPROC, of ​​course, has reservations and is written in T-SQL, but if you just want to return all the values ​​with the same SELECT * behavior, this should make the job pretty easy for you. Enter a table name, number of columns and press F5. He returns them in order from left to right, just as you expected. I limited it to only 5 columns, but you can edit the logic if you need more. Accepts temporary and permanent tables.

EXEC OnlySomeColumns 'MyTable', 3

 /*------------------------------------------------------------------------------------------------------------------ Document Title: The Unknown SELECT SPROC.sql Created By: CR Date: 4.28.2013 Purpose: Returns all results from temp or permanent table when not knowing the column names SPROC Input Example: EXEC OnlySomeColumns 'MyTable', 3 --------------------------------------------------------------------------------------------------------------------*/ IF OBJECT_ID ('OnlySomeColumns', 'P') IS NOT NULL DROP PROCEDURE OnlySomeColumns; GO CREATE PROCEDURE OnlySomeColumns @TableName VARCHAR (1000), @TotalColumns INT AS DECLARE @Column1 VARCHAR (1000), @Column2 VARCHAR (1000), @Column3 VARCHAR (1000), @Column4 VARCHAR (1000), @Column5 VARCHAR (1000), @SQL VARCHAR (1000), @TempTable VARCHAR (1000), @PermanentTable VARCHAR (1000), @ColumnNamesAll VARCHAR (1000) --First determine if this is a temp table or permanent table IF @TableName LIKE '%#%' BEGIN SET @TempTable = @TableName END --If a temporary table IF @TableName NOT LIKE '%#%' BEGIN SET @PermanentTable = @TableName END --If a permanent column name SET NOCOUNT ON --Start with a few simple error checks IF ( @TempTable = 'NULL' AND @PermanentTable = 'NULL' ) BEGIN RAISERROR ( 'ERROR: Please select a TempTable or Permanent Table.',16,1 ) END IF ( @TempTable <> 'NULL' AND @PermanentTable <> 'NULL' ) BEGIN RAISERROR ( 'ERROR: Only one table can be selected at a time. Please adjust your table selection.',16,1 ) END IF ( @TotalColumns IS NULL ) BEGIN RAISERROR ( 'ERROR: Please select a value for @TotalColumns.',16,1 ) END --Temp table to gather the names of the columns IF Object_id('tempdb..#TempName') IS NOT NULL DROP TABLE #TempName CREATE TABLE #TempName ( ID INT, Name VARCHAR (1000) ) --Select the column order from a temp table IF @TempTable <> 'NULL' BEGIN --Verify the temp table exists IF NOT EXISTS ( SELECT 1 FROM tempdb.sys.columns WHERE object_id = object_id ('tempdb..' + @TempTable +'') ) BEGIN RAISERROR ( 'ERROR: Your TempTable does not exist - Please select a valid TempTable.',16,1 ) RETURN END SET @SQL = 'INSERT INTO #TempName SELECT column_id AS ID, Name FROM tempdb.sys.columns WHERE object_id = object_id (''tempdb..' + @TempTable +''') ORDER BY column_id' EXEC (@SQL) END --From a permanent table IF @PermanentTable <> 'NULL' BEGIN --Verify the temp table exists IF NOT EXISTS ( SELECT 1 FROM syscolumns WHERE id = ( SELECT id FROM sysobjects WHERE Name = '' + @PermanentTable + '' ) ) BEGIN RAISERROR ( 'ERROR: Your Table does not exist - Please select a valid Table.',16,1 ) RETURN END SET @SQL = 'INSERT INTO #TempName SELECT colorder AS ID, Name FROM syscolumns WHERE id = ( SELECT id FROM sysobjects WHERE Name = ''' + @PermanentTable + ''' ) ORDER BY colorder' EXEC (@SQL) END --Set the names of the columns IF @TotalColumns >= 1 BEGIN SET @Column1 = (SELECT Name FROM #TempName WHERE ID = 1) END IF @TotalColumns >= 2 BEGIN SET @Column2 = (SELECT Name FROM #TempName WHERE ID = 2) END IF @TotalColumns >= 3 BEGIN SET @Column3 = (SELECT Name FROM #TempName WHERE ID = 3) END IF @TotalColumns >= 4 BEGIN SET @Column4 = (SELECT Name FROM #TempName WHERE ID = 4) END IF @TotalColumns >= 5 BEGIN SET @Column5 = (SELECT Name FROM #TempName WHERE ID = 5) END --Create a select list of only the column names you want IF Object_id('tempdb..#FinalNames') IS NOT NULL DROP TABLE #FinalNames CREATE TABLE #FinalNames ( ID INT, Name VARCHAR (1000) ) INSERT #FinalNames SELECT '1' AS ID, @Column1 AS Name UNION ALL SELECT '2' AS ID, @Column2 AS Name UNION ALL SELECT '3' AS ID, @Column3 AS Name UNION ALL SELECT '4' AS ID, @Column4 AS Name UNION ALL SELECT '5' AS ID, @Column5 AS Name --Comma Delimite the names to insert into a select statement. Bracket the names in case there are spaces SELECT @ColumnNamesAll = COALESCE(@ColumnNamesAll + '], [' ,'[') + Name FROM #FinalNames WHERE Name IS NOT NULL ORDER BY ID --Add an extra bracket at the end to complete the string SELECT @ColumnNamesAll = @ColumnNamesAll + ']' --Tell the user if they selected to many columns IF ( @TotalColumns > 5 AND EXISTS (SELECT 1 FROM #FinalNames WHERE Name IS NOT NULL) ) BEGIN SELECT 'This script has been designed for up to 5 columns' AS ERROR UNION ALL SELECT 'Only the first 5 columns have been selected' AS ERROR END IF Object_id('tempdb..#FinalNames') IS NOT NULL DROP TABLE ##OutputTable --Select results using only the Columns you wanted IF @TempTable <> 'NULL' BEGIN SET @SQL = 'SELECT ' + @ColumnNamesAll + ' INTO ##OutputTable FROM ' + @TempTable + ' ORDER BY 1' EXEC (@SQL) END IF @PermanentTable <> 'NULL' BEGIN SET @SQL = 'SELECT ' + @ColumnNamesAll + ' INTO ##OutputTable FROM ' + @PermanentTable + ' ORDER BY 1' EXEC (@SQL) END SELECT * FROM ##OutputTable SET NOCOUNT OFF 

Answer 4

A dynamic query using for xml path also do the job:

 declare @sql varchar(max) set @sql = (SELECT top 2 COLUMN_NAME + ',' from information_schema.columns where table_name = 'YOUR_TABLE_NAME_HERE' order by ordinal_position for xml path('')) set @sql = (SELECT replace(@sql +' ',', ','')) exec('SELECT ' + @sql + ' from YOUR_TABLE_NAME_HERE') 

Answer 5

SQL does not understand the order of columns. You need to know the column names in order to get them.

You can view the information_schema query for column names. For example:

 SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE table_name = 'tbl_name' ORDER BY ordinal_position LIMIT 2; 

Answer 6

You can query sysobject tables to find out the first two columns, and then dynamically generate the required SQL query.

Answer 7

If you want a permant object that you can query over and over again, display a view for each table that returns only the first 2 columns. You can name columns Column1 and Column2 or use existing names.

If you want to return the first two columns from any table without any preprocessing steps, create a stored procedure that requests system information and performs a dynamic query that returns the first two columns from the table.

Answer 8

Or do I need to go a long way and find out the column names first? How can I do it?

This is pretty easy to do manually.
Just run this first

 select * from tbl where 1=0 

This operator works in all major DBMSs without any system directories. This gives you all the column names, then all you have to do is enter the first two

 select colname1, colnum2 from tbl