开发者

How to query the column description of a table?

开发者 https://www.devze.com 2023-02-18 11:22 出处:网络
Is there any query to get the list of columns with its primarykey foreign key reference? SELECT column_name, data_type, CHARacter_maximum_length

Is there any query to get the list of columns with its primarykey foreign key reference?

SELECT column_name, data_type, CHARacter_maximum_length
FROM DBTIME.information_schema.columns
WHERE table_name = 'Table Name' 

From this query I am getting the datatype and maximum le开发者_JS百科ngth of a column. Along with this I need to know its foreign key primary key reference. Please help...


SELECT
    K_Table = FK.TABLE_NAME,
    FK_Column = CU.COLUMN_NAME,
    PK_Table = PK.TABLE_NAME,
    PK_Column = PT.COLUMN_NAME,
    Constraint_Name = C.CONSTRAINT_NAME
FROM
    INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS C
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS FK
    ON C.CONSTRAINT_NAME = FK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS PK
    ON C.UNIQUE_CONSTRAINT_NAME = PK.CONSTRAINT_NAME
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE CU
    ON C.CONSTRAINT_NAME = CU.CONSTRAINT_NAME
INNER JOIN (
    SELECT
        i1.TABLE_NAME,
        i2.COLUMN_NAME
    FROM
        INFORMATION_SCHEMA.TABLE_CONSTRAINTS i1
    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE i2
        ON i1.CONSTRAINT_NAME = i2.CONSTRAINT_NAME
    WHERE i1.CONSTRAINT_TYPE = 'PRIMARY KEY'
) PT
    ON PT.TABLE_NAME = PK.TABLE_NAME


SELECT * FROM information_schema.columns WHERE table_name = 'Table Name' 
select * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where TABLE_NAME='Table Name'// It will list all the primary key and foreign key references.
0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号