How to write Select query by taking n-1 column names

Question

In my database table i have

 sr_no
 FID
 DOJMU
 DOJMCIS
 Designation
 UpdateDate

I have to write a select command such that i should select every field except sr_no.I know that we can wite it as Select FID,DOJMU,DOJMCIS,Designation,UpdateDate from TableName.But for me i need the query as select * from table name But it should exclude one column name is it posssible?

As i need to execute this query in C#(asp.net with c#),I have the following code

select p.Name,m.* from Publication_det m INNER JOIN Personal_det p  ON m.FID= p.FID

where m.updateDate between @Start and @End Here you can see that m.* represents the above select * from table name,so while executing sr_no will also being displayed,Hence i need a solution for using n-1 column names.

`Select *` selects ALL from table - i think there's no way except writing individual column name for it — Shumail, Oct 05 '13 at 09:14

Joachim Isaksson · Answer 1 · 2013-10-05T09:23:09.823

There's no standard SQL way to do it with a select, either you'll have to list them or select them all. In TSQL, a dynamic query may give you the result you're asking for, but the complexity is quite a bit higher than your regular select.

If it's more or less a static query, you can create a view for it though;

CREATE VIEW test_view AS 
  SELECT FID,DOJMU,DOJMCIS,Designation,UpdateDate 
  FROM test;

Then a regular;

SELECT * FROM test_view;

...will get the columns you want.

Giannis Paraskevopoulos · Answer 2 · 2013-10-05T09:21:07.733

You could do it with a dynamic SQL query.

For instance:

DECLARE @cols AS VARCHAR(MAX)
DECLARE @SQL AS VARCHAR(MAX)

SET @cols = STUFF
            (
                (
                    SELECT distinct ',' + QUOTENAME([name]) 
                    FROM   syscolumns 
                    WHERE  id = 
                           (
                               SELECT id 
                               FROM   sysobjects 
                               WHERE  type = 'V' 
                                      AND [Name] = 'TableName'
                           ) 
                           AND [name] not in ('sr_no')--you may define more names here
                    FOR XML PATH(''), TYPE
                 ).value('.', 'NVARCHAR(MAX)') 
                 ,1
                 ,1
                 ,''
            )

SET @SQL = 'SELECT ' + @cols + ' FROM TableName'

EXEC(@SQL)

score 0 · Answer 3 · answered Oct 05 '13 at 09:47

You have two options :

1) declare @tablename varchar(50) = 'submission_mst'

select 'Select '+
stuff
((
select ', '+ t.name
from  (
select name ,[object_id] from sys.columns where object_id in (select object_id from   sys.tables where name =@tablename)
and name <>'MGAName'
)t
for xml path ('')),1,1,'') + ' from'+ @tablename

2)

SELECT * INTO #TempTable  FROM tablename
ALTER TABLE #TempTable  DROP COLUMN columnname
SELECT * FROM #TempTable
DROP TABLE #TempTable

How to write Select query by taking n-1 column names

3 Answers3