Solved

How to find an identity column on a temporary table

Posted on 2008-06-19
9
2,076 Views
Last Modified: 2008-07-08
I have code that will find an identity column on a table, but cannot figure out the method of determining the identity column on a temporary table (in SQL 2000)?

I know there is the colstat field in syscolumns, but I heard that shouldn't be used and will no longer be supported...
ALTER PROCEDURE [dbo].[stU_GetIdentityFieldName]

	@tableName varchar(500),

	@identityFieldName varchar(500) output

AS

BEGIN
 

	set nocount on
 

	create table #identity (name varchar(1000))
 

	--make sure this isn't a temp table

	if @tableName not like '[#]%' begin

		exec('

			declare @name varchar(100)
 

			declare c cursor for 

			select name from syscolumns where ID = object_ID(N''[' + @tableName + ']'')
 

			open c

			fetch next from c into @name

			while @@fetch_status = 0 begin

				if (select columnproperty(object_ID(N''[' + @tableName + ']''), @name, ''IsIdentity'')) = 1 insert #identity select @name

			fetch next from c into @name

			end
 

			close c deallocate c

		')

		select @identityFieldName = name from #identity

	end else begin

		--if we have a temp table, we can't find it

		print('As of now, we know no way of determining a sure-fire way of finding an identity column on a temporary table.')

	end
 

	set nocount off
 

end

Open in new window

0
Comment
Question by:papalarge
  • 4
  • 3
9 Comments
 
LVL 13

Accepted Solution

by:
MikeWalsh earned 125 total points
ID: 21827697
Hi,

Your code confuses me a little bit. It looks like you are purposefully not looking for temporary tables and then saying you can't find them. I presume your code excludes those because it doesn't currently work.

I don't fully understand what your goal is here but to help you out, remember that a temporary table is not created in your user database but it is created in tempdb.

You can see this behavior by a quick experiment like this:

USER yourUserDatabase
CREATE TABLE #temp (nocolumnwillhaveanamelikethis int identity(1,1))

then look for your column:

use your user database first... Select name from syscolumns where name = 'nocolumnwillhaveanamelikethis' you won't find it.

Run that same select in Tempdb.. There it is.

So you will need to search tempdb for your tables that begin with #/##.

I would also look at getting away from querying the system tables and looking at the information_schema.columns to get at the data you need.

HTH - Mike
0
 
LVL 51

Expert Comment

by:Mark Wills
ID: 21831762
@mikewalsh: good posting... must be the initials...
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21831955
Thanks ;)

0
 

Author Comment

by:papalarge
ID: 21832418
Thanks for the post, Mike.  The reason my code excludes finding the identity in temp tables, is because the method doesn't work the same.

Consider the following code:

----------------------------------
--create a dummy temp table with an identity
select identity(int,1,1) uid into #i

--of course this doesn't work since #tmp resides in tempdb
select columnproperty(object_ID(N'#tmp'), 'uid', 'IsIdentity')

--however, this doesn't work either, a NULL is still returned, instead of the "uid" field
select columnproperty(object_ID(N'tempdb..#tmp'), 'uid', 'IsIdentity')
-----------------------------------

I know that I can look in tempdb's syscolumns table for colstat = 1 to find if something is an identity, at least in SQL 2000, and (I think) SQL 2005.  But I read that it's undocumented functionality, and shouldn't be used.  (http://www.megasolutions.net/Sqlserver/How-do-you-script-to-check-Identity-in-all-tables_-65578.aspx)

So what's the preferred method of finding out what column has an identity on it in a temp table?

As an aside... as for selecting from tempdb.information_schema.columns, I can be sure to get my temp table using Object_ID in the syscolumns table.  How can I be sure using a LIKE on the table name of a temp table, since the server is hammered by other users, and could share the table name?  This will reside in a stored procedure that could be run simultaneously multiple times, and I doubt I could pick out the exact object I want using tempdb.information_schema.columns...
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:papalarge
ID: 21832427
Rename "#tmp" to "#i" in the previous post... same results, just no dumb mistakes... heh.
0
 

Author Comment

by:papalarge
ID: 21919052
There weren't any good answers here... I ended up just using my own solution, using a non-standard, and not forward-compatible way of finding the identity column on a temp table.
0
 
LVL 13

Expert Comment

by:MikeWalsh
ID: 21920331
I would say the answer to your question was  to look at the information_schema on tempdb. That explains how to find if a temp table has an identity. That was answering your original question. As to your followup question of how to find the temp table created for your own session, that is probably going to be difficult since as you know the table name has characters appended to it to make sure the name is unique.

I can't think of a way to do that using like since the id at the end of the temp table is randomly generated as far as I know, or there may be a pattern but I don't believe it links evenly to a spid/session_id. I can't think of a reason to need to check this on the fly with users in the system and I guess it wasn't developed that way. If you were checking within a session, you should be able to grab the object_id, however. Since once a temp table is created by an ongoing session that session knows that table as it's #table_name name without the '__________________________000003' appended to it. So if you need to check within the session you could use object_id/object_name and find it that way. Good luck.

As far as refunding - Hard to say, I answered the original question don't care either way really though. I answer because I enjoy helping not for points.
0
 

Author Comment

by:papalarge
ID: 21954925
This is fine.  Thanks for your help, Mike.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
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…

760 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now