Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
multiple connections use the same temp table name, there doesn't seem to be a
way to differentiate between them. Each table name is appended with a bunch
of underscores and a random number (i.e.
"#MyTable__________________________________________________________________________________________________________0000000002B0"),
but that doesn't seem to help much.
Thanks,
Rich WoodThis is a multi-part message in MIME format.
--=_NextPart_000_01D9_01C69935.4E112260
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
It seems odd that you would need to do this, since your code for the =current connection had to know how to create the #Temp Table.
However, if you must, you could use this as a starting point:
SELECT * FROM Tempdb..sysobjects WHERE name LIKE '%#Temp%'
SELECT * FROM Tempdb..syscolumns WHERE id =3D object_ID('tempdb..#Temp')
-- Arnie Rowland, YACE* "To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Rich Wood" <RichWood@.newsgroup.nospam> wrote in message =news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table =in SQL > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but =if > multiple connections use the same temp table name, there doesn't seem =to be a > way to differentiate between them. Each table name is appended with a =bunch > of underscores and a random number (i.e. > ="#MyTable________________________________________________________________=__________________________________________0000000002B0"), > but that doesn't seem to help much.
> > Thanks,
> > Rich Wood
--=_NextPart_000_01D9_01C69935.4E112260
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
It seems odd that you would need to do =this, since your code for the current connection had to know how to create the #Temp =Table.
However, if you must, you could use =this as a starting point:
SELECT *
FROM =Tempdb..sysobjects WHERE name LIKE ='%#Temp%'
SELECT *
FROM Tempdb..syscolumns
WHERE id =3D object_ID('tempdb..#Temp')
-- Arnie Rowland, YACE* "To =be successful, your heart must accompany your knowledge."
*Yet Another Certification =Exam
"Rich Wood"
--=_NextPart_000_01D9_01C69935.4E112260--|||Arnie Rowland wrote:
> It seems odd that you would need to do this, since your code for the curr=ent connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-07=54-45EB-8978-6391D4939CD1@.microsoft.com...
> > Is there a way to get the schema (columns) of a local temporary table i=n SQL
> > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but =if
> > multiple connections use the same temp table name, there doesn't seem t=o be a
> > way to differentiate between them. Each table name is appended with a b=unch
> > of underscores and a random number (i.e.
> > "#MyTable______________________________________________________________=____________________________________________0000000002B0"),
> > but that doesn't seem to help much.
> >
> > Thanks,
> >
> > Rich Wood
> --=3D_NextPart_000_01D9_01C69935.4E112260
> Content-Type: text/html; charset=3DUtf-8
> Content-Transfer-Encoding: quoted-printable
> X-Google-AttachSize: 2562
> =EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
> &
>
>
>
>
>
> It seems odd that you would need to do t=his, since
> your code for the current connection had to know how to create the #Temp
> Table.
>
> However, if you must, you could use this= as a
> starting point:
> SELECT *
> FROM Tempdb..sysobje=cts
>
> WHERE name LIKE '%#Temp%'= face=3D"Courier New" size=3D2>
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id =3D object_ID('tempdb..#Temp')
> -- Arnie Rowland, YACE* "To =be
> successful, your heart must accompany your knowledge."
>
> *Yet Another Certification Exam
>
>
> "Rich Wood" < href=3D"mailto:RichWood@.newsgroup.nospam"> size=3D2>RichWood@.newsgroup.nospam=> wrote in
> message href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com face=3DArial size=3D2>...> Is= there a way to
> get the schema (columns) of a local temporary table in SQL > Serve=r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if > mul=tiple
> connections use the same temp table name, there doesn't seem to be a =>
> way to differentiate between them. Each table name is appended with a bun=ch
> > of underscores and a random number (i.e. >
> "#MyTable________________________________________________________________=__________________________________________0000000002B0"),
> > but that doesn't seem to help much.> > Thanks,=>
> > Rich Wood
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use
sp_help 'tempdb..#temp'
Regards
Amish Shah|||Hi Rich,
Thank you for your posting!
You could use the object_ID function to get the object id of the temporary
table you current user created.
As Arnie mentioned, you could use the following statement to query the
information.
SELECT *
FROM Tempdb..sysobjects
WHERE id = object_ID('tempdb..#TEMP')
SELECT *
FROM Tempdb..syscolumns
WHERE id = object_ID('tempdb..#TEMP')
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||To make a long story short, the local temporary table is created dynamically
with the result of a query -- so even though I create it locally I don't know
the column names at design time.
Thanks for your response -- using the object_id function to query the
tempdb..syscolumns table worked.
Rich Wood
"Arnie Rowland" wrote:
> It seems odd that you would need to do this, since your code for the current connection had to know how to create the #Temp Table.
> However, if you must, you could use this as a starting point:
> SELECT *
> FROM Tempdb..sysobjects
> WHERE name LIKE '%#Temp%'
> SELECT *
> FROM Tempdb..syscolumns
> WHERE id = object_ID('tempdb..#Temp')
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> "Rich Wood" <RichWood@.newsgroup.nospam> wrote in message news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com...
> > Is there a way to get the schema (columns) of a local temporary table in SQL
> > Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> > multiple connections use the same temp table name, there doesn't seem to be a
> > way to differentiate between them. Each table name is appended with a bunch
> > of underscores and a random number (i.e.
> > "#MyTable__________________________________________________________________________________________________________0000000002B0"),
> > but that doesn't seem to help much.
> >
> > Thanks,
> >
> > Rich Wood
No comments:
Post a Comment