mssql table and column information

mssql-columns-in-table.sql

SELECT
	  columns.*
	, '' remarks
	, data_type type_name
	, character_maximum_length column_size
	, column_default column_default_value
	, syscolumns.is_identity
	, CASE WHEN primaryKeyConstraints.constraint_type IS NOT NULL THEN 1 ELSE 0 END is_primarykey
	, CASE WHEN foreignKeyConstraints.constraint_type IS NOT NULL THEN 1 ELSE 0 END is_foreignKey
FROM
	information_schema.columns columns
LEFT OUTER
	JOIN sys.columns syscolumns
	ON columns.COLUMN_NAME = syscolumns.name
	AND object_id = object_id('dbo.tablename')
LEFT OUTER
	JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE constraintColumnUsage
	ON columns.TABLE_CATALOG = constraintColumnUsage.TABLE_CATALOG
	AND columns.TABLE_SCHEMA = constraintColumnUsage.TABLE_SCHEMA
	AND columns.TABLE_NAME = constraintColumnUsage.TABLE_NAME
	AND columns.COLUMN_NAME = constraintColumnUsage.COLUMN_NAME
LEFT OUTER
	JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS primaryKeyConstraints
	ON constraintColumnUsage.TABLE_CATALOG = primaryKeyConstraints.TABLE_CATALOG
	AND constraintColumnUsage.TABLE_SCHEMA = primaryKeyConstraints.TABLE_SCHEMA
	AND constraintColumnUsage.TABLE_NAME = primaryKeyConstraints.TABLE_NAME
	AND constraintColumnUsage.CONSTRAINT_NAME = primaryKeyConstraints.CONSTRAINT_NAME
	AND primaryKeyConstraints.CONSTRAINT_TYPE = 'PRIMARY KEY'
LEFT OUTER
	JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS foreignKeyConstraints
	ON constraintColumnUsage.TABLE_CATALOG = foreignKeyConstraints.TABLE_CATALOG
	AND constraintColumnUsage.TABLE_SCHEMA = foreignKeyConstraints.TABLE_SCHEMA
	AND constraintColumnUsage.TABLE_NAME = foreignKeyConstraints.TABLE_NAME
	AND constraintColumnUsage.CONSTRAINT_NAME = foreignKeyConstraints.CONSTRAINT_NAME
	AND foreignKeyConstraints.CONSTRAINT_TYPE = 'FOREIGN KEY'
WHERE
	columns.table_schema = 'dbo'
AND
	columns.table_name = 'tablename'

mssql-tables-in-database.sql

SELECT
	  '' remarks
	, table_name
	, table_type
	, table_schema
	, table_catalog
FROM
	information_schema.tables tables

WHERE table_schema NOT IN ('information_schema','pg_catalog')

ORDER BY
	  table_schema
	, table_name