首页 文章

SQL Server:使用sql查询获取表主键[重复]

提问于
浏览
107

可能重复:如何列出SQL Server表的主键?

我想使用SQL查询为 SQL Server 数据库获取特定表的主键 .

MySQL 我使用以下查询来获取表主键:

SHOW KEYS FROM tablename WHERE Key_name = 'PRIMARY'

什么相当于 SQL Server 的上述查询?

如果有一个查询适用于 MySQLSQL Server 那么这将是一个理想的情况 .

10 回答

  • 4

    This should list all the constraints and at the end you can put your filters

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

    如需参考,请阅读 - http://blogs.msdn.com/b/sqltips/archive/2005/09/16/469136.aspx

  • 4

    请记住,如果要获得准确的主要字段,则需要将TABLE_NAME和TABLE_SCHEMA置于条件中 .

    此解决方案应该工作:

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

    我还发现另一个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'
    
  • 6
    SELECT COLUMN_NAME FROM {DATABASENAME}.INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE TABLE_NAME LIKE '{TABLENAME}' AND CONSTRAINT_NAME LIKE 'PK%'
    

    WHERE =来自服务器的数据库和 =您要从中查看主键的表名 . 注意:输入您的数据库名称和表名称不带括号 .

  • 136

    使用SQL SERVER 2005,您可以尝试

    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
    

    发现于SQL SERVER – 2005 – Find Tables With Primary Key Constraint in Database

  • 1

    从记忆中,它就是这个

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

    或这个..

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

    我认为其中一个应该可以工作,具体取决于数据的存储方式,但我担心我无法访问SQL来实际验证相同的数据 .

  • 3

    找到另一个:

    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;
    

    我在SQL Server 2003/2005上测试了这个

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

    这将在sql 2005中工作

  • 9

    我给你的代码不仅可以工作,也可以检索SQL Server中的表中的大量数据 . 在SQL Server 2k5 / 2k8中测试,dunno大约2k . 请享用!

    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
    

    您只能使用主键部分,但我认为其余部分可能会变得很方便 . 最好的问候,大卫

  • 56

    它也是(Transact-SQL)......根据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'
    

相关问题