it-source

SQL Server 테이블의 기본 키를 어떻게 나열합니까?

criticalcode 2023. 5. 1. 21:28
반응형

SQL Server 테이블의 기본 키를 어떻게 나열합니까?

간단한 질문입니다. T-SQL로 테이블의 기본 키를 어떻게 나열합니까? 테이블에서 인덱스를 가져오는 방법은 알고 있지만 PK를 가져오는 방법은 기억나지 않습니다.

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 Tab.Constraint_Type = 'PRIMARY KEY'
    AND Col.Table_Name = '<your table name>'

으로 권장되는 연습입니다.sys.*에 대한 INFORMATION_SCHEMASQL Server에서 데이터베이스를 마이그레이션할 계획이 없는 한 사용할 수 있습니다.다음과 같이 사용할 수 있습니다.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>');

이것은 시스템 테이블만 사용하는 솔루션입니다.

데이터베이스의 모든 기본 키가 나열됩니다.각 기본 키에 대한 스키마, 테이블 이름, 열 이름 및 올바른 열 정렬 순서를 반환합니다.

특정 테이블의 기본 키를 가져오려면 및 을 필터링해야 합니다.

IMHO, 이 솔루션은 매우 일반적이며 문자열 리터럴을 사용하지 않으므로 모든 시스템에서 실행됩니다.

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 ;

MS SQL 서버를 사용하면 다음 작업을 수행할 수 있습니다.

-- List all tables primary keys
SELECT * FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'PRIMARY KEY'

특정 테이블을 원하는 경우 table_name 열을 필터링할 수도 있습니다.

저는 INFORMATION_SCHEMA 기법을 좋아하지만, 제가 사용한 또 다른 기법은 다음과 같습니다.

exec sp_pkeys 'table'

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>'

주어진 표에 대한 제약 조건을 결정하는 데 사용합니다.
그런 다음 을 사용하여 각 키가 기본 키인지 확인합니다.

내가 따르는 간단한 기술을 말하는 것입니다.

SP_HELP 'table_name'

이 코드를 쿼리로 실행합니다.기본 키를 알고 싶은 table_name 위치에 테이블 이름을 입력합니다(단일 따옴표도 잊지 마십시오).결과는 첨부된 이미지와 같이 표시됩니다.당신에게 도움이 되길 바랍니다.

여기에 이미지 설명 입력

--공동 관련 쿼리의 예이기도 한 또 다른 수정 버전입니다.

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')

여기에는 모든 제약 조건(기본 키 및 외부 키)과 쿼리 끝에 테이블 이름이 나열되어야 합니다.

/* 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 을 통해 읽어보십시오.

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

여기에는 PK 열이 표시됩니다.

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

프로시저는 다음과 같습니다.sp_help정보를 제공할 것입니다.다음 문을 실행합니다.

execute sp_help table_name

지정된 테이블 이름 및 스키마의 기본 키 열을 쉼표로 구분하여 나열한 경우:

Select distinct SUBSTRING ( stuff(( select distinct ',' + [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'  
                                    order by 1 FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,0,'' ) 
                            ,2,9999)

감사합니다.

저는 그것을 사용하여 모든 테이블에 대한 모든 기본 키를 찾았습니다.

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

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

아래 쿼리는 특정 테이블의 기본 키를 나열합니다.

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

ORM을 직접 수행하거나 지정된 테이블에서 코드를 생성하려는 경우 다음과 같은 형식을 사용할 수 있습니다.

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

결과는 다음과 같습니다.

				<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>

기본 키 및 유형이 필요한 경우 이 쿼리가 유용할 수 있습니다.

SELECT L.TABLE_SCHEMA, L.TABLE_NAME, L.COLUMN_NAME, R.TypeName
FROM(
    SELECT COLUMN_NAME, TABLE_NAME, TABLE_SCHEMA
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
    WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_SCHEMA + '.' + QUOTENAME(CONSTRAINT_NAME)), 'IsPrimaryKey') = 1
)L
LEFT JOIN (
    SELECT
    OBJECT_NAME(c.OBJECT_ID) TableName ,c.name AS ColumnName ,t.name AS TypeName
    FROM sys.columns AS c
    JOIN sys.types AS t ON c.user_type_id=t.user_type_id
)R ON L.COLUMN_NAME = R.ColumnName AND L.TABLE_NAME = R.TableName

시도해 보십시오.

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'

이 버전은 스키마, 테이블 이름 및 순서가 지정된 쉼표로 구분된 기본 키 목록을 표시합니다.Object_Id()는 링크 서버에서 작동하지 않으므로 테이블 이름으로 필터링합니다.

REPLACE(Si1)를 사용하지 않습니다.Column_Name, ', '', ', ', ', 이 경우 테스트 중인 데이터베이스에서 Column_Name에 대한 xml 열기 및 닫기 태그가 표시됩니다.데이터베이스에서 'Column_Name'을 교체해야 하는 이유를 잘 모르니, 아시는 분이 계시다면 코멘트 부탁드립니다.

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;

그리고 조지의 질문을 사용한 것과 같은 패턴입니다.

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;

나는 이것이 유용하다는 것을 알게 되었고, 쉼표로 구분된 열 목록과 함께 테이블 목록을 제공한 다음 기본 키인 쉼표로 구분된 목록도 제공합니다.

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

Sys.Objects Table에는 사용자 정의 스키마 범위의 각 개체에 대한 행이 포함되어 있습니다.

기본 키 또는 기타와 같이 생성된 제약 조건이 개체가 되고 테이블 이름이 parent_object가 됩니다.

시스템 쿼리.개체 및 필수 유형의 개체 ID 수집

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)

아래의 원래 질문에 대한 보다 정확한 간단한 답변을 제안해도 되겠습니까?

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'

주의:

  1. 위의 답변 중 일부에 기본 키 열에 대한 필터가 없습니다!
  2. CTE에서 아래를 사용하여 더 큰 열 목록에 참여하여 소스에서 BIML 생성의 스테이징 테이블 및 SSIS 코드를 제공하는 메타데이터를 제공합니다.

최근에 게시되었을 수도 있지만 이를 통해 누군가가 t-sql 쿼리를 사용하여 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'

다음과 같은 경우 이 쿼리를 사용하여 모든 외부 키 목록을 볼 수 있습니다.

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

제 친구가 이것을 발견했는데, 만약 당신이 특정 스키마에서 모든 테이블의 기본 키를 찾고 있다면 매우 효과적입니다.

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>'

아마도 가장 간단한 해결책일 것입니다 :)

EXEC sp_pkeys YourTable

기본 키 또는 외부 키에 대한 키 제약 조건을 기반으로 키의 데이터 유형을 나열하는 시도입니다.

SELECT 
    ksu.table_name as TableName, 
    ksu.column_name as ColumnName, 
    tc.constraint_type as ConstraintType, 
    c.Data_Type as DataType, 
    ksu.ordinal_position as OrdinalPosition
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE ksu
    ON tc.Table_Name = ksu.Table_Name 
    and tc.Constraint_Name = ksu.Constraint_Name
JOIN INFORMATION_SCHEMA.COLUMNS c
    ON c.Table_Name = ksu.Table_Name 
    and c.Column_Name = ksu.Column_Name
WHERE tc.Constraint_Type = 'Primary Key' 
    --or tc.Constraint_Type = 'Foreign Key'
GROUP BY 
    ksu.table_name, ksu.column_name, tc.constraint_type, c.Data_Type, ksu.ordinal_position
ORDER BY ksu.table_name, ksu.column_name, tc.constraint_type, c.Data_Type

Oracle에 필요한 경우 매우 간단합니다.

SELECT `Constraint_Name`
  FROM `All_Constraints`
 WHERE `Constraint_Type` = `'P'`
   AND `Owner` = `'your schema here';`

언급URL : https://stackoverflow.com/questions/95967/how-do-you-list-the-primary-key-of-a-sql-server-table

반응형

'it-source' 카테고리의 다른 글

Postgre에 비해 얼마나 큰가.SQL 테이블?  (0) 2023.05.01
Powershell의 Git 분기에 대한 탭 완료 추가  (0) 2023.05.01
테이블 간에 SQL 데이터 이동  (0) 2023.05.01
Range 개체가 지정된 경우 셀 범위의 각 셀을 반복합니다.  (0) 2023.05.01
디렉토리의 모든 파일 및 폴더를 삭제하는 방법은 무엇입니까?  (0) 2023.05.01

'it-source'의 다른글

  • 현재글SQL Server 테이블의 기본 키를 어떻게 나열합니까?

관련글

댓글

티스토리툴바