Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
sanjnep
Posting Yak Master
191 Posts |
 Posted - 2007-06-18 : 16:44:21
|
| I need to find PK that has identity value. I just can find table name and its PK name(select object_name(parent_obj) as [table],name as [PK] from sysobjects where xtype = 'PK'order by [table]). How can I find if PK (with column name) has identity value?Thanks |
|
|
cvraghu
Posting Yak Master
187 Posts |
Posted - 2007-06-18 : 17:23:58
|
| sp_help <tablename> will give you all details about the object, including the primary key and whether its an identity. |
 |
|
|
sanjnep
Posting Yak Master
191 Posts |
Posted - 2007-06-18 : 17:26:53
|
| No I want to list all the tables which have PK and if it is identity columns. |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2007-06-18 : 17:34:12
|
| Here is a starter select * from information_schema.TABLE_CONSTRAINTSPeter LarssonHelsingborg, Sweden |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-06-18 : 22:07:18
|
| It's fairly simple:Select primary key constraints from the INFORMATION_SCHEMA.TABLE_CONSTRAINTS view, join that to the INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE view to get the list of primary key columns, and then use function COLUMNPROPERTY to determine if those columns are IDENTITY columns.CODO ERGO SUM |
|
|
|
|
|
|
How to find if the PK is identity...