This SQL query obtain end-user table metadata from SQL Server database system tables.

get_tables.sql

-- File Name:	get_tables.sql
-- Description:	Get all schemas and user-defined tables 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]
	,	part.[rows]						AS [Rows]

FROM	sys.tables tbl

	INNER JOIN sys.partitions part 
		ON tbl.[object_id] = part.[object_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];

GO

	INNER JOIN sys.partitions			part ON tbl.[object_id] = part.[object_id]
	INNER JOIN sys.extended_properties  prop ON tbl.[object_id] = prop.[major_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];

GO