get_columns.sql

-- File Name:	get_columns.sql
-- Description:	Get all schemas, user-defined tables, and columns in the SQL Server database
--				specified in the USE statement.

USE AdventureWorks2019;

GO

SELECT

		SCHEMA_NAME(tbl.[schema_id])	AS [Schema]
	,	tbl.name						AS [Table]
	,	col.name						AS [Column]
	,	prop.value						AS [Description]
	,	tp.name							AS [Data Type]
	,	col.max_length					AS [Max Length]
	,	col.precision					AS [Precision]
	,	col.scale						AS [Scale]
	,	col.is_nullable					AS [Is Nullable]

FROM	sys.columns col

	INNER JOIN sys.partitions part			
		ON		col.object_id		= part.object_id

	INNER JOIN sys.tables tbl				
		ON		col.object_id		= tbl.object_id

	INNER JOIN sys.types tp
		ON		col.user_type_id	= tp.user_type_id

	INNER JOIN sys.extended_properties prop	
		ON		prop.class			= 1 -- OBJECT_OR_COLUMN
			AND	col.object_id		= prop.major_id 
			AND	col.column_id		= prop.minor_id

WHERE part.index_id IN (0, 1) -- 0 for table with a primary key and 1 for tables without a primary key

ORDER BY [Schema], [Table], [Column]
-- ORDER BY [Column], [Schema], [Table]

GO