Get insights on fields and tables in SQL Server

You may need to get a generic overview of the state of your server:

• what tables have data ?

• what fields do I have in each table?

• etc
  

A way of getting a first insight in SQL Server is to launch this select.

all_columns has all fields of the db, while dm_db_partition_stats gather 'row_count' information of the partition. It needs to be crossed with all_objects to define the type of object.

Image by storyset on Freepik

SELECT 
	ac.object_id
	,ao.name as OBJECT
	,ao.type_desc as TYPEOBJECT
	,ac.name as FIELDNAME
	,ac.column_id
	,ac.system_type_id
	,ac.user_type_id
	,st.name as TYPEFIELDNAME
	,ac.max_length
	,ac.precision
	,ac.scale
	,ddps.row_count as NUMROWSTABLE
FROM MY_DB.sys.all_columns ac
INNER JOIN MY_DB.sys.all_objects ao 
  ON ac.object_id = ao.object_id
INNER JOIN MY_DB.sys.systypes st 
  ON st.xtype = ac.system_type_id
INNER JOIN MY_DB.sys.dm_db_partition_stats ddps 
  ON ddps.object_id = ac.object_id and ddps.object_id = ao.object_id
WHERE ao.type_desc = 'USER_TABLE' AND ddps.row_count > 0
GROUP BY ac.object_id
	,ao.name 
	,ao.type_desc 
	,ac.name
	,ac.column_id
	,ac.system_type_id
	,ac.user_type_id
	,st.name 
	,ac.max_length
	,ac.precision
	,ac.scale
	,ddps.row_count
ORDER BY OBJECT ASC, FIELDNAME ASC ;