July 21, 2008 at 11:43 pm
Dear all,
I want the list of columns of Local Temporary Table (#MyTable) in one of my stored procedure.
I am sure, you'll think that if I created the table in the same stored procedure then I must know the name of columns.
But I am writing a query in which I need to use the columns name dynamically.
So how can I get the list of #MyTable columns?
July 22, 2008 at 12:10 am
Temporary tables get created in tempdb.
Try to access tempdb.dbo.syscolumns and tempdb.dbo.sysobjects
July 22, 2008 at 12:45 am
Hi Rosh,
Thanks for reply.
I know that temp tables get created into system DB (tempdb).
Can you write a query for that?
Suppose my stored procedure looks like this:
CREATE PROC TestProc
AS
BEGIN
CREATE TABLE #T (ID int, FirstName varchar(30), LastName varchar(30), DOB DATETIME)
INSERT INTO #T SELECT ID,FirstName,LastName,DOB FROM EMP
-- SELECT ColumnsList Query ??
--- here I need to apply my business logic according ti column name
END
July 22, 2008 at 1:47 am
Hello Hari...
Hari.Sharma (7/22/2008)
Hi Rosh,Thanks for reply.
I know that temp tables get created into system DB (tempdb).
Can you write a query for that?
Suppose my stored procedure looks like this:
CREATE PROC TestProc
AS
BEGIN
CREATE TABLE #T (ID int, FirstName varchar(30), LastName varchar(30), DOB DATETIME)
INSERT INTO #T SELECT ID,FirstName,LastName,DOB FROM EMP
-- SELECT ColumnsList Query ??
--- here I need to apply my business logic according ti column name
END
U can easily get all the column names..
refer the below query...
SELECT * FROM tempdb.sys.columns
WHERE object_id = (SELECT object_id FROM tempdb.sys.columns WHERE NAME = 'FirstName')
--Samarth
July 22, 2008 at 2:09 am
Do you just need the column list from the table #T
If so then see if this query helps you.
SELECT * FROM tempdb.sys.columns
WHERE object_id = (SELECT object_id FROM tempdb.sys.objects WHERE NAME like '#T%' AND Type = 'U')
July 22, 2008 at 2:49 am
rosh (7/22/2008)
Do you just need the column list from the table #TIf so then see if this query helps you.
SELECT * FROM tempdb.sys.columns
WHERE object_id = (SELECT object_id FROM tempdb.sys.objects WHERE NAME like '#T%' AND Type = 'U')
We should not adopt this kind of techniques in real projects.
Try this and see the result:
CREATE TABLE #T (ID int, FirstName varchar(30), LastName varchar(30), DOB DATETIME)
CREATE TABLE #T1 (ID int, FirstName varchar(30), LastName varchar(30))
SELECT * FROM tempdb.sys.columns
WHERE object_id = (SELECT object_id FROM tempdb.sys.columns WHERE NAME = 'FirstName')
July 22, 2008 at 2:54 am
thegreatsamarth (7/22/2008)
Hello Hari...U can easily get all the column names..
refer the below query...
SELECT * FROM tempdb.sys.columns
WHERE object_id = (SELECT object_id FROM tempdb.sys.columns WHERE NAME = 'FirstName')
--Samarth
Samarth,
see the result now:
CREATE TABLE #T (ID int, FirstName varchar(30), LastName varchar(30), DOB DATETIME)
CREATE TABLE #T1 (ID int, FirstName varchar(30), LastName varchar(30))
SELECT * FROM tempdb.sys.columns
WHERE object_id = (SELECT object_id FROM tempdb.sys.columns WHERE NAME = 'FirstName')
July 22, 2008 at 3:11 am
Fair enough.
Probably if you name the temporary table more meaningfully the probability of you running into the above problem would be a rare.
Anyways I may be wrong.
Let me know the solution/technique that real projects use.
July 22, 2008 at 3:33 am
I forgot the basic point-
If you are creating Local temp tables in a stored procedure, the scope for the existence of those temporary tables is only the procedure execution. The temp tables automatically get dropped once the procedure execution is over.
Hope this helps.
July 22, 2008 at 5:21 am
IF OBJECT_ID('tempdb..#tmp1') > 0
DROP TABLE #tmp1
CREATE TABLE #tmp1( ColID INT, ColName VARCHAR(10) )
SELECT * FROM tempdb.sys.Columns WHERE Object_ID = OBJECT_ID('tempdb..#tmp1')
IF OBJECT_ID('tempdb..#tmp1') > 0
DROP TABLE #tmp1
July 22, 2008 at 5:49 am
Maybe I'm dense (ok, I am, but humor me), but if you're the one creating the temporary tables, don't you know the column names already?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
July 22, 2008 at 6:23 am
I'm with Grant on this one. I know you said something is happening dynamically, but at the time of creation, something knows the field names. You are either building a create table statement, or you are doing some kind of select into. Either way, this is the time to keep track of the columns you added to the temp table.
Querying TempDB is going to get pretty ugly. If you do have multiple connections running your procedure, it is going to be tricky to determine which temp table is for the current connection. When your temp table gets created, it gets the name you gave it followed by a bunch of underscores and an integer value. I am not sure you can figure out which one is for the current connection.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply