sql - una - tipos de restricciones en base de datos

Servidor SQL: obtenga la clave primaria de la tabla usando la consulta sql

Question

Posible duplicado:
¿Cómo se incluye la clave principal de una tabla de SQL Server?

Quiero obtener la clave primaria de una tabla particular utilizando la consulta SQL para la base de datos de SQL Server .

En MySQL estoy usando la siguiente consulta para obtener la clave primaria de la tabla:

SHOW KEYS FROM tablename WHERE Key_name = ''PRIMARY''

¿Qué es equivalente a la consulta anterior para SQL Server ?

Si hay una consulta que funcionará tanto para MySQL como para SQL Server , será un caso ideal.

Answer 1

De memoria, es esto

SELECT * FROM sys.objects
WHERE type = ''PK''
AND object_id = OBJECT_ID (''tableName'')

o esto..

SELECT * FROM sys.objects
WHERE type = ''PK''
AND parent_object_id = OBJECT_ID (''tableName'')

Creo que uno de ellos probablemente debería funcionar dependiendo de cómo se almacenan los datos, pero me temo que no tengo acceso a SQL para verificar realmente lo mismo.

Answer 2

El código que le daré funciona y no solo recupera claves, sino también una gran cantidad de datos de una tabla en SQL Server. Se prueba en SQL Server 2k5 / 2k8, no sé aproximadamente 2k. ¡Disfrutar!

SELECT DISTINCT
sys.tables.object_id AS TableId,
sys.columns.column_id AS ColumnId,
sys.columns.name AS ColumnName,
sys.types.name AS TypeName,
sys.columns.precision AS NumericPrecision,
sys.columns.scale AS NumericScale,
sys.columns.is_nullable AS IsNullable,
( SELECT
COUNT(column_name)
FROM
INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
WHERE
TABLE_NAME = sys.tables.name AND
CONSTRAINT_NAME =
( SELECT
constraint_name
FROM
INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE
TABLE_NAME = sys.tables.name AND
constraint_type = ''PRIMARY KEY'' AND
COLUMN_NAME = sys.columns.name
)
) AS IsPrimaryKey,
sys.columns.max_length / 2 AS CharMaxLength /*BUG*/
FROM
sys.columns, sys.types, sys.tables
WHERE
sys.tables.object_id = sys.columns.object_id AND
sys.types.system_type_id = sys.columns.system_type_id AND
sys.types.user_type_id = sys.columns.user_type_id AND
sys.tables.name = ''TABLE''
ORDER BY
IsPrimaryKey

Puede usar solo la parte de la clave principal, pero creo que el resto podría ser útil. Un saludo, David

Answer 3

Encontré otro:

SELECT KU.table_name as TABLENAME,column_name as PRIMARYKEYCOLUMN
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
INNER JOIN
INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KU
ON TC.CONSTRAINT_TYPE = ''PRIMARY KEY'' AND
TC.CONSTRAINT_NAME = KU.CONSTRAINT_NAME AND
KU.table_name=''yourTableName''
ORDER BY KU.TABLE_NAME, KU.ORDINAL_POSITION;

He probado esto en SQL Server 2003/2005

Answer 4

También encontré otro para SQL Server:

SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + ''.'' + QUOTENAME(CONSTRAINT_NAME)), ''IsPrimaryKey'') = 1
AND TABLE_NAME = ''TableName'' AND TABLE_SCHEMA = ''Schema''

Answer 5

También es (Transact-SQL) ... de acuerdo con BOL.

-- exec sp_serveroption ''SERVER NAME'', ''data access'', ''true'' --execute once
EXEC sp_primarykeys @table_server = N''server_name'',
@table_name = N''table_name'',
@table_catalog = N''db_name'',
@table_schema = N''schema_name''; --frequently ''dbo''

Answer 6

Tenga en cuenta que si desea obtener el campo primario exacto, debe poner TABLE_NAME y TABLE_SCHEMA en la condición.

esta solución debería funcionar:

select COLUMN_NAME from information_schema.KEY_COLUMN_USAGE
where CONSTRAINT_NAME=''PRIMARY'' AND TABLE_NAME=''TABLENAME''
AND TABLE_SCHEMA=''DATABASENAME''

Answer 7

Usando SQL SERVER 2005, puedes probar

SELECT i.name AS IndexName,
OBJECT_NAME(ic.OBJECT_ID) AS TableName,
COL_NAME(ic.OBJECT_ID,ic.column_id) AS ColumnName
FROM sys.indexes AS i INNER JOIN
sys.index_columns AS ic ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Encontrado en SQL SERVER - 2005 - Buscar tablas con restricción de clave principal en la base de datos

Answer 8

Esto debería enumerar todas las restricciones y al final puedes poner tus filtros

/* 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 9

SELECT COLUMN_NAME FROM {DATABASENAME}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME LIKE ''{TABLENAME}'' AND CONSTRAINT_NAME LIKE ''PK%''

DÓNDE
{DATABASENAME} = su base de datos desde su servidor Y
{TABLENAME} = el nombre de su tabla desde la que desea ver la clave principal.

NOTA: ingrese el nombre de su base de datos y el nombre de la tabla sin corchetes.

Answer 10

select *
from sysobjects
where xtype=''pk'' and
parent_obj in (select id from sysobjects where name=''tablename'')

esto funcionará en sql 2005