Sql-server – SQL sys.tables formatting

sql serversql server 2014

I can get the databases and the tables like the following:

enter image description here

But what I want as a result is

enter image description here

How can I do that? I want to improve the formatting but I do not know how.

Best Answer

Why does this need to be done in T-SQL? Presentation is not SQL Server's job. Since the client has to loop through those rows to display them anyway, it can keep track of the last table it saw and only print the name on the new row if it's different.

Yes, there are ways to produce this output using SQL Server alone, but I'm reluctant to show them to you because you're liable to use them instead of performing this presentation work where it belongs.

SELECT

[table] = CASE
  WHEN LAG(t.name,1) OVER (ORDER BY t.name, c.name) = t.name THEN ''
  ELSE s.name + N'.' + t.name
END,

[column] = c.name
FROM sys.tables AS t
INNER JOIN sys.schemas AS s
ON t.[schema_id] = s.[schema_id]
INNER JOIN sys.columns AS c
ON t.[object_id] = c.[object_id]
ORDER BY t.name, c.name;