Select Columns Conditionally in SQL Server

Question

I want to select every column that its name does not contain Id

This is what I have tried:

Select *
From Table_Name
Where Column_Name Like '%Id%'

What is the solution?

Answer 1

I think you'll want to use INFORMATION_SCHEMA for this.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Name Of Your Table, As A String'
AND COLUMN_NAME NOT LIKE '%Id%'

More on INFORMATION_SCHEMA from MSDN.

Answer 2

You can do it like this. Assume table as column id,value1,value2. You need to select column name having name like value.

CREATE TABLE [dbo].[tbl1](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [value1] [varchar](2) NULL,
    [value2] [varchar](4) NULL)


GO

create table  #tblcols (colname varchar(100))

insert into #tblcols
select Column_name from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'tbl1'

delete from #tblcols where colname not like '%val%'
select * from #tblcols

declare @sql nvarchar(500)


SELECT @sql = COALESCE(@sql + ', ', ' ') +  colname FROM #tblcols


 set @sql = 'select ' + @sql + ' from tbl1'
 select @sql

 exec sp_executesql  @sql

 drop table  #tblcols

Then, using dynamic SQL create sql statement like select value1,value2 from tbl. And use EXEC SP_EXECUTESQL @SQL to get result.

Answer 3

Try this:

select * from sys.columns where
name NOT like '%ID%'

sys.columns

or

select * from INFORMATION_SCHEMA.COLUMNS
where COLUMN_NAME NOT   like '%ID%'

INFORMATION_SCHEMA.COLUMNS