Where can I query clustered or nonclustered table pk?

[pdbowling]

I was curious if there was a way to query in tsql whether or not a table's key in clustered or not using table_name or object_id or whatever is best?

Here is a table that has a clustered primary key set. I know that there are tables in my database that are Not clustered on the pk and I will need to know this when I perform some future processing.

CREATE TABLE [dbo].[UserRole](
	[RoleId] [uniqueidentifier] NOT NULL,
	[UserId] [uniqueidentifier] NOT NULL,
	[CreateDate] [datetime] NOT NULL,
 CONSTRAINT [PK_dbo.UserRole] PRIMARY KEY CLUSTERED 
(
	[RoleId] ASC,
	[UserId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Thanks, All.
Patrick

 

[fredericofonseca]

those questions you just asked can be easily found by doing some searches on google - so what have you found so far and what questions about what you found do you have?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[gmmastros]

try this:

Select * 
from   sys.indexes 
Where  is_primary_key = 1 
       and type_desc <> 'CLUSTERED'

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[pdbowling]

Thank you, gmmastros. That is perfect.

fredericofonseca
I googled these before posting. Just didn't find anything about clustering.

This gets me my primary keys

SELECT COLUMN_NAME
  FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS tab
  JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE AS col
    ON tab.TABLE_NAME = col.TABLE_NAME
   AND tab.CONSTRAINT_NAME = col.CONSTRAINT_NAME
 WHERE tab.Table_Name = 'MyTableName'
   AND CONSTRAINT_TYPE = 'Primary Key'

This gets me my foreign keys

SELECT fktab.[name] AS CurrentTable, tab.[name] AS ReferencedTable, col.[name] AS ForeignKeyName";
  FROM sys.foreign_key_columns AS fks
  JOIN sys.tables AS fktab
    ON fktab.object_id=fks.parent_object_id
  JOIN sys.tables AS tab
    ON tab.object_id = fks.referenced_object_id
  JOIN sys.columns AS col
    ON col.object_id = tab.object_id
   AND col.column_id = fks.constraint_column_id
 WHERE fks.parent_object_id = (select object_id from sys.tables where name = 'MyTableName')

This gets as much as I could find about columns. Just did not see Clustering in the tables.

SELECT cols.[name] AS ColumnName, typ.[name] AS DataType, cols.max_length, cols.is_nullable, cols.is_identity
  FROM Sys.Columns as cols
  JOIN sys.Types AS typ
    ON cols.user_type_id = typ.user_type_id
  WHERE cols.object_id = (select object_id from sys.tables where name = 'MyTableName')
 ORDER BY column_id

 

[Olaf Doschke]

pdbowling,

remember clustered is a type of index, mainly. So you you don't find it in the meta data about tables or columns, but about indexes.
MSDN is your info base about mssql, t-sql and system views, meta data etc.

sys.indexes:

http://technet.microsoft.com/en-us/library/ms173760(v=sql.105).aspx

So type=1 indexes are clustered indexes, is_primary_key gives the primary key indexes.

sys.index_columns:

http://technet.microsoft.com/en-us/library/ms175105(v=sql.105).aspx

is telling you the columns indexed.

Bye, Olaf.