Selecting SQL Column Names for use in a query

pts.
Tags:
SQL
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

I would do this in 2 passes, one to generate a script looking for all columns beginning with ‘Z’ and then the results would look in those columns for ‘grant’ as follows:

select ‘select * from ‘+table_name+’ where ‘+column_name+’ =grant’
from information_schema.columns
where COLUMN_NAME like ‘Z%’ and data_type in (‘varchar’,’nvarchar’)

Discuss This Question: 3  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • Kurthp
    Sorry, missed some quotes on previous post. Try this one: select 'select * from '+table_name+' where '+column_name+" ='grant'" from information_schema.columns where COLUMN_NAME like 'A%' and data_type in ('varchar','nvarchar')
    195 pointsBadges:
    report
  • Kurthp
    And one more time in Oracle... select 'select * from '||table_name||' where '||column_name||' =''grant''' from dba_tab_columns where COLUMN_NAME like 'A%' and data_type in ('VARCHAR2','CHAR','NVARCHAR2','NCHAR')
    195 pointsBadges:
    report
  • Sgraham
    Thank you for the code, it helped me along the way, but wasn't a total answer. I also got some help from Tek-Tips.com and this is what they came up with. It works, but I have not modified it to suite my needs just yet, but again, Thanks! ====: Code Block :==== declare @mycolumn varchar(20), @mytable varchar(20), @sql nvarchar(1000) --create a cursor containing column and table names you want declare coltab cursor for Select column_name, table_name from INFORMATION_SCHEMA.COLUMNS where column_name like 'z%' or column_name = 'grant' open coltab --throw first row into variables fetch next from coltab into @mycolumn, @mytable --here, I build a temp table to hold the data we pull create table #temp (oldCol nvarchar(20), data nvarchar(20)) --create a sql string using these variables in place of column names and table names set @sql = 'insert into #temp (oldCol, data) (select ''' + @mytable + '_' + @mycolumn + ''', ' + @mycolumn + ' from ' + @mytable + ')' --execute the sql command exec sp_executesql @sql --pull the next row in the cursor fetch next from coltab into @mycolumn, @mytable --start a loop to get through the entire cursor 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 --close and destroy the cursor close coltab deallocate coltab --now get the data from the temp table and drop it select * from #temp drop table #temp
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following