November 19, 2007 at 3:12 pm
I am in need of a query that will return all of the column names in a table that have a specific value, specifically all coumns that contain a value of 1.
Anyone have any ideas?
Thanks,
Lee
November 21, 2007 at 6:47 am
just query sys.columns - I'm sure that somewhere on the ms site there is a pdf of the system tables and views - you can work out how to do that from there.
something like this:
select * from sys.columns where [object_id]=object_id(mytablename)
and [name] like '%1%';
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
October 30, 2014 at 11:42 am
I realize this is several years later, but I'm also looking for a solution. But the solution provided here gets the names of columns that have a value in the name of the column, not within the data. I think the original question was looking for code that would return any column name of a table where the column's data contains a specified value. I could be misinterpreting though, so forgive me if that's the case. But regardless, I'd like to know the answer to what I interpreted the question to be. I've found plenty of code that shows how to return a the name of the column if that column name contains a specific value, but nothing to return a column name if that field contains a specific value.
October 30, 2014 at 12:36 pm
You can simulate this with dynamic SQL - it's messy, but it works.
with Desired_Columns as (
select s.name as SchemaName
, t.name as TableName
, c.name as ColumnName
, ROW_NUMBER() OVER (ORDER BY s.name, t.name, c.name) as Row_Num
from sys.columns c
inner join sys.tables t
on c.object_id = t.object_id
inner join sys.schemas s
on s.schema_id = t.schema_id
inner join sys.types ty
on c.system_type_id = ty.system_type_id
-- Put in desired datatypes here
where ty.name in ('bit','int')
),
InnerQuery as (
select 'select ''' + ColumnName + ''' as ColName '
+ ', ''' + SchemaName + '.' + TableName + ''' as TableName '
+ ', case when exists (select 1 from ' + SchemaName + '.'
+ TableName + ' where ' + ColumnName + ' = 1) then '
+ '''True'' '
+ 'else NULL end as Has_1 '
+ case when exists (
select 1
from Desired_Columns b
where a.Row_Num < b.Row_Num )
then 'UNION ALL'
else ''
end as Query
, Row_Number() OVER (ORDER BY SchemaName, TableName, ColumnName) as Row_Num
from Desired_Columns a
),
OuterQuery as (
select 'select ColName, TableName from (' as Query
, 0 as Row_Num
union all
select ') t0 where t0.Has_1 is not null ' as Query
, (select max(Row_Num) from InnerQuery) + 1 as Row_Num
),
WholeQuery as (
select *
from OuterQuery
union all
select *
from InnerQuery
)
select Query
from WholeQuery
order by Row_Num
The output of THAT should be something you can copy and paste to get a list of columns. You could potentially use exec() to run the output automatically - my solution is usually good enough for me.
October 31, 2014 at 4:22 pm
Thanks for the response. If I'm reading this correctly it searches for datatypes?
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply