Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

How to find an identity column on a temporary table

Posted on 2008-06-19
9
2,086 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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

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.

Question has a verified solution.

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

Suggested Solutions

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 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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

856 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