Selecting Column Names for use in a query 3

[sgraham]

How do I select the column names from a DB?

I'm looking to do something like:

SELECT * FROM db1
WHERE column_name like 'Z%' AND column_value = 'grant'

If you could educate me on this I would appreciate it.

Thanks for you time,
Steve Graham
Beginner

 

[Juice05]

What are you trying to accomplish by doing this? I am just trying to figure out whether or not there is a better approach to your problem.

 

[DotNetGnat]

try this:

SELECT * FROM information_schemas.columns WHERE column_name like 'Z%' AND column_value = 'grant'

-DNG

 

[amrita418]

Infomation_Schema does not has a column values (data) though you can get the column names from it.

Look into using sp_msforeachtable stored procedure, I think that might help you.

Regards,
AA

 

[amrita418]

Also, check thread183-1078033 or search for threads on this topic for the code you need.

Regards,
AA

 

[sgraham]

Thank you very much!

I'm working/reading through the code and links you've given me and learning a lot, however, I'm still having trouble with selecting only the column names that have a value of 'grant'.

I'll post my final solution when I find it.

Thanks again :0)

 

[sgraham]

btw juice,

What I'm trying to do is dynamically select only the column names that have actually been given the value of 'grant'. The solutions provided thus far have worked wonderfully, but are only step one.

Step One:
Select the column names - done.

Step Two:
Select only column names where like 'z%' with a value = 'grant'

Can step two be done without using a case statement? Should I use a JOIN, or a while loop, or ??

Amrita, I've done some initial checking into sp_msforeachtable in conjunction with @whereand, but understanding the dynamic SQL and concatination needed is still doing funny things to my head :0)

 

[amrita418]

Can you post the code you have so far?

 

[sgraham]

Ugg, I don't know... none of the idea's I'm having seem to pan out (mostly because I don't really know how to use SQL).

Is there a way to select the data in the column similar to information_schemas.columns? something like information_schemas.columns.data??

 

[Jasen]

What I came up with:

declare @mycolumn varchar(20), 
	@mytable varchar(20), 
	@sql nvarchar(1000)


[COLOR=green]--create a cursor containing column and table names you want[/color]
declare coltab cursor for Select column_name, table_name 
			 from INFORMATION_SCHEMA.COLUMNS 
			 where column_name like 'z%' or
			       column_name = 'grant'

open coltab
[COLOR=green]--throw first row into variables[/color]
fetch next from coltab into @mycolumn, @mytable

[COLOR=green]--here, I build a temp table to hold the data we pull[/color]
create table #temp (oldCol nvarchar(20), data nvarchar(20))

[COLOR=green]--create a sql string using these variables in place of column names and table names[/color]
set @sql = 'insert into #temp (oldCol, data) (select ''' + @mytable + '_' + @mycolumn + ''', ' + @mycolumn + ' from ' + @mytable + ')'
[COLOR=green]--execute the sql command[/color]
exec sp_executesql @sql

[COLOR=green]--pull the next row in the cursor[/color] 
fetch next from coltab into @mycolumn, @mytable
[COLOR=green]--start a loop to get through the entire cursor[/color]
while @@FETCH_STATUS = 0
   BEGIN
	set @sql =  'insert into #temp (oldCol, data) (select ''' + @mytable + '_' + @mycolumn + ''', ' + @mycolumn + ' from ' + @mytable + ')'
	exec sp_executesql @sql
	fetch next from coltab into @mycolumn, @mytable
   END

[COLOR=green]--close and destroy the cursor[/color]
close coltab
deallocate coltab

[COLOR=green]--now get the data from the temp table and drop it[/color]
select * from #temp
drop table #temp

You may have to adjust the sizes of the varchar's (or use a different data type) depending what's in these columns you're pulling.

________
Remember, you're unique... just like everyone else.

 

[Jasen]

edit: I misread the the OP. This will still work fine, but for the cursor you will have just

declare coltab cursor for Select column_name, table_name
             from INFORMATION_SCHEMA.COLUMNS
             where column_name like 'z%' or
                   column_name = 'grant'

and then the @sql string would look more like

set @sql = 'insert into #temp (oldCol, data) (select ''' + @mytable + '_' + @mycolumn + ''', ' + @mycolumn + ' from ' + @mytable + ' where '+ @mycolumn + ' = ''grant'')'

________
Remember, you're unique... just like everyone else.

 

[Jasen]

Must be getting late, I messed up the cursor part again. Remove the "or" part of that statement for it to work.

________
Remember, you're unique... just like everyone else.

 

[sgraham]

Wow - That's some piece of code!

With a bit of customization, adding a column or two, it’ll be able to do everything I need. Thanks so much for hand holding me; this has been an education to be sure!

Thanks to DotNetGnat & amrita418 as well. Each of you has given me much to look at and learn.


Thanks again,
Steve