Select column name and table name in table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Select column name and table name in table

Author

Topic

reenz
Starting Member

29 Posts

Posted - 2006-03-06 : 20:18:24

I have a table tableFields that will store the 'column name' and 'table the column belongs to'.

Eg.
Table [checkpoint] have columns [id],[place],[time]

So if i select * from tableFields, i wil get

column table flag
id checkpoint 0
place checkpoint 1
time checkpoint 1

i am trying to do a SELECT whereby the flag = 1.
Eg.
SELECT place,time
FROM checkpoint
WHERE flag='1'

Is it possible to do a SELECT directly from tableFields?

something in the line of

SELECT (SELECT column FROM tableFields)
FROM (SELECT table FROM tableFields)
WHERE (tableFields.flag ='1')

I know its not the correct syntax and such about i am completely clueless about how to go about doing it.

=(

Please help!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-06 : 20:21:13

you have to use Dynamic SQL for this. Check up exec() or sp_executesql

----------------------------------
'KH'

reenz
Starting Member

29 Posts

Posted - 2006-03-06 : 20:38:25

Does that means i have to use a cursor to keep loop so as to get a pair of column name and table name at a time?

As tableFields might be big, i really hope there can be other ways rather then looping and getting it one at a time.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-06 : 20:51:38

You don't have to use cursor. See this example.

create table #tableFields
(
	column_name	varchar(50),
	table_name	varchar(50),
	flag		int
)

insert into #tableFields
select	'id', 	 'checkpoint', 0	union all
select	'place', 'checkpoint', 1	union all
select	'time',	 'checkpoint', 1

declare
	@sql	varchar(1000)

select	@sql = 'select '

select	@sql = @sql + column_name + ','
from	#tableFields
where	flag	= 1
order by column_name

select	@sql = left(@sql, len(@sql) - 1) + char(13)
select	@sql = @sql + 'from '
select	@sql = @sql + (select distinct table_name from	#tableFields where	flag	= 1)

print	@sql

----------------------------------
'KH'

reenz
Starting Member

29 Posts

Posted - 2006-03-06 : 21:51:22

WOW! thanks a lot!

however i do not quite get this
select @sql = left(@sql, len(@sql) - 1) + char(13)

why do u need to + char(13)?

Also, is there a way to append ',' to distinct table names?

select @sql = @sql + (select distinct table_name from #tableFields where flag = 1)
will return a "subquery returned more than 1 value error"

and

select @sql = @sql + table_name + ','
from #tableFields
where flag = 1

gives redundant/repeated table names

and

select distinct @sql = @sql + table_name + ','
from #tableFields
where flag = 1

gives me only 1 table name!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-06 : 23:37:38

"why do u need to + char(13)?"
char(13) is the Carriage Return character. It serves as delimiter as well as make dynamic sql more readable when you display it using print

"Also, is there a way to append ',' to distinct table names? "

select @sql = @sql + table_name + ','
from  #tableFields
where flag = 1
group by table_name

If you have more than one table that fulfilled your criteria, you will have to take care of condition like
- same column name in different table (this you can always prefix the column name with the table name like table_name.column_name)
- identify the column that join the 2 table together. (this will be tricky)
Your current structure looks like does not handle the above scenario.

----------------------------------
'KH'

reenz
Starting Member

29 Posts

Posted - 2006-03-07 : 02:12:53

Oooh! thanks!

I've actually done the same thing with
select @sql = @sql + table_name + ','
from
(select * from (select distinct table_name from tableFields where flag =1))

i actually need to call this statement from a asp.net page so im thinking of puting these codes in a stored procedure and returning the data table. After puting the select statement in @sql, how do i actually execute it?

sp_executeSQL @sql or execute @s doesn't seem to do the trick...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-07 : 02:19:45

[code]exec(@sql)[/code]
or
[code]exec sp_executesql @sql[/code]

Use sp_executesql if you need to pass in parameters

----------------------------------
'KH'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources