Tutorial :Please tell me how to get primary key name in SQL Server 2005



Question:

I need a primary key name and primary key column name of a table please tell me what query should I write..


Solution:1

declare @tableName as nvarchar(100)  set @tableName = 'table'    select i.name, c.name   from sys.index_columns ic       join sys.indexes i on ic.index_id=i.index_id      join sys.columns c on c.column_id=ic.column_id  where       i.[object_id] = object_id(@tableName) and       ic.[object_id] = object_id(@tableName) and       c.[object_id] = object_id(@tableName) and      is_primary_key = 1  


Solution:2

Here's another option that uses the INFORMATION_SCHEMA views

SELECT       cu.Table_Catalog,      cu.Table_Schema,      cu.table_name,      cu.Constraint_name  ,      cu.column_name    FROM       sys.indexes si      inner join  INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE cu      on si.name = cu.constraint_name  WHERE       is_primary_key = 1  


Solution:3

Not quite what you're looking for, but you can play around with it:

SQL 2000: T-SQL to get foreign key relationships for a table


Solution:4

Just a performance note - the select using sys tables is a couple orders of magnitude faster than the select that uses INFORMATION_SCHEMA views


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »