tipos - ver constraints de una tabla sql server




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

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.


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.


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


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'

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'


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


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

esto funcionará en sql 2005





sql-server