查询同时附带查主表的第二列
--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;