SQLSERVER.sql

-- ALl Table Names
SELECT
  T.TABLE_NAME AS 'Table'
FROM INFORMATION_SCHEMA.TABLES T
WHERE TABLE_TYPE = 'BASE TABLE'
  AND TABLE_SCHEMA = 'schema'
ORDER BY T.TABLE_NAME ASC;

-- TABLE PROPERTIES
SELECT  *
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
  ON C.TABLE_NAME = T.TABLE_NAME
  AND T.TABLE_SCHEMA = C.TABLE_SCHEMA
WHERE T.TABLE_TYPE = 'BASE TABLE'
  AND T.TABLE_SCHEMA = 'schema'
ORDER BY T.TABLE_NAME;

-- PRIMARY KEYS
SELECT DISTINCT *
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='table'
ORDER BY KU.TABLE_NAME;

-- FOREIGN KEYS
SELECT DISTINCT
    FK.TABLE_NAME,
    CU.COLUMN_NAME,
    PK.TABLE_NAME,
    PT.COLUMN_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
WHERE FK.TABLE_NAME = 'table'
  AND FK.TABLE_SCHEMA = 'schema'
  AND PK.TABLE_SCHEMA = 'schema'
  AND CU.TABLE_SCHEMA = 'schema'