List Primary Keys and Indexes in each table

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

List Primary Keys and Indexes in each table

Author

Topic

joanannlim
Starting Member

2 Posts

Posted - 2005-10-07 : 01:28:53

How to scan through all tables in the database to list down the primary key and indexes, as well as tables that does not contain any keys. Thanks.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-07 : 01:49:04

This forum is to post workable scripts
Hereafter post your questions at Developer Forum

Try this

Select CU.table_name, CU.Column_Name,TC.Constraint_Type,CU.constraint_Name
from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CU inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC on CU.Constraint_Name=TC.Constraint_Name
order by CU.table_name, CU.Column_Name

Also

sp_help 'TableName' will give you more details

Madhivanan

Failing to plan is Planning to fail

joanannlim
Starting Member

2 Posts

Posted - 2005-10-07 : 03:09:42

Thanks so much! Your answer is much appreciated.
I will post my questions at the forum you mentioned next time.Sorry for my wrong posting.

coolerbob
Aged Yak Warrior

841 Posts

Posted - 2007-08-14 : 11:01:33

or this:

SELECT OBJECT_NAME(i.id) AS TableName
, i.name AS IndexName
, CASE INDEXPROPERTY(i.id, i.name, 'IsClustered')
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS IsClustered
, CASE INDEXPROPERTY(i.id, i.name, 'IsUnique')
WHEN 1 THEN 'Yes'
ELSE 'No'
END AS IsUnique
, STATS_DATE(i.id, i.indid) AS LastUpdatedDate
, i.id AS ObjectID
, i.indid AS IndexID
FROM sysindexes AS i
WHERE 1 NOT IN (INDEXPROPERTY(i.id, i.name, 'IsStatistics')
, INDEXPROPERTY(i.id, i.name, 'IsAutoStatistics')
, INDEXPROPERTY(i.id, i.name, 'IsHypothetical'))
AND OBJECTPROPERTY(i.id, 'IsMSShipped') = 0
--to filter out the text/ntext/image columns in sysindexes:
AND INDEXPROPERTY(i.id, i.name, 'IndexDepth') > 0
ORDER BY TableName, IsClustered desc, IndexID

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-08-14 : 11:20:45

moved from script library

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp

ashley.sql
Constraint Violating Yak Guru

299 Posts

Posted - 2007-08-14 : 11:51:11

SELECT

O.NAME AS TableName,
I.NAME AS IndexName,

Case WHEN I.Status=18450 THEN 'Clustered' ELSE 'Non-Clustered' END AS [INDEX TYPE]

FROM SYSOBJECTS O, SYSINDEXES I

WHERE (O.ID = I.ID )
AND (I.Status = 18450 OR I.Status = 2097152)
-- AND o.name = 'TABLE_NAME' ----------- If you want a specific table.

Ashley Rhodes

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources