link system tables to user tables

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)

link system tables to user tables

Author

Topic

Dennis Falls
Starting Member

41 Posts

Posted - 2006-05-15 : 12:21:17

I have a table, tblAllData that contains multiple columns that start with diagcode, for example; diagcode1, diagcode2 ....

Is there anyway, using sysobjects and syscolumns to select from the tblAllData table where syscolumns.name like 'diagcode%' ?

nr
SQLTeam MVY

12543 Posts

Posted - 2006-05-15 : 13:05:00

declare @sql varchar(8000)
select @sql = coalesce(@sql + ',','') + name from sycolumns where id = object_id('tblAllData') and name like 'diagcode%'
select @sql = 'select ' + @sql + ' from tblAllData'
exec (@sql)

consider using varchar(max) if you are v2005 or splitting the variable up.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-15 : 13:09:18

You'll have to use dynamic sql. Something like this...

(watch out for the drop table statement!)

--data
create table tblAllData (diagcodeA int, diagcodeB int, C int, D int, diagcodeE int)
insert tblAllData
          select 1, 2, 3, 4, 5
union all select 6, 7, 8, 9, 10

select * from tblAllData

--calculation
declare @s nvarchar(4000)
set @s = 'select '
select @s = @s + COLUMN_NAME + ',' from information_schema.columns
    where TABLE_NAME = 'tblAllData' and COLUMN_NAME like 'diagcode%'
set @s = left(@s, len(@s)-1) + ' from tblAllData'

exec sp_executesql @s

drop table tblAllData

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

RyanRandall
Master Smack Fu Yak Hacker

1074 Posts

Posted - 2006-05-15 : 13:10:31

Nigel!

Stop beating me to it!

Ryan Randall
www.monsoonmalabar.com London-based IT consultancy

Solutions are easy. Understanding the problem, now, that's the hard part.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources