Using sysObjects, sysColumns to retrieve dynamic data

Hello all,

assuming I had one table as following:

cnf_Attendee

AttendeeID
Name
Address
Email

And I would like to use the following SQL to retrieve a recordset of column names, joined to this table with the correct column being returned.

SELECT
      C.name
FROM
      sysobjects AS O
INNER JOIN
      syscolumns AS C
      ON C.id = O.id
/*
INNER JOIN
      dbo.cnf_Attendee AS Data
      ON 1 = 1
*/
WHERE
      O.xtype = 'U' AND
      O.name LIKE 'cnf_%' AND
      C.colid > 1


Currently this would give me a Recordset of:

AttendeeID
Name
Address
Email

But if I had the following data inside my cnf_Attendee table:

1,    Test1,    Test1, test@test.com
2,    Test2,    Test2,  test2@test.co.uk

I would like the following data inside my Recordset (after joining the data table):

AttendeeID     1
Name            Test1
Address         Test1
Email             test@test.com
AttendeeID     2
Name            Test2
Address         Test2
Email             test2@test.co.uk

Is this possible without using a cursor or temporary table?????

Many thanks.
LVL 9
AlfaNoMoreAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ShogunWadeCommented:
Firstly you dont need to / shouldnt use the sys tables to get the list of columns for a given table.    You should use the information_schema

SELECT column_name from INFORMATION_SCHEMA.columns WHERE table_name='MyTable'
0
ShogunWadeCommented:
"Is this possible without using a cursor or temporary table?????"    It could be done with dynamic sql,  but its a question of "which is the lesser of the evils".
0
PaulKeatingCommented:
This does what you want, but you will have to reorder the data that you get, maybe by putting a key in the SELECT and sorting.

You can't have the values from the target table back in a single column from a single query, because they are different datatypes.

AttendeeID     1                         integer
Name            Test1                   char
Address         Test1                   char
Email             test@test.com      char

This retrieves data from only one table but it shows the technique. You would need another loop to go through table names.

This is how we used to do loops in T-SQL before it had cursors. As a rule, it's still generally faster, sometimes much faster.

declare @select varchar(255)
declare @colid  int
declare @table  varchar(255)

select @table = 'cnf_Attendee'
select @colid = 1

while @colid is not null
  begin
  select @select = 'select ' + char(39) + name + char(39) + ',' + name + ' from ' + @table
         from syscolumns where  id = object_id(@table) and colid = @colid
  exec ( @select )
  select @colid = min(colid) from syscolumns where  id = object_id(@table ) and colid > @colid
  end
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

AlfaNoMoreAuthor Commented:
I've gone down the following route, as I need ONE recordset to be returned, and not a combination of them! I'm sure this is BAD, so if you can tweak this, please feel free. PaulKeating, I used both a cursor and a loop, and couldn't notice any real difference in timing?

Combination of all comments:

SET NOCOUNT ON

CREATE TABLE ##ResponseColumns (
      ResponseID      int,
      ColumnName      varchar(255),
      ResponseValue      varchar(255)
)

DECLARE @column            varchar(255)
DECLARE @SQL            varchar(4000)

DECLARE columns_cursor CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.columns
WHERE table_name='cnf_ViewAttendeeData'

OPEN columns_cursor

FETCH NEXT FROM columns_cursor
INTO @column

WHILE @@FETCH_STATUS = 0
BEGIN

      SET @SQL = 'INSERT INTO ##ResponseColumns
                   SELECT AttendeeID_PK, ''' + @column + ''', ' + @column + '
                   FROM cnf_ViewAttendeeData'
      EXEC(@SQL)

      FETCH NEXT FROM columns_cursor
      INTO @column

END

CLOSE columns_cursor

DEALLOCATE columns_cursor


SELECT * FROM ##ResponseColumns


DROP TABLE ##ResponseColumns

SET NOCOUNT OFF


0
SjoerdVerweijCommented:
Small tweak:

DECLARE columns_cursor CURSOR FOR
SELECT column_name
FROM INFORMATION_SCHEMA.columns
WHERE table_name='cnf_ViewAttendeeData'

can be

DECLARE columns_cursor CURSOR
Local Fast_Forward
FOR
SELECT column_name
FROM INFORMATION_SCHEMA.columns
WHERE table_name='cnf_ViewAttendeeData'
0
ShogunWadeCommented:
Most significant issue IMHO is the fact that you are using a global temp table.     What if two people call the proc @ the same time .... oops.
0
SjoerdVerweijCommented:
That can be fixed, actually, with the following code at the top of the procedure:

While Exists(Select *
             From TempDB.Information_Schema.Tables
             Where Table_Name = '##ResponseColumns')
  WaitFor Delay '000:00:03'

0
ShogunWadeCommented:
or using local temp tables.
0
SjoerdVerweijCommented:
Can't do that. Local temp tables aren't visible within the scope of the Exec (@var).
0
PaulKeatingCommented:
You said you wanted it without temp tables or cursors.
0
ShogunWadeCommented:
"Can't do that. Local temp tables aren't visible within the scope of the Exec (@var)."    That is a common misconception.    If the local temp table is created outside of the dynamic sql if can be seen inside it, but not the opposite way around :

try this test:

CREATE TABLE #a(a int)
insert #a values (1)
declare @sql varchar(1000)
set @sql='select * from #a'
exec (@sql)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SjoerdVerweijCommented:
I stand corrected.
0
ShogunWadeCommented:
"I stand corrected."   lol,   i said the same thing a few months ago when I was corrected by arbert (i think it was)
0
SjoerdVerweijCommented:
The worst about these misconceptions is that I'll spend the rest of the day kicking myself and wondering where the hell I got that idea -- and thus who to kick :-)
0
AlfaNoMoreAuthor Commented:
I tried this aproach (this morning) without using a temp table, but it takes so much longer than the cursor/temp table route, I think I'll have to go down that route! I think the fact that I 77 columns within my table (and consequently then within the case statement), that this takes so long?

SELECT
      Cols.column_name AS ColumnName,
      CASE Cols.column_name
      WHEN 'AttendeeID_PK' THEN CAST(AttendeeID_PK AS varchar)
      WHEN 'ConferenceID_FK' THEN CAST(ConferenceID_FK AS varchar)
      WHEN 'Email' THEN Email
      WHEN 'Title' THEN Title
      WHEN 'FirstName' THEN FirstName
      WHEN 'Surname' THEN Surname
      WHEN 'FirstNameBadge' THEN FirstNameBadge
      WHEN 'EmployeeNumber' THEN EmployeeNumber
      /* REMOVED EXTRA COLUMNS DUE TO SPACE */
      END AS ColumnValue
FROM
      INFORMATION_SCHEMA.columns AS Cols
INNER JOIN
      cnf_ViewAttendeeData AS Data
      ON 1 = 1 --Full Join
WHERE
      Cols.table_name='cnf_ViewAttendeeData'
0
ShogunWadeCommented:
"The worst about these misconceptions is that I'll spend the rest of the day kicking myself and wondering where the hell I got that idea"   lol,   and then like i did, spend the rest of the week changing code :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.