請問sql語法..顯示資料字典中的主鍵該怎麼寫呢?

我用sql server新增一個預存程序,可以顯示資料表各個欄位的的屬性,但不知道顯示主鍵該怎麼寫...有人可以幫幫我嗎?感激不盡

CREATE PROCEDURE ps_UTIL_DataDictionary

@TableName sysname

AS

--取得資料字典

SELECT @TableName AS TableName,

Column_Name,

Character_Maximum_Length AS Length,

Data_Type AS DataType,

CASE WHEN Is_Nullable = 'Yes' then 'NULL'

ELSE 'NOT NULL'

END AS Nullabillity

From Information_Schema.Columns

WHERE Table_Name = @TableName

ORDER BY Ordinal_Position

GO

Update:

非常感謝你的回答^^

但我不知道怎麼加進去耶..

(省略)

SELECT @TableName AS TableName,

CONSTRAINT_TYPE as ct (加)

From Information_Schema.Columns

left outer join Information_Schema.TABLE_CONSTRAINTS (加)

????(少了點什麼)

WHERE Table_Name = @TableName

(省略)

執行後會說[關鍵字WHERE附近與法不正確]

1 Answer

Rating
  • 1 decade ago
    Favorite Answer

    可以顯示資料表各個欄位的的屬性,

    但不知道顯示主鍵該怎麼寫>>

    Information_Schema.TABLE_CONSTRAINTS

    CONSTRAINT_TYPE

    varchar(11)

    Type of constraint:

    CHECK檢查

    UNIQUE唯一鍵

    PRIMARY KEY主鍵

    FOREIGN KEY外鍵

    Name

    Returns

    CHECK_CONSTRAINTS

    Check Constraints

    COLUMN_DOMAIN_USAGE

    Every column that has a user-defined data type.

    COLUMN_PRIVILEGES

    Every column with a privilege granted to or by the current user in the current database.

    COLUMNS

    Lists every column in the system

    CONSTRAINT_COLUMN_USAGE

    Every column that has a constraint defined on it.

    CONSTRAINT_TABLE_USAGE

    Every table that has a constraint defined on it.

    DOMAIN_CONSTRAINTS

    Every user-defined data type with a rule bound to it.

    DOMAINS

    Every user-defined data type.

    KEY_COLUMN_USAGE

    Every column that is constrained as a key

    PARAMETERS

    Every parameter for every user-defined function or stored procedure in the datbase. For functions this returns one row with return value information.

    REFERENTIAL_CONSTRAINTS

    Every foreign constraint in the system.

    ROUTINE_COLUMNS

    Every column returned by table-valued functions.

    ROUTINES

    Every stored procedure and function in the database.

    SCHEMATA

    Every database in the system.

    TABLE_CONSTRAINTS

    Every table constraint.

    TABLE_PRIVILEGES

    Every table privilege granted to or by the current user.

    TABLES

    Every table in the system.

    VIEW_COLUMN_USAGE

    Every column used in a view definition.

    Source(s): SAP / Oracle 顧問 OpenSource ERP Adempiere 北美與中國(含台灣區)技術支持團隊
Still have questions? Get your answers by asking now.