How to get the name of the column that is an Identity Column

I would like to know the best method of getting the name of the column if it
is an IDENTITY column. I need to remove the IDENTITY property on all tables
.
Thankshi,
Using syscolumns system table allocated in every db.
"CSHARPITPRO" wrote:

> I would like to know the best method of getting the name of the column if
it
> is an IDENTITY column. I need to remove the IDENTITY property on all tabl
es.
> Thanks|||There is no way to just remove the IDENTITY property using T-SQL, unless you
are just going to drop the column altogether.
If you just want a list so you can go deal with this manually:
SELECT TABLE_NAME, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY
(
OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity'
) = 1
ORDER BY TABLE_NAME;
"CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
news:2603575C-72FA-4FB5-A74D-AEA9B32C6EC4@.microsoft.com...
>I would like to know the best method of getting the name of the column if
>it
> is an IDENTITY column. I need to remove the IDENTITY property on all
> tables.
> Thanks|||this will get you a list of all identity columns for user defined tables:
select o.name, col_name(i.object_id, column_id)
from sys.objects o join sys.identity_columns i on o.object_id = i.object_id
where o.type = 'U'
order by o.name|||Thanks Aaron,
This information is very helpful. I am just going to handle it
manually. Thank you very much!
"Aaron Bertrand [SQL Server MVP]" wrote:

> There is no way to just remove the IDENTITY property using T-SQL, unless y
ou
> are just going to drop the column altogether.
> If you just want a list so you can go deal with this manually:
> SELECT TABLE_NAME, COLUMN_NAME
> FROM INFORMATION_SCHEMA.COLUMNS
> WHERE COLUMNPROPERTY
> (
> OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity'
> ) = 1
> ORDER BY TABLE_NAME;
>
>
> "CSHARPITPRO" <CSHARPITPRO@.discussions.microsoft.com> wrote in message
> news:2603575C-72FA-4FB5-A74D-AEA9B32C6EC4@.microsoft.com...
>
>|||> this will get you a list of all identity columns for user defined tables:
> select o.name, col_name(i.object_id, column_id)
> from sys.objects o join sys.identity_columns i on o.object_id =
> i.object_id
> where o.type = 'U'
> order by o.name
Sure, in SQL Server 2005. The user didn't post their target version.
The query I posted works in 2000 as well.|||Thanks Enric
"Enric" wrote:
> hi,
> Using syscolumns system table allocated in every db.
> "CSHARPITPRO" wrote:
>|||Thanks!
"PhxSam" wrote:

> this will get you a list of all identity columns for user defined tables:
> select o.name, col_name(i.object_id, column_id)
> from sys.objects o join sys.identity_columns i on o.object_id = i.object_i
d
> where o.type = 'U'
> order by o.namesql