Details of Primary Keys, Foreign Keys, Unique Keys and Indexes.

The following script can be use to get the list of all Primary Key, Foreign Key and Unique Key.

SELECT OBJECT_NAME(OBJECT_ID) AS NAMEOFCONSTRAINT
	,SCHEMA_NAME(SCHEMA_ID) AS SCHEMANAME
	,OBJECT_NAME(PARENT_OBJECT_ID) AS TABLENAME
	,TYPE_DESC AS CONSTRAINTTYPE
FROM SYS.OBJECTS
WHERE TYPE_DESC IN (
		'FOREIGN_KEY_CONSTRAINT'
		,'PRIMARY_KEY_CONSTRAINT'
		,'UNIQUE_CONSTRAINT'
		)
--AND SCHEMA_ID=SCHEMA_ID('GUEST') /* Uncomment when in need for specific schema */

The following script can be used to get the list of all Indexes.

SELECT Si.NAME 'Index Name'
	,St.NAME 'Table Name'
	,C.NAME 'Column Name'
	,Si.Type_Desc 'Index Type'
	,Si.Is_Primary_Key 'Is Primary Key'
	,Si.Is_Unique 'Is Unique'
	,si.Is_Unique_Constraint 'Is Unique Constraint'
	,Si.is_disabled 'Is Disabled'
	,si.is_padded 'Is Padded'
FROM sys.indexes SI
INNER JOIN sys.tables ST ON SI.object_id = St.object_id
INNER JOIN sys.index_columns IC ON SI.object_id = ic.object_id
	AND SI.index_id = ic.index_id
INNER JOIN sys.columns C ON IC.column_Id = c.column_Id
	AND c.object_id = st.object_id
-- WHERE SCHEMA_ID = SCHEMA_ID('GUEST') /* Uncomment when in need for specific schema */
ORDER BY St.NAME
	,C.column_id

Thanks
Manish