SELECT 

--TBL.TABLE_SCHEMA,

TBL.TABLE_TYPE, 

COL.TABLE_NAME,

COL.ORDINAL_POSITION,

COL.COLUMN_NAME,

COL.DATA_TYPE,

COL.IS_NULLABLE,

ISNULL(COL.CHARACTER_MAXIMUM_LENGTH,-1) AS MAXIMUM_LENGTH,

--COL.TABLE_CATALOG,

(CASE KEYUSG.CONSTRAINT_TYPE WHEN 'PRIMARY KEY' THEN 'YES' ELSE 'NO' END) PRIMARY_KEY,

(CASE KEYUSG.CONSTRAINT_TYPE WHEN 'FOREIGN KEY' THEN 'YES' ELSE 'NO' END) FOREIGN_KEY,

FK.FOREIGN_TALBE,

FK.FOREIGN_COLUMN,

KEYUSG.CONSTRAINT_NAME

FROM

INFORMATION_SCHEMA.COLUMNS COL

JOIN

INFORMATION_SCHEMA.TABLES TBL

ON

COL.TABLE_NAME=TBL.TABLE_NAME 



	LEFT JOIN

	(

	SELECT 

	USG.CONSTRAINT_NAME,

	USG.TABLE_NAME,

	USG.COLUMN_NAME,

	CONST.CONSTRAINT_TYPE

	 FROM

	INFORMATION_SCHEMA.KEY_COLUMN_USAGE USG

	JOIN

	INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONST

	ON

	USG.TABLE_NAME=CONST.TABLE_NAME

	AND

	USG.CONSTRAINT_NAME = CONST.CONSTRAINT_NAME

	) 

	AS KEYUSG

ON

COL.TABLE_NAME=KEYUSG.TABLE_NAME

AND

COL.COLUMN_NAME=KEYUSG.COLUMN_NAME



---FOREIGHTKEYS

	LEFT OUTER JOIN

	(

	SELECT 

	USAGE.TABLE_NAME,

	USAGE.COLUMN_NAME,

	UNI_USAGE.TABLE_NAME FOREIGN_TALBE,

	UNI_USAGE.COLUMN_NAME FOREIGN_COLUMN,

	CONST.CONSTRAINT_NAME,

	UNIQUE_CONSTRAINT_NAME

	FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS CONST

	JOIN

	INFORMATION_SCHEMA.KEY_COLUMN_USAGE USAGE

	ON

	USAGE.CONSTRAINT_NAME=CONST.CONSTRAINT_NAME 

	JOIN

	INFORMATION_SCHEMA.KEY_COLUMN_USAGE UNI_USAGE

	ON

	UNI_USAGE.CONSTRAINT_NAME=CONST.UNIQUE_CONSTRAINT_NAME 

	) 

	AS FK

ON

FK.TABLE_NAME=COL.TABLE_NAME 

AND

FK.COLUMN_NAME = COL.COLUMN_NAME

AND 

KEYUSG.CONSTRAINT_NAME=FK.CONSTRAINT_NAME