Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Using sysObjects, sysColumns to retrieve dynamic data

Posted on 2004-12-01
16
Medium Priority
?
1,811 Views
Last Modified: 2008-02-07
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.
0
Comment
Question by:AlfaNoMore
  • 7
  • 5
  • 2
  • +1
16 Comments
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12718123
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12718172
"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
 
LVL 5

Expert Comment

by:PaulKeating
ID: 12718273
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 9

Author Comment

by:AlfaNoMore
ID: 12718386
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
 
LVL 18

Assisted Solution

by:SjoerdVerweij
SjoerdVerweij earned 300 total points
ID: 12718481
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12718649
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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12718713
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12718766
or using local temp tables.
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12718850
Can't do that. Local temp tables aren't visible within the scope of the Exec (@var).
0
 
LVL 5

Expert Comment

by:PaulKeating
ID: 12718935
You said you wanted it without temp tables or cursors.
0
 
LVL 18

Accepted Solution

by:
ShogunWade earned 450 total points
ID: 12718957
"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
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12718975
I stand corrected.
0
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12718995
"I stand corrected."   lol,   i said the same thing a few months ago when I was corrected by arbert (i think it was)
0
 
LVL 18

Expert Comment

by:SjoerdVerweij
ID: 12719385
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
 
LVL 9

Author Comment

by:AlfaNoMore
ID: 12724224
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
 
LVL 18

Expert Comment

by:ShogunWade
ID: 12724398
"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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question