How to get all table counts from database in JSON output in TSQL script-CodePudding

I would like to have a TSQL script to output all table counts from a SQL Server database to a JSON file. Getting the table counts is not the problem, but outputting this to JSON is the issue. I can't get it to work.

This is what I have to get the table counts:

USE databasename
GO

SELECT
      QUOTENAME(SCHEMA_NAME(sOBJ.schema_id))   '.'   QUOTENAME(sOBJ.name) AS [TableName]
      , SUM(sPTN.Rows) AS [RowCount]
FROM 
      sys.objects AS sOBJ
      INNER JOIN sys.partitions AS sPTN
            ON sOBJ.object_id = sPTN.object_id
WHERE
      sOBJ.type = 'U'
      AND sOBJ.is_ms_shipped = 0x0
      AND index_id < 2 -- 0:Heap, 1:Clustered
GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]

GO

How can I output the results to JSON?

CodePudding user response：

To convert a simple resultset to Json, add for json auto to the end of the statement eg,

...GROUP BY 
      sOBJ.schema_id
      , sOBJ.name
ORDER BY [TableName]
FOR JSON AUTO

See this example DB<>Fiddle