Autoval in SQL Server 2005?!

[Michel Racicot]

I used to do the following in SQL Server 2000 to see if I had at least one
column in a table that have an identity column.

select @AtLeastOneAutoval = count(*) from tempdb.dbo.syscolumns
where id = object_id(@TableName) and Autoval is not null

However, for an unknown reason, the identity columns are not working the
same way in SQL Server 2005 and I got 0 as the result even for tables with
identity!!!

So our application is crashing... in SQL Server 2005 only...

And we need to fix it quickly, we have some clients in SQL 2000 and some in
SQL 2005...

Do someone have a solution?

Thank you

[David Portas]

You just learnt why you should beware of undocumented features. My BOL
says that column is for "Internal Use Only".

Instead do:

SELECT OBJECTPROPERTY(OBJECT_ID(@TableName),'TableHasIdentity');

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

[Aaron Bertrand [SQL Server MVP]]

> However, for an unknown reason, the identity columns are not working the
> same way in SQL Server 2005 and I got 0 as the result even for tables with
> identity!!!

For SQL Server 2005, use the following query:

SELECT
Table_Name = OBJECT_NAME([object_id]),
Column_Name = [name]
FROM
sys.columns
WHERE
is_identity = 1
ORDER BY
Table_Name;

[SQL Menace]

And yet another way by using the sys.identity_columns view

SELECT
SCHEMA_NAME(CAST(OBJECTPROPERTYEX(OBJECT_ID, 'SchemaId') AS INT))
+ '.' + OBJECT_NAME(OBJECT_ID) as FullTableName
FROM sys.identity_columns

Denis the SQL Menace
http://sqlservercode.blogspot.com/