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