获取Synapse湖数据库各表的列数与行数

方法1：用Synapse Serverless SQL批量查询 #

针对从Dataverse同步来的外部表，直接写SQL就能批量拿到所有表的列数和行数：

单表查询 #

SELECT
    '目标表名' AS table_name,
    (SELECT COUNT(column_id) FROM sys.columns WHERE object_id = OBJECT_ID('目标表名')) AS column_count,
    (SELECT COUNT(*) FROM 目标表名) AS row_count;

批量查询所有外部表 #

用动态SQL自动遍历所有湖数据库表：

DECLARE @sql NVARCHAR(MAX) = '';

SELECT @sql += 'UNION ALL SELECT ''' + name + ''' AS table_name, ' + 
               '(SELECT COUNT(column_id) FROM sys.columns WHERE object_id = OBJECT_ID(''' + QUOTENAME(name) + ''')) AS column_count, ' +
               '(SELECT COUNT(*) FROM ' + QUOTENAME(name) + ') AS row_count '
FROM sys.external_tables;

SET @sql = STUFF(@sql, 1, 10, '');

EXEC sp_executesql @sql;

注意：大型表用COUNT(*)会较慢，可换成APPROX_COUNT_BIG(*)获取近似行数提升查询速度。

方法2：Synapse Studio可视化查看（临时场景） #

如果只是临时查看个别表的数据：

• 打开Synapse Studio，找到湖数据库里的目标表，右键选「预览数据」
• 预览界面顶部会直接显示总行数，列数可以通过表头或右侧列列表统计

方法3：Delta格式表直接读元数据（如果同步的是Delta表） #

如果同步过来的表是Delta Lake格式，直接查元数据就能快速拿到行数，无需全表扫描：

SELECT
    t.name AS table_name,
    COUNT(c.column_id) AS column_count,
    JSON_VALUE(t.properties, '$.numRows') AS row_count
FROM sys.external_tables t
JOIN sys.columns c ON t.object_id = c.object_id
WHERE t.data_source_id IN (
    SELECT data_source_id FROM sys.external_data_sources WHERE type_desc = 'DELTA'
)
GROUP BY t.name, t.properties;

内容的提问来源于stack exchange，提问作者Dharmesh Rathod