Selecting All the columns except IDENTITY Column

Question

I am trying to display all the columns available in the table EXCEPT IDENTITY COLUMN. Is it possible?

Pseudo code would be something like this.

SELECT * FROM Tab1 WHERE IDENTIY = NULL

or

SELECT * FROM Tab1 WHERE IDENTIY <> 'YES'

This is obviously invalid. But I want to achieve something like this. Is it possible??

PS. I am doing this through SQL server connection from VBScript, and I dont want to hard code the column names( Obviously for flexibility).

Here's the VbScript snippet.

SQL_Cols = "select Column_name from Information_schema.columns where Table_name = 'Tab1'"
Recordset.open SQL_Cols,ConnString
p = recordset.getrows()
STR_Cols = ""
for i=1 to Ubound(p,2)   ' from 1 to UBound since in p(0,0) name of identity column is stored   
    if i = Ubound(p,2) Then
        STR_Cols  = STR_Cols  &"["&p(0,i)&"]" 
    else
        STR_Cols  = STR_Cols  &"["&p(0,i)&"],"
    end if
NEXT
Recordset.close

STR_Cols stores all the column names (Except 1st one (which is an identity Column))

Which i am later using to perform the down streaming task. This is working absolutely fine only one thing. This would work only if the 1st column is IDENTIY column, if the table is altered, the code will have to be changed. I want the code to be as flexible and robust as possible.

Answer 1

try like this:

sys.columns=Contains All Columns regarding to Particular Database

sys.tables=Contains All Tables regarding to Particular Database

sys.identity_columns=Contains All identity Columns regarding to Particular Database

So Result Can be Generated By joins with is_identity=1 and Exclude Identity column with Not IN with all columns to particular table

select c.name from sys.columns c
join sys.tables AS t
on t.object_id=c.object_id
where c.name not in (select name from sys.identity_columns where is_identity=1)
and  t.name='MyTableName'

Answer 2

Try this. Use Sys.columns table to get the column list without identity column. Then use Dynamic SQL to generate column list and execute the query

declare @ collist varchar(max)='',@ sql nvarchar(max)
select @collist += ','+name from sys.columns where object_name(object_id)='Tab1' and is_identity <> 1
select @collist = right(@collist,len(@collist)-1)
set @sql ='select '+@collist+ ' from Tab1'
exec sp_executesql @sql