June 6, 2011 at 11:52 am
I was trying to get the identity for a table and whether one exists. I tried the following I had found on the net, but it doesn't seem to work:
SELECT table_name, column_name
FROM information_schema.columns
WHERETable_Name = 'Roles'
AND COLUMNPROPERTY(OBJECT_ID(table_name),column_name,'IsIdentity')=1
This returns nothing.
I also found that this one doesn't seem to work in the new DB (2008) but worked fine in 2005.
SELECT @ident_flag = count(1), @Identity = c.name
FROM sysobjects o JOIN syscolumns c on c.id = o.id
WHERE o.NAME = @table_name AND COLUMNPROPERTY (c.id, c.name,
'IsIdentity') = 1
GROUP BY c.name
Why is that and what would get me what I need for both 2005 and 2008?
Thanks,
Tom
June 6, 2011 at 12:04 pm
The first one also works in 2005 and not 2008
Tom
June 6, 2011 at 12:12 pm
I have tested both queries on SQL 2008 and both work perfectly fine for me.
Are you getting error messages or is it just that you get no results?
If you are getting no results, try a different table.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 6, 2011 at 12:26 pm
The first one gives me an empty set.
The second gives me blanks in my fields.
I did find one that works:
SELECT sys.columns.is_identity
FROM sys.columns
JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.is_identity = 1 AND
sys.objects.type IN ( N'U' )
AND sys.objects.name = 'Roles'
But this one will have a problem if you have 2 tables with the same name but different Schemas.
The Roles table actually has a schema name in front of it (inter.Roles). Maybe that is why the first 2 queries are not working in 2008.
The only way to get the one that works is to take out the schema name but if there are 2 table with the same name, you will get 2 rows.
Tom
June 6, 2011 at 12:32 pm
So try this
SELECT sys.columns.is_identity
FROM sys.columns
JOIN sys.objects
ON sys.columns.object_id = sys.objects.object_id
WHERE sys.columns.is_identity = 1 AND
sys.objects.type IN ( N'U' )
AND sys.objects.name = 'Roles'
AND SCHEMA_NAME(schema_id) = 'inter' --added to restrict to specific schemas
It's not that the queries don't work. Your expected results are not what you want to see. That would be helpful when posting questions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 6, 2011 at 1:32 pm
Actually, the first 2 are not working (as they did before) for me in 2008 but do in 2005. Which is why I posted it. And I did mention that they worked in one and not the other.
The 3rd one, as I mentioned, did work (as expected) but I did mention later that perhaps the schema was causing the problem.
When I added the last line you suggested, it works fine.
Thanks,
tom
June 6, 2011 at 1:42 pm
K. I ran both of those queries on SQL 2005, SQL 2008 and 2008 R2 and they work perfectly fine for me.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
June 6, 2011 at 1:54 pm
Well, they aren't working for me but it isn't an issue since the other one does.
Thanks,
Tom
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply