Binaryworld - Using INFORMATION_SCHEMA views to get schema information (i.e. tables, ... [ SQL Server -> View ]

INFORMATION_SCHEMA is ANSI standard and most of modern RDBMS support it. You can use INFORMATION_SCHEMA views can be used to get various schema information

i.e.

Table information
Column information
Primary Key information etc.

How to get list of tables

Click here to copy the following block

SELECT * FROM INFORMATION_SCHEMA.TABLES

How to get list of only user created tables

The problem in the previous query is it returns some system tables and views. TO get only user tables use the following query

Click here to copy the following block

select * from sqdaudit.information_schema.tables where objectproperty(OBJECT_ID(TABLE_NAME), 'IsMSShipped') = 0

How to get list of columns

Click here to copy the following block

SELECT * FROM INFORMATION_SCHEMA.COLUMNS

How to find identity columns for all tables

Click here to copy the following block

select c.Table_name, c.Column_name
FROM INFORMATION_SCHEMA.COLUMNS c
Where columnproperty(object_id(c.table_name), c.column_name, 'IsIdentity') = 1

How to get list of columns defined as primary key for all tables

Click here to copy the following block

SELECT COL.TABLE_NAME as PKI_TABLENAME, COL.COLUMN_NAME as PKI_COLNAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS as CONST
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE as COL
ON CONST.TABLE_SCHEMA = COL.TABLE_SCHEMA
AND CONST.TABLE_NAME = COL.TABLE_NAME
AND CONST.CONSTRAINT_NAME = COL.CONSTRAINT_NAME
WHERE CONST.CONSTRAINT_TYPE = 'PRIMARY KEY'
AND CONST.CONSTRAINT_TYPE = 'PRIMARY KEY'

How to get list of all columns defined as foreign key and related primary key for all tables

Click here to copy the following block

Use Northwind
go

SELECT
FK_Database= FK.TABLE_CATALOG,
FK_Schema= FK.TABLE_SCHEMA,
  FK_Table = FK.TABLE_NAME,
  FK_Column = CU.COLUMN_NAME,
PK_Database = PK.TABLE_CATALOG,
PK_Schema = PK.TABLE_SCHEMA,
  PK_Table = PK.TABLE_NAME,
  PK_Column = PT.COLUMN_NAME,
  Constraint_Name = C.CONSTRAINT_NAME,
COL.IS_NULLABLE,
COL.DATA_TYPE
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
INNER JOIN INFORMATION_SCHEMA.COLUMNS COL
ON CU.COLUMN_NAME=COL.COLUMN_NAME and COL.TABLE_NAME=FK.TABLE_NAME
--// optional:
--WHERE PK.TABLE_NAME IN ('something')
--WHERE FK.TABLE_NAME IN ('something')
--Where COL.IS_NULLABLE='YES'
ORDER BY
  1,2,3,4