SQL Server - List all table_names, row_count, usage, view_names, view_definitions from database

--Table

SELECT

s.Name AS SchemaName,

t.Name AS TableName,

p.rows AS RowCounts,

CAST(ROUND((SUM(a.used_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Used_MB,

CAST(ROUND((SUM(a.total_pages) - SUM(a.used_pages)) / 128.00, 2) AS NUMERIC(36, 2)) AS Unused_MB,

CAST(ROUND((SUM(a.total_pages) / 128.00), 2) AS NUMERIC(36, 2)) AS Total_MB

FROM sys.tables t

INNER JOIN sys.indexes i ON t.OBJECT_ID = i.object_id

INNER JOIN sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id

INNER JOIN sys.allocation_units a ON p.partition_id = a.container_id

INNER JOIN sys.schemas s ON t.schema_id = s.schema_id

GROUP BY t.Name, s.Name, p.Rows

ORDER BY 3 desc, 4

You can get data like this:

--View

SELECT v.name as VIEW_NAME, v.create_date , Replace(Replace(SM.definition,char(10),''),char(13),'') as view_definition

FROM sys.views V

     INNER JOIN  sys.sql_modules SM ON V.object_id  = SM.object_id 

     INNER JOIN sys.Objects SO ON SM.Object_id = SO.Object_id

WHERE SO.type = 'v'

Databricks SQL - extract data from nested JSON text

 

%sql

select distinct left(v2.inner_key, locate('_', v2.inner_key) - 1 ) as some_alias

from db_name.tbl_name 

     LATERAL VIEW json_tuple(tbl_name.nested_json_col_name, 'outter_key') v1 

     as outer_key

     LATERAL VIEW json_tuple(v1.outer_key, 'inner_key') v2

     as inner_key