Solved

How to find an identity column on a temporary table

Posted on 2008-06-19
9
2,078 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

911 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

23 Experts available now in Live!

Get 1:1 Help Now