SQL:递归应用和联合表函数

Question

我使用的是SQL Server Management Studio 18.

我有一个函数，它以表名作为参数，并输出一个表，其中包含有相同列的其他表的信息。每个表有不同数量的列(这些列也在其他表中或不在其他表中)。输出是列名、表名和主题。这个作品。我想将相同的函数应用于第一个表的结果集中的所有表，并将它们彼此合并。

我知道我做错了什么(dbo.TableStructure(firstTable.TableName)))不起作用，因为该函数只针对一个参数而不是多个参数。但我不知道该做些什么来弥补。函数代码:

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)))

应用函数的代码:

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));

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;