0

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));
jarlh
  • 42,561
  • 8
  • 45
  • 63
jebk
  • 1
  • 2
  • Which dbms are you using? – jarlh Jan 19 '21 at 08:53
  • SQL Server Management Studio 18 – jebk Jan 19 '21 at 09:34
  • Are you locked into table functions? Using CTEs are the way I've done this stuff before. https://stackoverflow.com/questions/49939839/recursive-subquerying-with-sorting - This is how I did it with Oracle. SQL Server is not too different.. https://stackoverflow.com/questions/23336520/getting-depth-first-traversal-insted-of-breadth-first-in-t-sql – JGFMK Jan 19 '21 at 09:46

1 Answers1

0

I think you just want 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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786