Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2102
  • Last Modified:

How to find an identity column on a temporary table

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
papalarge
Asked:
papalarge
  • 4
  • 3
1 Solution
 
MikeWalshCommented:
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
 
Mark WillsTopic AdvisorCommented:
@mikewalsh: good posting... must be the initials...
0
 
MikeWalshCommented:
Thanks ;)

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
papalargeAuthor Commented:
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
 
papalargeAuthor Commented:
Rename "#tmp" to "#i" in the previous post... same results, just no dumb mistakes... heh.
0
 
papalargeAuthor Commented:
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
 
MikeWalshCommented:
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
 
papalargeAuthor Commented:
This is fine.  Thanks for your help, Mike.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now