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-6391D4
939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table in S
QL
> Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> multiple connections use the same temp table name, there doesn't seem to b
e a
> way to differentiate between them. Each table name is appended with a bunc
h
> of underscores and a random number (i.e.
> " #MyTable________________________________
_________________________________
________________________________________
_0000000002B0"),
> but that doesn't seem to help much.
>
> Thanks,
>
> Rich WoodIs 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________________________________
___________________________________
_______________________________________0
000000002B0"),
but that doesn't seem to help much.
Thanks,
Rich Wood|||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-6391D4
939CD1@.microsoft.com...
> Is there a way to get the schema (columns) of a local temporary table in S
QL
> Server 2005? TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if
> multiple connections use the same temp table name, there doesn't seem to b
e a
> way to differentiate between them. Each table name is appended with a bunc
h
> of underscores and a random number (i.e.
> " #MyTable________________________________
_________________________________
________________________________________
_0000000002B0"),
> but that doesn't seem to help much.
>
> Thanks,
>
> Rich Wood|||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...
n SQL[vbcol=seagreen]
if[vbcol=seagreen]
o be a[vbcol=seagreen]
unch[vbcol=seagreen]
________________________________________
____0000000002B0"),[vbcol=seagreen]
> --=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">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
> <META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2>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.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>However, if you must, you could use this=
as a
> starting point:</FONT></DIV>
> <DIV><BR><FONT face=3D"Courier New" size=3D2>SELECT * </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>FROM Tempdb..sysobje=
cts
> </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>WHERE name LIKE '%#Temp%'</FONT>=
</DIV><FONT
> face=3D"Courier New" size=3D2>
> <DIV><BR>SELECT * </DIV>
> <DIV>FROM Tempdb..syscolumns </DIV>
> <DIV>WHERE id =3D object_ID('tempdb..#Temp')</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, YACE* <BR>"To =
be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>*Yet Another certification Exam</FONT></=
DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>"Rich Wood" <</FONT><A
> href=3D"mailto:RichWood@.newsgroup.nospam"><FONT face=3DArial
> size=3D2>RichWood@.newsgroup.nospam</FONT></A><FONT face=3DArial size=3D2>=
> wrote in
> message </FONT><A
> href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"><FONT fa=
ce=3DArial
> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com</FONT></=
A><FONT
> face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> Is=
there a way to
> get the schema (columns) of a local temporary table in SQL <BR>> Serve=
r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if <BR>> mul=
tiple
> connections use the same temp table name, there doesn't seem to be a <BR>=
>
> way to differentiate between them. Each table name is appended with a bun=
ch
> <BR>> of underscores and a random number (i.e. <BR>>
> " #MyTable________________________________
________________________________=
________________________________________
__0000000002B0"),
> <BR>> but that doesn't seem to help much.<BR>> <BR>> Thanks,<BR>=
>
> <BR>> Rich Wood</FONT></BODY></HTML>
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use=20
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 kno
w
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:
[vbcol=seagreen]
> It seems odd that you would need to do this, since your code for the curre
nt 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-075
4-45EB-8978-6391D4939CD1@.microsoft.com...|||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...
n SQL[vbcol=seagreen]
if[vbcol=seagreen]
o be a[vbcol=seagreen]
unch[vbcol=seagreen]
________________________________________
____0000000002B0"),[vbcol=seagreen]
> --=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">
> <HTML><HEAD>
> <META http-equiv=3DContent-Type content=3D"text/html; charset=3Dutf-8">
> <META content=3D"MSHTML 6.00.5296.0" name=3DGENERATOR>
> <STYLE></STYLE>
> </HEAD>
> <BODY>
> <DIV><FONT face=3DArial size=3D2>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.</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>However, if you must, you could use this=
as a
> starting point:</FONT></DIV>
> <DIV><BR><FONT face=3D"Courier New" size=3D2>SELECT * </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>FROM Tempdb..sysobje=
cts
> </FONT></DIV>
> <DIV><FONT face=3D"Courier New" size=3D2>WHERE name LIKE '%#Temp%'</FONT>=
</DIV><FONT
> face=3D"Courier New" size=3D2>
> <DIV><BR>SELECT * </DIV>
> <DIV>FROM Tempdb..syscolumns </DIV>
> <DIV>WHERE id =3D object_ID('tempdb..#Temp')</FONT></DIV>
> <DIV><BR><FONT face=3DArial size=3D2>-- <BR>Arnie Rowland, YACE* <BR>"To =
be
> successful, your heart must accompany your knowledge."</FONT></DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>*Yet Another certification Exam</FONT></=
DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2></FONT> </DIV>
> <DIV><FONT face=3DArial size=3D2>"Rich Wood" <</FONT><A
> href=3D"mailto:RichWood@.newsgroup.nospam"><FONT face=3DArial
> size=3D2>RichWood@.newsgroup.nospam</FONT></A><FONT face=3DArial size=3D2>=
> wrote in
> message </FONT><A
> href=3D"news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com"><FONT fa=
ce=3DArial
> size=3D2>news:ECDEB660-0754-45EB-8978-6391D4939CD1@.microsoft.com</FONT></=
A><FONT
> face=3DArial size=3D2>...</FONT></DIV><FONT face=3DArial size=3D2>> Is=
there a way to
> get the schema (columns) of a local temporary table in SQL <BR>> Serve=
r 2005?
> TEMPDBO.INFORMATION_SCHEMA.COLUMNS lists the columns, but if <BR>> mul=
tiple
> connections use the same temp table name, there doesn't seem to be a <BR>=
>
> way to differentiate between them. Each table name is appended with a bun=
ch
> <BR>> of underscores and a random number (i.e. <BR>>
> " #MyTable________________________________
________________________________=
________________________________________
__0000000002B0"),
> <BR>> but that doesn't seem to help much.<BR>> <BR>> Thanks,<BR>=
>
> <BR>> Rich Wood</FONT></BODY></HTML>
> --=3D_NextPart_000_01D9_01C69935.4E112260--
use=20
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 kno
w
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:
[vbcol=seagreen]
> It seems odd that you would need to do this, since your code for the curre
nt 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-075
4-45EB-8978-6391D4939CD1@.microsoft.com...
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment