What's a good use case for SELECT * in production code?

Out of habit, I never use SELECT * in production code (I only use it with ad-hoc scrap queries, typically when learning the schema of an object).
But I ran across a case now where I'm tempted to use it but would feel cheap if I did.

My use case is inside a stored procedure where a local temp table is created that should always match the underlying table used to create it, whenever the stored procedure runs. The temp table is populated much later on, so a quick hack to create the temp table without being verbose would be SELECT * INTO #TempTable FROM RealTable WHERE 1 = 0 especially for a table with hundreds of columns.

If the consumer of my stored procedure is agnostic to dynamic result sets, then are there any issues with me selling my services to SELECT *?

I generally abhor SELECT * in production code, and I've been in a situation where its use led to massive amounts of rework later. Your case does look like a fair use of it though.

The place where I find SELECT * to be a must - and its evil cousin "INSERT INTO tbl" without a column list - is in an archiving situation, where rows are being moved to another table that must have the same structure.

INSERT INTO SalesOrderArchive  -- Note no column list
SELECT *
  FROM SalesOrder
 WHERE OrderDate < @OneYearAgo

DELETE FROM SalesOrder
 WHERE OrderDate < @OneYearAgo

If a new column is added to SalesOrder in the future, but not to SalesOrderArchive, the INSERT will fail. Which sounds bad, but it's actually a really good thing! Because the alternative is much worse. If all the columns were listed on the INSERT and the SELECT, then the INSERT would succeed, and so would the following DELETE (which is effectively "DELETE *"). Production code that succeeds doesn't get any attention, and it may be a long time before someone notices that the new column is not being archived, but being silently deleted altogether.

StackExchange Database Administrators Q#253873, answer score: 59