SQL Server Performance

is this possible

Discussion in 'General Developer Questions' started by ranjitjain, Aug 18, 2005.

  1. ranjitjain New Member

    Hi Experts,
    I have a temp table with 4 columns and dynamically generated column names.
    I want to fire select query with column names to change it to alias names.
    something like one said to me in ORACLE
    select 1,2,3 from tabe1
    i.e. column 1, column 2 etc.
    So i want the way we specify nos. in order by clause like order by 1.
    Is this possible???
    When i fired select 1,2,3 from table1.....its printing no of rows with filled values i.e. 1,2,3.....

  2. FrankKalis Moderator

  3. ranjitjain New Member

    Hi Frank,
    Is there any other way to get those column names as its my global temp table.
  4. Madhivanan Moderator

    I am not sure what you are asking

    Is this you wanted?

    select [1],[2],[3] from tabe1




    Madhivanan

    Failing to plan is Planning to fail
  5. ranjitjain New Member

    Hi Madhivanan,
    I want to call the columns in select statement by column numbers, the way we call in order by clause i.e. order by 1......
  6. FrankKalis Moderator

    You would need a column named "1" in the table for this to work. Actually I think this is a way to get these informations:<br /><pre id="code"><font face="courier" size="2" id="code"><br />DECLARE @a VARCHAR(50), @b VARCHAR(50), @c VARCHAR(50), @d VARCHAR(50)<br />SELECT <br />@a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END) <br /> , @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END) <br />, @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END)<br />, @d = MAX(CASE WHEN colorder=4 THEN sc.name ELSE '' END)<br />FROM<br />syscolumns sc<br />INNER JOIN <br />sysobjects so<br />ON <br />so.id = sc.id<br />WHERE<br />so.type = 'u'<br />AND <br />so.name = 'authors'<br />GROUP BY so.name<br />SELECT @a AS FirstColumn, @b AS SecondColumn, @c AS ThirdColumn, @d AS ForthColumn<br />DECLARE @stmt NVARCHAR(4000)<br />SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c + ',' + @d + ' FROM authors'<br />EXEC sp_ExecuteSQL @stmt<br /></font id="code"></pre id="code"><br />But frankly, I think it's a kludge at best. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  7. FrankKalis Moderator

  8. Madhivanan Moderator

    Try this also


    Declare @sql varchar(2000)
    Declare @col int
    set @col=2
    set @sql=''
    select @sql=@sql+','+column_name from information_schema.columns
    where table_name='yourTable' and Ordinal_position=@col
    select @sql='Select '+substring(@sql,2,len(@sql))+' from yourTable'
    select @sql
    Exec(@sql)



    Madhivanan

    Failing to plan is Planning to fail
  9. FrankKalis Moderator

  10. Madhivanan Moderator

    Thanks Frank. Thats Correct
    Is my approach correct?


    Madhivanan

    Failing to plan is Planning to fail
  11. FrankKalis Moderator

    I hadn't thought about using aggregate concatenation for this. Looks like there's more than one way to skin that cat. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  12. Madhivanan Moderator

    But I think some times it looks easier [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  13. ranjitjain New Member

    Thanx guys,
    But will this work on temp tables too.
    Ive tested on fixed tables and i got what i wanted but trying on temporary table
  14. FrankKalis Moderator

    Sure, just replace


    where table_name=OBJECT_NAME(OBJECT_ID('##yourtable')) and Ordinal_position<=@col

    in Madhivanan's solution or


    AND so.name = OBJECT_NAME(OBJECT_ID('##yourtable'))

    and it should work.


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  15. Madhivanan Moderator

    Frank, as temp tables are stored like

    #tt_________________________________________________________________________________________________________________000000000019

    I think the above wont work

    So we need to query on tempdb sysobjects



    Use tempDb
    Select column_name from information_Schema.columns where
    table_name=OBJECT_NAME((Select top 1 id from sysobjects where name like '%#temptable%'))



    Madhivanan

    Failing to plan is Planning to fail
  16. FrankKalis Moderator

    Have you tried it? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />The fact that a temp table #t under the covers has such a cryptic name as you've mentioned is exactly the reason why I used OBJECT_NAME(OBJECT_ID))<br /><pre id="code"><font face="courier" size="2" id="code"><br />DROP TABLE #t<br />CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5), c4 VARCHAR(5))<br />INSERT INTO #t SELECT 'a', 'b', 'c', 'd'<br />DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5), @d VARCHAR(5)<br />SELECT <br />@a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END) <br /> , @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END) <br />, @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END)<br />, @d = MAX(CASE WHEN colorder=4 THEN sc.name ELSE '' END)<br />FROM<br />syscolumns sc<br />INNER JOIN <br />sysobjects so<br />ON <br />so.id = sc.id<br />WHERE<br />so.type = 'u'<br />AND <br /> so.name = OBJECT_NAME(OBJECT_ID('#t'))<br />GROUP BY so.name<br />SELECT @a AS FirstColumn, @b AS SecondColumn, @c AS ThirdColumn, @d AS ForthColumn<br />DECLARE @stmt NVARCHAR(4000)<br />SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c + ',' + @d + ' FROM #t'<br />EXEC sp_ExecuteSQL @stmt<br /><br />FirstColumn SecondColumn ThirdColumn ForthColumn <br />----------- ------------ ----------- ----------- <br />c1 c2 c3 c4<br /><br />(1 row(s) affected)<br /><br />c1 c2 c3 c4 <br />----- ----- ----- ----- <br />a b c d<br /><br />(1 row(s) affected)<br /></font id="code"></pre id="code"><br /><br /><br />Btw, a SELECT * might also work. [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  17. Madhivanan Moderator

    Well

    When I use this I dont get any names

    Select column_name from information_Schema.columns where
    table_name=OBJECT_NAME(object_Id('#t'))



    Madhivanan

    Failing to plan is Planning to fail
  18. Adriaan New Member

    Try OBJECT_NAME(object_Id(DB_NAME() + '..#t'))?
  19. FrankKalis Moderator

    It is working for me.


    DROP TABLE #t
    CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5), c4 VARCHAR(5))
    INSERT INTO #t SELECT 'a', 'b', 'c', 'd'
    Declare @sql varchar(2000)
    Declare @col int
    set @col=4
    set @sql=''
    select @sql=@sql+','+column_name from information_schema.columns
    where table_name=OBJECT_NAME(OBJECT_ID('#t')) and Ordinal_position<=@col
    select @sql='Select '+substring(@sql,2,len(@sql))+' from #t'
    select @sql
    Exec(@sql)

    ----------------------------
    Select c1,c2,c3,c4 from #t

    (1 row(s) affected)

    c1 c2 c3 c4
    ----- ----- ----- -----
    a b c d


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  20. FrankKalis Moderator

  21. FrankKalis Moderator

    Okay, here's the revamped version of my statement


    USE PUBS
    SET NOCOUNT ON
    DROP TABLE #t
    CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5), c4 VARCHAR(5))
    INSERT INTO #t SELECT 'a', 'b', 'c', 'd'
    DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5), @d VARCHAR(5)
    SELECT
    @a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END)
    , @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END)
    , @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END)
    , @d = MAX(CASE WHEN colorder=4 THEN sc.name ELSE '' END)
    FROM
    tempdb..syscolumns sc
    INNER JOIN
    tempdb..sysobjects so
    ON
    so.id = sc.id
    WHERE
    so.type = 'u'
    AND
    so.id = OBJECT_ID('tempdb..#t')
    GROUP BY so.name
    SELECT @a AS FirstColumn, @b AS SecondColumn, @c AS ThirdColumn, @d AS ForthColumn
    SET NOCOUNT OFF
    DECLARE @stmt NVARCHAR(4000)
    SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c + ',' + @d + ' FROM #t'
    EXEC sp_ExecuteSQL @stmt

    FirstColumn SecondColumn ThirdColumn ForthColumn
    ----------- ------------ ----------- -----------
    c1 c2 c3 c4

    c1 c2 c3 c4
    ----- ----- ----- -----
    a b c d

    (1 row(s) affected)


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  22. Madhivanan Moderator

    Is there any setting I have to enable?
    Still I dont get any names

    After
    select @sql='Select '+substring(@sql,2,len(@sql))+' from #t'

    @sql has "Select from #t"


    Madhivanan

    Failing to plan is Planning to fail
  23. FrankKalis Moderator

  24. FrankKalis Moderator

    ...but the INFORMATION_SCHEMA views always refer to the current database. That's why your solution won't work unless your in tempdb.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  25. Madhivanan Moderator

    quote:Originally posted by FrankKalis

    ...but the INFORMATION_SCHEMA views always refer to the current database. That's why your solution won't work unless your in tempdb.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)


    Thats correct
    Thats why I used Use tempdb in my query

    Why is it not possible to write tempdb..Information_schema.columns


    Madhivanan

    Failing to plan is Planning to fail
  26. FrankKalis Moderator

  27. Madhivanan Moderator

    I hope there is definitely a way to do this [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  28. RGKN New Member

    I think it is possible you just need to miss one of the dots out as the table "owner" is Information_schema


    Try


    select * from tempdb.information_schema.columns

    create table #rn
    ([tid] int identity(1,1),
    [ttext] varchar(10))

    select * from tempdb.information_schema.columns
  29. Madhivanan Moderator

    >>select * from tempdb.information_schema.columns

    Well. Thats correct

    But this does not return anything

    select * from tempdb.information_schema.columns
    where table_name like '%'+object_name(object_id('tempdb..#rn'))+'%'



    Madhivanan

    Failing to plan is Planning to fail
  30. Adriaan New Member

    You need to get the column names from tempdb.dbo.syscolumns using the object id for the temporary table.

    You can't use ...
    OBJECT_NAME(tempdb.dbo.syscolumns.id) LIKE '#T%'
    ... because it will give you a NULL for temporary tables.

    And you can't refer to tempdb.dbo.sysobjects, because there can be multiple temporary tables with the same base name but not necessarily the same column lists.

    Luckily the id value in tempdb.dbo.syscolumns is the same as the OBJECT_ID() for the temporary table in your current database, so we can use this query:


    USE Northwind
    GO

    CREATE TABLE #T (i INT, v VARCHAR(100))

    SELECT S.[name] FROM tempdb.dbo.syscolumns S
    WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')

    DROP TABLE #T
  31. FrankKalis Moderator

    And then we arrive at a solution which doesn't use the INFORMATION_SCHEMA views anymore, but directly queries the system tables. I think Madhivanan was looking for such a way.<br /><br />Btw, in all the excitement, we should ask Ranjit whether his question had been answered or not. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  32. Madhivanan Moderator

    Well Adriaan<br /><br />For temporary tables, it is better to use tempdb systables than views<br /><br />&gt;&gt;Btw, in all the excitement, we should ask Ranjit whether his question had been answered or not. <br /><br />I think he will be more clearer [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  33. Adriaan New Member

    If they want to force us to use the INFORMATION_SCHEMA views, then they should add some that we can actually use.

    Like permission listings per object per login/db_role, organized in columns. The current ones are sequential lists that you have to step through before you have any useful information.
  34. Madhivanan Moderator

    Ranjith, Did you get the solution?


    Madhivanan

    Failing to plan is Planning to fail
  35. ranjitjain New Member

    Hi Madhivanan,
    I have parked the log temporarily because of other bugs so i could not test it,
    but i read all the posts for which i'm very thankful.
    Ill implement each one by one and will keep you updated.
    Thanx alot.
  36. FrankKalis Moderator

    Please do so. I would be interested in your final solution here. [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  37. ranjitjain New Member

    <blockquote id="quote"><font size="1" face="Verdana, Arial, Helvetica" id="quote">quote:<hr height="1" noshade id="quote"><i>Originally posted by Adriaan</i><br /><br />You need to get the column names from tempdb.dbo.syscolumns using the object id for the temporary table.<br /><br />You can't use ...<br /><b>OBJECT_NAME(tempdb.dbo.syscolumns.id) LIKE '#T%'</b><br />... because it will give you a NULL for temporary tables.<br /><br />And you can't refer to tempdb.dbo.sysobjects, because there can be multiple temporary tables with the same base name but not necessarily the same column lists.<br /><br />Luckily the id value in tempdb.dbo.syscolumns is the same as the OBJECT_ID() for the temporary table in your current database, so we can use this query:<br /><br /><pre id="code"><font face="courier" size="2" id="code">USE Northwind<br />GO<br />CREATE TABLE #T (i INT, v VARCHAR(100))<br />SELECT S.[name] FROM tempdb.dbo.syscolumns S<br />WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')<br />DROP TABLE #T</font id="code"></pre id="code"><br /><hr height="1" noshade id="quote"></font id="quote"></blockquote id="quote"><br /><br />Hi Adriann,<br />I'm trying the above code to get column names with order by colid clause.<br />But then it returns resultset with 4 rows so ill have to use cursor which i want to avoid.<br />How to get all of them in a row???????[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]
  38. Madhivanan Moderator

    No. It will work
    Can you post the exact query you used?


    Madhivanan

    Failing to plan is Planning to fail
  39. FrankKalis Moderator

    Have you read the answers carefully? [<img src='/community/emoticons/emotion-5.gif' alt=';)' />]<br />My suggestion returns one row querying the system tables, Madhivanan suggested aggregate concatenation. Combine this altogether and you arrive at something like this:<br /><pre id="code"><font face="courier" size="2" id="code"><br />USE Northwind<br />GO<br />DECLARE @list VARCHAR(100)<br />CREATE TABLE #T (i INT, v VARCHAR(100))<br />INSERT INTO #T SELECT 1,'a'<br />SELECT @list = ISNULL(@list + ',','') +S.[name] FROM tempdb.dbo.syscolumns S <br />WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T') ORDER BY S.[colid] DESC<br />SELECT @list<br />EXEC ('SELECT '+ @list + ' FROM tempdb.dbo.#T')<br />DROP TABLE #T<br /></font id="code"></pre id="code"><br /><br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  40. Adriaan New Member

    The trick is that you can use SELECT for concatenating values into a variable, like this:
    DECLARE @Cols VARCHAR(8000)

    SET @Cols = ''

    SELECT @Cols = @Cols + CASE WHEN LEN(@Cols) > 0 THEN ', ' ELSE '' END + S.name
    FROM tempdb.dbo.syscolumns S
    WHERE S.id = OBJECT_ID('tempdb.dbo.#T')
    ORDER BY S.colid
  41. FrankKalis Moderator

    [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  42. ranjitjain New Member

    Hi,
    i fired this

    DECLARE @Cols VARCHAR(8000)
    CREATE TABLE #T (i INT, v VARCHAR(100))
    SELECT S.[name] FROM tempdb.dbo.syscolumns S
    WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')

    SET @Cols = ''
    SELECT @Cols = @Cols + CASE WHEN LEN(@Cols) > 0 THEN ', ' ELSE '' END + S.[name]
    FROM tempdb.dbo.syscolumns S
    WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')
    ORDER BY S.[colid]
    select @cols
    drop table #t

    and the output is
    name ----------------------------
    i
    v

    --------------------------------------------------------
    i, v

    but i'm looking for something like this

    col1 col2
    i v

    so instead of two rows one row with two cols....
    else if i get i,v ill have to again split it.
    As I want to show the result after that by the alias name so i need them in a row with n cols.
  43. Adriaan New Member

    The reason why you're seeing two rowsets in the output is that your script now has two SELECTs returning a rowset (in bold):

    DECLARE @Cols VARCHAR(8000)
    CREATE TABLE #T (i INT, v VARCHAR(100))
    SELECT S.[name] FROM tempdb.dbo.syscolumns S
    WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')


    SET @Cols = ''
    SELECT @Cols = @Cols + CASE WHEN LEN(@Cols) > 0 THEN ', ' ELSE '' END + S.[name]
    FROM tempdb.dbo.syscolumns S
    WHERE S.[id] = OBJECT_ID('tempdb.dbo.#T')
    ORDER BY S.[colid]
    select @cols
    drop table #t

    Now you want to do cross-tab query on that - why?

    Also, that's the point where a cursor is your only option, at least as long as (1) you're not using a client app that does the cross-tab for you, like Access, and (2) you're not using SQL Server 2005.
  44. FrankKalis Moderator

    You've missed the fact that you've need to execute this dynamically after you've retrieved the list of columns.

    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  45. Madhivanan Moderator

  46. Adriaan New Member

    Madhivanan, thanks for dragging that out from the mists of time!
  47. Madhivanan Moderator

    No Problem Adriaan [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
  48. ranjitjain New Member

    Hi Adriaan,<br />I know the fact why its returning two resultset.<br />But the thing is when it returns all the columns names of that temp table in one column with 2-3 rows i need to use cursor which i want to avoid.<br />So i thought it would be better if somehow i can get all column names in a row so that ill store them directly in 2-3 variables.<br />So that the things for me will be easier and i can do something like . . .<br />set @cmd=@col1val+' as cal_date,'+@col2val+' as isdata'+@col3val +' as iswe'.<br />and finally ill fire select query with the above @cmd appended on that temp table..<br />I hope this idea is good for dealing to get the result of temp table.[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />
  49. ranjitjain New Member

    Hi Madhivanan,
    I refered the link provided by you, and i got what i was expecting...
    I think it will solve my problem somewhat.....
  50. ranjitjain New Member

    Hi Guys,
    I thought i got the solution by firing this...

    CREATE TABLE #T (ispace INT, vautoid VARCHAR(100),z_123 int)
    DECLARE @Test VARCHAR(8000)
    declare @col1 varchar(50), @col2 varchar(50), @col3 varchar(50)
    SET @Test = ''
    SELECT @Test = @Test + CASE WHEN LEN(@Test) = 0 THEN '' ELSE ',' END + name
    from tempdb.dbo.syscolumns WHERE id = object_id('tempdb.dbo.#T')
    order by colorder

    select @col1=left(@test,charindex(',',@test)-1)
    select @col1
    select @col2=substring(@test,charindex(',',@test)+1,charindex(',',@test,charindex(',',@test)+1)-charindex(',',@test)-1)
    select @col2
    select @col3=substring(@test,charindex(',',@test,charindex(',',@test)+1)+1,len(@test))
    select @col3
    drop table #t

    But still solution is at par.
    In the above example what modification i need to do if the temporary table name is dynamic, lets say something like...

    declare @temptabname varchar(500)
    select @temptabname= newid()
    select @temptabname='[##'+@temptabname+']'
    and this dynamic table name i want to use in above eg.?????
  51. Madhivanan Moderator

    This way you can do

    Declare @tableName varchar(20)
    Set @tableName='table'
    Exec('Select Columns from '+@tableName)


    Madhivanan

    Failing to plan is Planning to fail
  52. ranjitjain New Member

    Hi madhivanan,
    Well thats true...
    But as i mentioned in last msg, I'm creating dynamic named temp table and i'm happy with the above merged code to return columns of temp table where the #t is fixed and how can i replace that with my temp table name.
    What modifications i need to do to add that table name to return column names in one row so that i can store them in variables to do further processing.
  53. ranjitjain New Member

    Hi Frank,
    I tried your solution.....which looks more better


    CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5))
    INSERT INTO #t SELECT 'a', 'b', 'c'
    DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
    SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END) ,
    @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END) ,
    @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END)
    FROMtempdb.dbo.syscolumns sc WHEREsc.[id] = OBJECT_ID('tempdb.dbo.#t')

    SELECT @a , @b , @c
    DECLARE @stmt NVARCHAR(4000)
    SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c +' FROM #t'
    EXEC sp_ExecuteSQL @stmt
    DROP TABLE #t

    Now i just need suggestion on modification i need to do if the temp object name is dynamic.
    lets say something like...

    declare @temptabname varchar(500)
    select @temptabname= newid()
    select @temptabname='[##'+@temptabname+']'

  54. ranjitjain New Member

    Hi Frank i modified the code and tried this but giving error......

    DECLARE @stmt NVARCHAR(4000)
    declare @tabname varchar(5)
    set @tabname='#t'
    CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5))
    INSERT INTO #t SELECT 'a', 'b', 'c'
    DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
    set @stmt='SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name END) ,
    @b = MAX(CASE WHEN colorder=2 THEN sc.name END) ,
    @c = MAX(CASE WHEN colorder=3 THEN sc.name END)
    FROMtempdb.dbo.syscolumns sc WHEREsc.[id] = OBJECT_ID(''tempdb.dbo.'+@tabname+''')'
    select @stmt
    EXEC sp_ExecuteSQL @stmt
    SELECT @a , @b , @c
    set @stmt=''
    SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c +' FROM '+@tabname
    EXEC sp_ExecuteSQL @stmt
    DROP TABLE #t

    error....must declare the variable @a whats wrong in this code for dynamic building.
  55. Madhivanan Moderator

    You need to declare them inside @stmt
    Try this



    DECLARE @stmt NVARCHAR(4000)
    declare @tabname varchar(5)
    set @tabname='#t'
    CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5))
    INSERT INTO #t SELECT 'a', 'b', 'c'
    --DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
    set @stmt='
    DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
    SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name END) ,
    @b = MAX(CASE WHEN colorder=2 THEN sc.name END) ,
    @c = MAX(CASE WHEN colorder=3 THEN sc.name END)
    FROM tempdb.dbo.syscolumns sc WHERE sc.[id] = OBJECT_ID(''tempdb.dbo.'+@tabname+''')
    Select @a, @b, @c from '+@tabname+''
    Exec(@stmt)
    EXEC sp_ExecuteSQL @stmt
    ---SELECT @a , @b , @c
    set @stmt=''
    --SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c +' FROM '+@tabname
    EXEC sp_ExecuteSQL @stmt
    DROP TABLE #t





    Madhivanan

    Failing to plan is Planning to fail
  56. ranjitjain New Member

    Hi madhivanan,
    i tried your code but not getting what i'm expecting.
    It returns the column names and the data from that table.
    >When i comment the insert statement line it doesnt return anything.
    >If i add another insert statement it repeats c1,c2,c3 twice.
    But doen't display the data which is not the case when the temp table name is fixed.
    I think some other modification is required.
  57. FrankKalis Moderator

    Ranjit, you missed the GROUP BY


    SELECT
    @a = MAX(CASE WHEN colorder=1 THEN sc.name ELSE '' END)
    , @b = MAX(CASE WHEN colorder=2 THEN sc.name ELSE '' END)
    , @c = MAX(CASE WHEN colorder=3 THEN sc.name ELSE '' END)
    , @d = MAX(CASE WHEN colorder=4 THEN sc.name ELSE '' END)
    FROM
    tempdb..syscolumns sc
    INNER JOIN
    tempdb..sysobjects so
    ON
    so.id = sc.id
    WHERE
    so.type = 'u'
    AND
    so.id = OBJECT_ID('tempdb..#t')
    GROUP BY so.name


    --
    Frank Kalis
    Microsoft SQL Server MVP
    http://www.insidesql.de
    Ich unterstütze PASS Deutschland e.V. http://www.sqlpass.de)
  58. ranjitjain New Member

    Hi Frank,
    Do i still need group by clause as i modified with pripor suggestions and getting the column names but now my question is how can i change the script to include that dynamic temp table name????

  59. ranjitjain New Member

    I think guys,
    All the things have jumbled up and you all are not getting what i'm expecting.
    Do you all want me to explain what i have upto and now what i want.
    Please mention?????
  60. Madhivanan Moderator

    Yes that would be nice


    Madhivanan

    Failing to plan is Planning to fail
  61. FrankKalis Moderator

    Yes, definitely! [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />--<br />Frank Kalis<br />Microsoft SQL Server MVP<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br />Ich unterstütze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <br />
  62. ghemant Moderator

  63. ranjitjain New Member

    Hi Guys,
    Ill Start all over again.
    I have a dynamic temp table name stored in a variable. The columns of that table is created in dll according to MDX query result returned.
    So i want the column names of that temp objects so that i can show them using alias names which is necessary for presentation.
    For e.g. the temp table can have product_category_327,time_quarter_jan,sales_amount named columns as the column names are also dynamic i can't fire select those coulmnnames from temp table.
    I need those column names so that i can say select @col1name as prodname,@col2name as time_frame from @temptable.

    So to achive this i tried adriaan and frank's code with slight modifications.
    After using adriaan code which i posted what i used i stored the column names in variables with high use of left,substring,charindex functions.

    So i went onto use Frank's code and i saved the result in diffrent variables.
    In this code without using group by i'm getting the proper column names.

    DECLARE @stmt NVARCHAR(4000)
    declare @tabname varchar(5)
    set @tabname='#t'
    CREATE TABLE #t (c1 VARCHAR(5), c2 VARCHAR(5), c3 VARCHAR(5))
    INSERT INTO #t SELECT 'a', 'b', 'c'
    INSERT INTO #t SELECT 'a', 'b', 'c'
    DECLARE @a VARCHAR(5), @b VARCHAR(5), @c VARCHAR(5)
    SELECT @a = MAX(CASE WHEN colorder=1 THEN sc.name END) ,
    @b = MAX(CASE WHEN colorder=2 THEN sc.name END) ,
    @c = MAX(CASE WHEN colorder=3 THEN sc.name END)
    FROM tempdb.dbo.syscolumns sc WHERE sc.[id] = OBJECT_ID('tempdb.dbo.#t')
    SELECT @a , @b , @c
    set @stmt=''
    SET @stmt = 'SELECT ' + @a + ','+ @b + ',' + @c +' FROM '+@tabname
    EXEC sp_ExecuteSQL @stmt
    DROP TABLE #t


    But now it doesn't work when i replace #t name which is fixed with @tabname(dynamic).
    So what modifications i need to do in the following code.
    When i follow madhivanans post to add declare statement of @a,@b inside '' string it doen't return proper result??????
    I hope its clear what i'm upto now and what is my fresh issue.
    Please ask if something is still uncleared.

Share This Page