Solved

How to find an identity column on a temporary table

Posted on 2008-06-19
9
2,082 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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
 

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

770 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