tablas - ¿Cómo se incluye la clave principal de una tabla de SQL Server?

modificar clave primaria sql server

Question

Pregunta simple, ¿cómo se enumera la clave principal de una tabla con T-SQL? Sé cómo obtener índices en una tabla, pero no recuerdo cómo obtener el PK.

Answer 1

Sys.Objects Table contiene una fila para cada objeto definido por el usuario y con un alcance de esquema.

Las restricciones creadas como clave principal u otras serán el objeto y el nombre de la tabla será el parent_object

Consultar sys.Objects y recopilar los Id del tipo requerido del Objeto

declare @TableName nvarchar(50)=''TblInvoice'' -- your table name
declare @TypeOfKey nvarchar(50)=''PK'' -- For Primary key
SELECT Name FROM sys.objects
WHERE type = @TypeOfKey
AND parent_object_id = OBJECT_ID (@TableName)

Answer 2

¿Puedo sugerir una respuesta simple más precisa a la pregunta original a continuación?

SELECT
KEYS.table_schema, KEYS.table_name, KEYS.column_name, KEYS.ORDINAL_POSITION
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE keys
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON cons.TABLE_SCHEMA = keys.TABLE_SCHEMA
AND cons.TABLE_NAME = keys.TABLE_NAME
AND cons.CONSTRAINT_NAME = keys.CONSTRAINT_NAME
WHERE cons.CONSTRAINT_TYPE = ''PRIMARY KEY''

Notas:

1. A algunas de las respuestas anteriores les falta un filtro solo para las columnas de clave principal.
2. Estoy usando a continuación en un CTE para unirme a una lista de columnas más grande para proporcionar los metadatos de una fuente para alimentar la generación BIML de tablas de etapas y el código de SSIS

Answer 3

- Esta es otra versión modificada que también es un ejemplo de Co-Related Query

SELECT TC.TABLE_NAME as [Table_name], TC.CONSTRAINT_NAME as [Primary_Key]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
INNER JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE CCU
ON TC.CONSTRAINT_NAME = CCU.CONSTRAINT_NAME
WHERE TC.CONSTRAINT_TYPE = ''PRIMARY KEY'' AND
TC.TABLE_NAME IN
(SELECT [NAME] AS [TABLE_NAME] FROM SYS.OBJECTS
WHERE TYPE = ''U'')

Answer 4

A continuación, la consulta mostrará las claves principales de una tabla en particular :

SELECT DISTINCT
CONSTRAINT_NAME AS [Constraint],
TABLE_SCHEMA AS [Schema],
TABLE_NAME AS TableName
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE
TABLE_NAME = ''mytablename''

Answer 5

Aquí hay otra manera de obtener la clave primaria de la tabla usando la consulta sql :

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA+''.''+CONSTRAINT_NAME), ''IsPrimaryKey'') = 1
AND TABLE_NAME = ''<your table name>''

Utiliza KEY_COLUMN_USAGE para determinar las restricciones para una tabla dada
Luego usa OBJECTPROPERTY( id , ''IsPrimaryKey'') para determinar si cada uno es una clave primaria

Answer 6

El procedimiento almacenado del sistema sp_help le dará la información. Ejecute la siguiente instrucción:

execute sp_help table_name

Answer 7

En general, se recomienda practicar ahora para usar las vistas sys.* Sobre INFORMATION_SCHEMA en SQL Server, así que a menos que planee migrar bases de datos, las usaría. Así es como lo haría con las vistas sys.* :

SELECT
c.name AS column_name,
i.name AS index_name,
c.is_identity
FROM sys.indexes i
inner join sys.index_columns ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
inner join sys.columns c ON ic.object_id = c.object_id AND c.column_id = ic.column_id
WHERE i.is_primary_key = 1
and i.object_ID = OBJECT_ID(''<schema>.<tablename>'');

Answer 8

Encontré esto útil, le doy una lista de tablas con una lista separada por comas de las columnas y luego también una lista separada por comas de cuáles son las claves principales

SELECT T.TABLE_SCHEMA, T.TABLE_NAME,
STUFF((
SELECT '', '' + C.COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS C
WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
FOR XML PATH ('''')
), 1, 2, '''') AS Columns,
STUFF((
SELECT '', '' + C.COLUMN_NAME
FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS TC
ON C.TABLE_SCHEMA = TC.TABLE_SCHEMA
AND C.TABLE_NAME = TC.TABLE_NAME
WHERE C.TABLE_SCHEMA = T.TABLE_SCHEMA
AND T.TABLE_NAME = C.TABLE_NAME
AND TC.CONSTRAINT_TYPE = ''PRIMARY KEY''
FOR XML PATH ('''')
), 1, 2, '''') AS [Key]
FROM INFORMATION_SCHEMA.TABLES T
ORDER BY T.TABLE_SCHEMA, T.TABLE_NAME

Answer 9

Está utilizando MS SQL Server puede hacer lo siguiente:

--List all tables primary keys
select * from information_schema.table_constraints
where constraint_type = ''Primary Key''

También puede filtrar en la columna table_name si desea una tabla específica.

Answer 10

Esta es una solución que usa solo sys -tables.

Enumera todas las claves primarias en la base de datos. Devuelve el esquema, el nombre de la tabla, el nombre de columna y el orden de clasificación de columna correcto para cada clave principal.

Si desea obtener la clave principal para una tabla específica, entonces debe filtrar en SchemaName y TableName .

En mi humilde opinión, esta solución es muy genérica y no utiliza ningún literal de cadena, por lo que se ejecutará en cualquier máquina.

select
s.name as SchemaName,
t.name as TableName,
tc.name as ColumnName,
ic.key_ordinal as KeyOrderNr
from
sys.schemas s
inner join sys.tables t on s.schema_id=t.schema_id
inner join sys.indexes i on t.object_id=i.object_id
inner join sys.index_columns ic on i.object_id=ic.object_id
and i.index_id=ic.index_id
inner join sys.columns tc on ic.object_id=tc.object_id
and ic.column_id=tc.column_id
where i.is_primary_key=1
order by t.name, ic.key_ordinal ;

Answer 11

Esta versión muestra el esquema, el nombre de la tabla y una lista ordenada y separada por comas de claves principales. Object_Id () no funciona para servidores de enlace, por lo que filtramos por el nombre de la tabla.

Sin el REPLACE (Si1.Column_Name, '''', '''') mostraría las etiquetas xml de apertura y cierre de Column_Name en la base de datos en la que estaba probando. No estoy seguro de por qué la base de datos requirió un reemplazo para ''Column_Name'', así que si alguien sabe, por favor comente.

DECLARE @TableName VARCHAR(100) = '''';
WITH Sysinfo
AS (SELECT Kcu.Table_Name
, Kcu.Table_Schema AS Schema_Name
, Kcu.Column_Name
, Kcu.Ordinal_Position
FROM [LinkServer].Information_Schema.Key_Column_Usage Kcu
JOIN [LinkServer].Information_Schema.Table_Constraints AS Tc ON Tc.Constraint_Name = Kcu.Constraint_Name
WHERE Tc.Constraint_Type = ''Primary Key'')
SELECT Schema_Name
,Table_Name
, STUFF(
(
SELECT '', ''
, REPLACE(Si1.Column_Name, '''', '''')
FROM Sysinfo Si1
WHERE Si1.Table_Name = Si2.Table_Name
ORDER BY Si1.Table_Name
, Si1.Ordinal_Position
FOR XML PATH('''')
), 1, 2, '''') AS Primary_Keys
FROM Sysinfo Si2
WHERE Table_Name = CASE
WHEN @TableName NOT IN( '''', ''All'')
THEN @TableName
ELSE Table_Name
END
GROUP BY Si2.Table_Name, Si2.Schema_Name;

Y el mismo patrón usando la consulta de George:

DECLARE @TableName VARCHAR(100) = '''';
WITH Sysinfo
AS (SELECT S.Name AS Schema_Name
, T.Name AS Table_Name
, Tc.Name AS Column_Name
, Ic.Key_Ordinal AS Ordinal_Position
FROM [LinkServer].Sys.Schemas S
JOIN [LinkServer].Sys.Tables T ON S.Schema_Id = T.Schema_Id
JOIN [LinkServer].Sys.Indexes I ON T.Object_Id = I.Object_Id
JOIN [LinkServer].Sys.Index_Columns Ic ON I.Object_Id = Ic.Object_Id
AND I.Index_Id = Ic.Index_Id
JOIN [LinkServer].Sys.Columns Tc ON Ic.Object_Id = Tc.Object_Id
AND Ic.Column_Id = Tc.Column_Id
WHERE I.Is_Primary_Key = 1)
SELECT Schema_Name
,Table_Name
, STUFF(
(
SELECT '', ''
, REPLACE(Si1.Column_Name, '''', '''')
FROM Sysinfo Si1
WHERE Si1.Table_Name = Si2.Table_Name
ORDER BY Si1.Table_Name
, Si1.Ordinal_Position
FOR XML PATH('''')
), 1, 2, '''') AS Primary_Keys
FROM Sysinfo Si2
WHERE Table_Name = CASE
WHEN @TableName NOT IN('''', ''All'')
THEN @TableName
ELSE Table_Name
END
GROUP BY Si2.Table_Name, Si2.Schema_Name;

Answer 12

Este te da las columnas que son PK.

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME = ''TableName''

Answer 13

Estoy diciendo una técnica simple que sigo

SP_HELP ''table_name''

ejecuta este código como consulta. Mencione el nombre de su tabla en el lugar de table_name para el que desea conocer la clave principal (no olvide las comillas simples). El resultado se mostrará como Imagen adjunta. Espero que te ayude

Answer 14

Gracias amigo.

Con una ligera variación lo usé para encontrar todas las claves principales para todas las tablas.

SELECT A.Name,Col.Column_Name from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col ,
(select NAME from dbo.sysobjects where xtype=''u'') AS A
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = ''PRIMARY KEY ''
AND Col.Table_Name = A.Name

Answer 15

Me gusta la técnica INFORMATION_SCHEMA, pero otra que he usado es: exec sp_pkeys ''table''

Answer 16

Prueba esto:

SELECT
CONSTRAINT_CATALOG AS DataBaseName,
CONSTRAINT_SCHEMA AS SchemaName,
TABLE_NAME AS TableName,
CONSTRAINT_Name AS PrimaryKey
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = ''Primary Key'' and Table_Name = ''YourTable''

Answer 17

Puede que se publique últimamente pero, con suerte, esto ayudará a que alguien vea la lista de claves principales en el servidor SQL mediante esta consulta t-sql:

SELECT schema_name(t.schema_id) AS [schema_name], t.name AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS PrimaryKeyColumnName,
i.name AS PrimaryKeyConstraintName
FROM sys.tables t
INNER JOIN sys.indexes AS i on t.object_id=i.object_id
INNER JOIN sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE OBJECT_NAME(ic.OBJECT_ID) = ''YourTableNameHere''

Puede ver la lista de todas las claves externas usando esta consulta si lo desea:

SELECT
f.name as ForeignKeyConstraintName
,OBJECT_NAME(f.parent_object_id) AS ReferencingTableName
,COL_NAME(fc.parent_object_id, fc.parent_column_id) AS ReferencingColumnName
,OBJECT_NAME (f.referenced_object_id) AS ReferencedTableName
,COL_NAME(fc.referenced_object_id, fc.referenced_column_id) AS
ReferencedColumnName ,delete_referential_action_desc AS
DeleteReferentialActionDesc ,update_referential_action_desc AS
UpdateReferentialActionDesc
FROM sys.foreign_keys AS f
INNER JOIN sys.foreign_key_columns AS fc
ON f.object_id = fc.constraint_object_id
--WHERE OBJECT_NAME(f.parent_object_id) = ''YourTableNameHere''
--If you want to know referecing table details
WHERE OBJECT_NAME(f.referenced_object_id) = ''YourTableNameHere''
--If you want to know refereced table details
ORDER BY f.name

Answer 18

Si está buscando hacer su propio ORM o generar código a partir de una tabla determinada, entonces esto podría ser lo que busca:

declare @table varchar(100) = ''mytable'';
with cte as
(
select
tc.CONSTRAINT_SCHEMA
, tc.CONSTRAINT_TYPE
, tc.TABLE_NAME
, ccu.COLUMN_NAME
, IS_NULLABLE
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
inner join INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu on tc.TABLE_NAME=ccu.TABLE_NAME and tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA
inner join information_schema.COLUMNS c on ccu.COLUMN_NAME=c.COLUMN_NAME and ccu.TABLE_NAME=c.TABLE_NAME and ccu.TABLE_SCHEMA=c.TABLE_SCHEMA
where
tc.table_name=@table
and
ccu.CONSTRAINT_NAME=tc.CONSTRAINT_NAME
union
select TABLE_SCHEMA,''COLUMN'', TABLE_NAME, COLUMN_NAME, IS_NULLABLE, DATA_TYPE,CHARACTER_MAXIMUM_LENGTH, NUMERIC_PRECISION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME=@table
and COLUMN_NAME not in (select COLUMN_NAME from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE where TABLE_NAME = @table)
)
select
cast(iif(CONSTRAINT_TYPE=''PRIMARY KEY'',1,0) as bit) PrimaryKey
,cast(iif(CONSTRAINT_TYPE=''FOREIGN KEY'',1,0) as bit) ForeignKey
,cast(iif(CONSTRAINT_TYPE=''COLUMN'',1,0) as bit) NotKey
,COLUMN_NAME
,cast(iif(is_nullable=''NO'',0,1) as bit) IsNullable
, DATA_TYPE
, CHARACTER_MAXIMUM_LENGTH
, NUMERIC_PRECISION
from
cte
order by
case CONSTRAINT_TYPE
when ''PRIMARY KEY'' then 1
when ''FOREIGN KEY'' then 2
else 3 end
, COLUMN_NAME

Así es como se vería el resultado:

 <table cellspacing=0 border=1>
<tr>
<td style=min-width:50px>PrimaryKey</td>
<td style=min-width:50px>ForeignKey</td>
<td style=min-width:50px>NotKey</td>
<td style=min-width:50px>COLUMN_NAME</td>
<td style=min-width:50px>IsNullable</td>
<td style=min-width:50px>DATA_TYPE</td>
<td style=min-width:50px>CHARACTER_MAXIMUM_LENGTH</td>
<td style=min-width:50px>NUMERIC_PRECISION</td>
</tr>
<tr>
<td style=min-width:50px>1</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>LectureNoteID</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>int</td>
<td style=min-width:50px>NULL</td>
<td style=min-width:50px>10</td>
</tr>
<tr>
<td style=min-width:50px>0</td>
<td style=min-width:50px>1</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>LectureId</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>int</td>
<td style=min-width:50px>NULL</td>
<td style=min-width:50px>10</td>
</tr>
<tr>
<td style=min-width:50px>0</td>
<td style=min-width:50px>1</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>NoteTypeID</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>int</td>
<td style=min-width:50px>NULL</td>
<td style=min-width:50px>10</td>
</tr>
<tr>
<td style=min-width:50px>0</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>1</td>
<td style=min-width:50px>Body</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>nvarchar</td>
<td style=min-width:50px>-1</td>
<td style=min-width:50px>NULL</td>
</tr>
<tr>
<td style=min-width:50px>0</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>1</td>
<td style=min-width:50px>DisplayOrder</td>
<td style=min-width:50px>0</td>
<td style=min-width:50px>int</td>
<td style=min-width:50px>NULL</td>
<td style=min-width:50px>10</td>
</tr>
</table>

Answer 19

Encontré esto por parte de mi amigo, muy efectivo si estás buscando todas las claves principales de la tabla bajo un esquema particular.

SELECT tc.constraint_name AS IndexName,tc.table_name AS TableName,tc.table_schema
AS SchemaName,kc.column_name AS COLUMN_NAME
FROM information_schema.table_constraints tc,information_schema.key_column_usage kc
WHERE tc.constraint_type = ''PRIMARY KEY'' AND kc.table_name = tc.table_name AND kc.table_schema = tc.table_schema
AND kc.constraint_name = tc.constraint_name AND tc.table_schema=''<SCHEMA_NAME>''

Answer 20

Esto debería enumerar todas las restricciones (clave principal y claves externas) y al final del nombre de la tabla de puesta en consulta

/* CAST IS DONE , SO THAT OUTPUT INTEXT FILE REMAINS WITH SCREEN LIMIT*/
WITH ALL_KEYS_IN_TABLE (CONSTRAINT_NAME,CONSTRAINT_TYPE,PARENT_TABLE_NAME,PARENT_COL_NAME,PARENT_COL_NAME_DATA_TYPE,REFERENCE_TABLE_NAME,REFERENCE_COL_NAME)
AS
(
SELECT CONSTRAINT_NAME= CAST (PKnUKEY.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=CAST (PKnUKEY.type_desc AS VARCHAR(30)) ,
PARENT_TABLE_NAME=CAST (PKnUTable.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( PKnUKEYCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME='''' ,
REFERENCE_COL_NAME=''''
FROM sys.key_constraints as PKnUKEY
INNER JOIN sys.tables as PKnUTable
ON PKnUTable.object_id = PKnUKEY.parent_object_id
INNER JOIN sys.index_columns as PKnUColIdx
ON PKnUColIdx.object_id = PKnUTable.object_id
AND PKnUColIdx.index_id = PKnUKEY.unique_index_id
INNER JOIN sys.columns as PKnUKEYCol
ON PKnUKEYCol.object_id = PKnUTable.object_id
AND PKnUKEYCol.column_id = PKnUColIdx.column_id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=PKnUTable.name
AND oParentColDtl.COLUMN_NAME=PKnUKEYCol.name
UNION ALL
SELECT CONSTRAINT_NAME= CAST (oConstraint.name AS VARCHAR(30)) ,
CONSTRAINT_TYPE=''FK'',
PARENT_TABLE_NAME=CAST (oParent.name AS VARCHAR(30)) ,
PARENT_COL_NAME=CAST ( oParentCol.name AS VARCHAR(30)) ,
PARENT_COL_NAME_DATA_TYPE= oParentColDtl.DATA_TYPE,
REFERENCE_TABLE_NAME=CAST ( oReference.name AS VARCHAR(30)) ,
REFERENCE_COL_NAME=CAST (oReferenceCol.name AS VARCHAR(30))
FROM sys.foreign_key_columns FKC
INNER JOIN sys.sysobjects oConstraint
ON FKC.constraint_object_id=oConstraint.id
INNER JOIN sys.sysobjects oParent
ON FKC.parent_object_id=oParent.id
INNER JOIN sys.all_columns oParentCol
ON FKC.parent_object_id=oParentCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.parent_column_id=oParentCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
INNER JOIN sys.sysobjects oReference
ON FKC.referenced_object_id=oReference.id
INNER JOIN INFORMATION_SCHEMA.COLUMNS oParentColDtl
ON oParentColDtl.TABLE_NAME=oParent.name
AND oParentColDtl.COLUMN_NAME=oParentCol.name
INNER JOIN sys.all_columns oReferenceCol
ON FKC.referenced_object_id=oReferenceCol.object_id /* ID of the object to which this column belongs.*/
AND FKC.referenced_column_id=oReferenceCol.column_id/* ID of the column. Is unique within the object.Column IDs might not be sequential.*/
)
select * from ALL_KEYS_IN_TABLE
where
PARENT_TABLE_NAME in (''YOUR_TABLE_NAME'')
or REFERENCE_TABLE_NAME in (''YOUR_TABLE_NAME'')
ORDER BY PARENT_TABLE_NAME,CONSTRAINT_NAME;

Para referencia, lea a través de - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

Answer 21

SELECT A.TABLE_NAME as [Table_name], A.CONSTRAINT_NAME as [Primary_Key]
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS A, INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE B
WHERE CONSTRAINT_TYPE = ''PRIMARY KEY'' AND A.CONSTRAINT_NAME = B.CONSTRAINT_NAME

Answer 22

SELECT Col.Column_Name from
INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab,
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col
WHERE
Col.Constraint_Name = Tab.Constraint_Name
AND Col.Table_Name = Tab.Table_Name
AND Constraint_Type = ''PRIMARY KEY''
AND Col.Table_Name = ''<your table name>''

Answer 23

SELECT t.name AS ''table'', i.name AS ''index'', it.xtype,
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 1
AND k.id = t.id)
AS ''column1'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 2
AND k.id = t.id)
AS ''column2'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 3
AND k.id = t.id)
AS ''column3'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 4
AND k.id = t.id)
AS ''column4'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 5
AND k.id = t.id)
AS ''column5'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 6
AND k.id = t.id)
AS ''column6'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 7
AND k.id = t.id)
AS ''column7'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 8
AND k.id = t.id)
AS ''column8'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 9
AND k.id = t.id)
AS ''column9'',
(SELECT c.name FROM syscolumns c INNER JOIN sysindexkeys k
ON k.indid = i.indid
AND c.colid = k.colid
AND c.id = t.id
AND k.keyno = 10
AND k.id = t.id)
AS ''column10'',
FROM sysobjects t
INNER JOIN sysindexes i ON i.id = t.id
INNER JOIN sysobjects it ON it.parent_obj = t.id AND it.name = i.name
WHERE it.xtype = ''PK''
ORDER BY t.name, i.name