Is it possible to detect what queries that return a large result set?

UPDATE:
With large result i mean that the query is returning many rows. In our case there is no point in returning millions of rows, it is most likely a mistake in the query /UPDATE

Like if I do a

select * from [MyMultiMillionRowsTable]

can I see that query somewhere/somehow?

And a related question, Is it possible to see how many times such a query is executed?

The reason im asking is that we are suspecting that some of our queries return overly large result sets and that is putting a strain on the memory and network, and we cant easily find them in the codebase.

Yes, you can do that. One way is to use Glenn Berry’s queries, slightly modified, see comment in the query.

  -- Get top total rows queries for entire instance
    SELECT TOP(50) DB_NAME(t.[dbid]) AS [Database Name], LEFT(t.[text], 50) AS [Short Query Text],
    qs.total_logical_reads AS [Total Logical Reads],
    qs.min_logical_reads AS [Min Logical Reads],
    qs.total_logical_reads/qs.execution_count AS [Avg Logical Reads],
    qs.max_logical_reads AS [Max Logical Reads],   
    qs.min_worker_time AS [Min Worker Time],
    qs.total_worker_time/qs.execution_count AS [Avg Worker Time], 
    qs.max_worker_time AS [Max Worker Time], 
    qs.min_elapsed_time AS [Min Elapsed Time], 
    qs.total_elapsed_time/qs.execution_count AS [Avg Elapsed Time], 
    qs.max_elapsed_time AS [Max Elapsed Time],
    qs.execution_count AS [Execution Count], qs.creation_time AS [Creation Time],
    -- ,t.[text] AS [Complete Query Text], qp.query_plan AS [Query Plan] -- uncomment out these columns if not copying results to Excel
     qs.total_rows AS [Total Rows] -- added by DM. Works on SQL Server 2012+

    FROM sys.dm_exec_query_stats AS qs WITH (NOLOCK)
    CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS t 
    CROSS APPLY sys.dm_exec_query_plan(plan_handle) AS qp 
    ORDER BY qs.total_rows DESC OPTION (RECOMPILE); --modified by DM

  [1]: https://www.sqlskills.com/blogs/glenn/category/dmv-queries/

.. OR just explore the content of sys.dm_exec_query_stats.

See more sys.dm_exec_query_stats (Transact-SQL)

DMV sys.dm_exec_query_stats is changed started with SQL Server 2012. A few new columns that reflect your question are introduced.

total_rows

Total number of rows returned by the query. Cannot be null.

Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.

last_rows

Number of rows returned by the last execution of the query. Cannot be null.

Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.

min_rows

Minimum number of rows ever returned by the query during one execution. Cannot be null.

Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.

max_rows

Maximum number of rows ever returned by the query during one execution. Cannot be null.

Will always be 0 when a natively compiled stored procedure queries a memory-optimized table.