sql - SQL: Apply and union table function recursively

Question

I'm using SQL Server Management Studio 18.

I have a function that takes a table name as a parameter and outputs a table with info about other tables that have the same columns in it. Each table has a different amount of columns (that are also in other tables or not). The output is column names, table names and subject. This works. I want to apply the same function to all tables that are in the result set of the first table I applied the function to, and union it with each other.

I know what I am doing wrong (dbo.TableStructure(firstTable.TableName)) doesn't work because the function is made for only 1 parameter and not multiple. But I don't know what to change to make it right. The code of the function:

create function [dbo].[TableStructure](@table nvarchar(50))
returns table as return 
(
select c.name as 'ColumnName', t.name as 'TableName', s.Subject as 'Subject'
from sys.columns c join sys.tables t on c.object_id = t.object_id join dbo.tableSubjects s on t.name=s.name
where t.name <> @table and c.name in (select name from sys.columns where object_id = (select object_id from sys.tables where name = @table)))

The code of applying the function:

declare @table varchar(50) = 'Example';

with firstTable as (select *, 1 as 'Counter' from dbo.TableStructure(@table));
union all
with tmpTable as (select *, 2 as 'Counter' from dbo.TableStructure(firstTable.TableName));

标签： sqlsql-serverrecursiontable-functionsmultiple-input

Answer 1

我想你只是想要cross apply：

with ts as (
      select ts.*, 1 as Counter
      from dbo.TableStructure(@table)
     )
select ts.*
from ts
union all
select ts2.*, 2 as counter
from ts cross apply
     dbo.TableStructure(ts.tablename) ts2;