Home Forums SQL Server 2008 SQL Server 2008 - General How to get schemaname, tablename, identity column, foreign key constraints RE: How to get schemaname, tablename, identity column, foreign key constraints

  • Hello!

    Something like this:

    SELECT

    CTU.TABLE_SCHEMA + '.' + CTU.TABLE_NAME

    ,

    KCU.COLUMN_NAME [COLUMN],

    CTU2.TABLE_SCHEMA + '.' + CTU2.TABLE_NAME [REFERENCED_TABLE],

    KCU2.COLUMN_NAME [REFERENCED_COLUMN],

    CTU.CONSTRAINT_NAME [FK_CONSTRAINT]

    FROM INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU

    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU ON KCU.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME AND KCU.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA

    JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC ON RC.CONSTRAINT_NAME = CTU.CONSTRAINT_NAME AND RC.CONSTRAINT_SCHEMA = CTU.CONSTRAINT_SCHEMA

    JOIN INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE CTU2 ON CTU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND CTU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA

    JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2 ON KCU2.CONSTRAINT_NAME = RC.UNIQUE_CONSTRAINT_NAME AND KCU2.CONSTRAINT_SCHEMA = RC.UNIQUE_CONSTRAINT_SCHEMA

    WHERE CTU.TABLE_NAME = 'tablename'

    AND CTU.CONSTRAINT_NAME LIKE 'FK_%'

    Lacc