Selecting all columns except the IDENTITY column
I am trying to display all the columns available in an EXCEPT ID table . Is it possible?
The pseudocode would be something like this.
SELECT * FROM Tab1 WHERE IDENTIY = NULL
or
SELECT * FROM Tab1 WHERE IDENTIY <> 'YES'
This is clearly not true. But I want to achieve something similar. Is it possible?
PS. I am doing this via a SQL server connection from VBScript and I don't want to hardcode the column names (obviously for flexibility).
Here is a 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 the 1st (which is the identification column))
which I later use to do the task downstream. Only one thing works great. This will only work if the first column has an IDENTIY column, if the table is changed the code will need to be changed. I want the code to be as flexible and robust as possible.
source to share
try like this:
sys.columns
= Contains all columns related to an individual database
sys.tables
= Contains all tables related to a single database
sys.identity_columns
= Contains all identity columns relative to a specific database
So the result can be generated joins
with a column is_identity=1
and Exclude Identity with Not IN
with all columns in a specific 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'
source to share
Try it. Use a table Sys.columns
to get a list of columns without identity column
. Then use Dynamic SQL
to create a list of columns and run a 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
source to share
You will get the Identity columns by running the following query:
SELECT
clmns.name
FROM
sys.tables AS tbl
INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id
LEFT OUTER JOIN sys.indexes AS ik ON ik.object_id = clmns.object_id and 1=ik.is_primary_key
LEFT OUTER JOIN sys.index_columns AS cik ON cik.index_id = ik.index_id and cik.column_id = clmns.column_id and cik.object_id = clmns.object_id and 0 = cik.is_included_column
where tbl.name = N'Tab1' AND is_identity = 1
source to share
here the solution is the same as what Pradeep suggested:
SQL_Cols = "declare @collist varchar(max)=''; select @collist += ', ' + name from sys.columns where object_name(object_id) = 'Tab1' and is_identity <> 1; select @collist = right(@collist,(case when len(@collist) > 2 then len(@collist)-2 else @collist end)); select @collist as [ColumnNames];"
Recordset.open SQL_Cols,ConnString
p = recordset.getrows()
STR_Cols = p(0,0)
check value STR_Cols
source to share