查詢同時附帶查主表的第二列

--declare @table nvarchar(50) = '子表';
--declare @size int = 10;
--declare @index int = 3;
--declare @order nvarchar(50) = 'Id';

if not @size > 0 begin
    set @size = 11;
end
if not @index > 0 begin
    set @index = 1;
end

declare @sql nvarchar(max) = 
'select ' + (
select quotename(@table) + '.' + quotename(name) + ', ' from sys.columns where object_name(object_id) = @table order by column_id for xml path('')
)
+
(
select object_name(f.referenced_object_id) + '.' + col_name(f.referenced_object_id, 2) + ' ''' + object_name(f.referenced_object_id) + '''' + ',' from sys.foreign_key_columns f
where f.parent_object_id = object_id(@table)
for xml path(''))
+
' row_number() over(order by ' + quotename(@table) + '.' + quotename(isnull(@order, 'Id')) + ') as rownum from ' + quotename(@table)
+
(
select
' inner join ' + object_name(f.referenced_object_id) + ' on ' + quotename(@table) + '.' + col_name(f.parent_object_id, f.parent_column_id) + ' = ' + object_name(f.referenced_object_id) + '.' + col_name(f.referenced_object_id, f.referenced_column_id)
from sys.foreign_key_columns f
where f.parent_object_id = object_id(@table)
for xml path(''));
set @sql = 'select '
+
(
select quotename(name) + ', ' from sys.columns where object_name(object_id) = @table order by column_id for xml path('')
)
+
(
select object_name(f.referenced_object_id) + ', ' from sys.foreign_key_columns f where f.parent_object_id = object_id(@table) for xml path('')
)
+ 'from (' + @sql + ') s
where rownum between (' + convert(nvarchar(10), (@index - 1) * @size + 1) + ') and (' + convert(nvarchar(10), @index * @size) + ')
';
print @sql;
set @sql = replace(@sql, ', from', ' from');
exec sp_executesql @sql;