sentient beings

If you've ever wanted to get a listing of all the columns in all of the tables in a certain database, here's how. I use this often for writing technical documentation on systems.

This little code will list all of your tables, columns, datatypes, nullable option, identity option and will also indicate primary and foreign keys and default constraints.

 
SELECT
--	distinct
--	quotename(schema_name(tbl.schema_id)) + N'.' + quotename(tbl.name) as TableName,
--	col.column_id,
	col.name AS ColumnName,
	type_name(col.user_type_id)+
	CASE
		WHEN COLUMNPROPERTY(tbl.OBJECT_ID, col.name, 'scale') IS null THEN
			' (' + CONVERT(VARCHAR,COLUMNPROPERTY(tbl.OBJECT_ID, col.name, 'precision')) + ')' --is null --convert(varchar, col.max_length)
		WHEN col.user_type_id in (60, 106, 108, 122) THEN
			' (' + CONVERT(VARCHAR,col.PRECISION) + ',' + CONVERT(VARCHAR, col.scale) + ')'
		ELSE ''
	END	AS DataType,
	CASE col.is_nullable
		WHEN 0 THEN 'NOT '
		ELSE ''
	END
		+	 'NULL' AS Nullable,
	CASE col.is_identity
		WHEN 0 THEN ''
		ELSE 'IDENTITY (' + CONVERT(VARCHAR, idc.seed_value) + ',' + CONVERT(VARCHAR, idc.increment_value) + ')'
	END	AS [IDENTITY],
	CASE
		WHEN ixc.column_id IS not null THEN
			'PK'
		WHEN fkc.constraint_object_id IS not null THEN
			QUOTENAME(schema_name(tb2.schema_id)) + N'.' + QUOTENAME(tb2.name) + N'.' + fcl.name
		ELSE ''
	END AS Key_Constraint,
	CASE
		WHEN dfc.OBJECT_ID IS not null THEN
			dfc.definition
		ELSE ''
	END AS [DEFAULT CONSTRAINT]
FROM
	sys.tables tbl
		INNER join
	(
		SELECT
			QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
			col.name,
			col.column_id,
			col.OBJECT_ID,
			col.is_identity,
			col.is_nullable,
			col.PRECISION,
			col.scale,
			col.user_type_id,
			1 AS SortKey
		FROM
			sys.columns col
				INNER join
			sys.tables tbl
				ON	tbl.OBJECT_ID = col.OBJECT_ID
 
		UNION 
 
		SELECT
			QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
			QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name) AS TableName,
			0,
			tbl.OBJECT_ID,
			0,
			0,
			0,
			0,
			0,
			0
		FROM
			sys.tables tbl
 
	)	col
		ON	tbl.OBJECT_ID = col.OBJECT_ID
		LEFT join
	sys.key_constraints pkc
		ON	pkc.parent_object_id = tbl.OBJECT_ID
		and	pkc.type = 'PK'
		LEFT join
	sys.index_columns ixc
		ON	ixc.OBJECT_ID = tbl.OBJECT_ID
		and	ixc.index_id = pkc.unique_index_id
		and	ixc.column_id = col.column_id
		LEFT join
	sys.foreign_key_columns fkc
		ON	fkc.parent_object_id = tbl.OBJECT_ID
		and	fkc.parent_column_id = col.column_id
		LEFT join
	sys.columns fcl
		ON	fkc.referenced_column_id = fcl.column_id
		and	fkc.referenced_object_id = fcl.OBJECT_ID
		LEFT join
	sys.tables tb2
		ON	fcl.OBJECT_ID = tb2.OBJECT_ID
		LEFT join
	sys.default_constraints dfc
		ON	dfc.parent_object_id = tbl.OBJECT_ID
		and	dfc.parent_column_id = col.column_id
		LEFT join
	sys.identity_columns idc
		ON	idc.OBJECT_ID = tbl.OBJECT_ID
		and	idc.column_id = col.column_id
WHERE
	tbl.type = 'U'
ORDER BY
	QUOTENAME(schema_name(tbl.schema_id)) + N'.' + QUOTENAME(tbl.name),
	SortKey,
	ColumnName