Temporary table scope is unclear and users should be better warned

Documentation says: > A local temporary table created within a stored procedure or trigger can have the same name as a temporary table that was created before the stored procedure or trigger is called. However, if a query references a temporary table and two temporary tables with the same name exist at that time, it isn't defined which table the query is resolved against. Nested stored procedures can also create temporary tables with the same name as a temporary table that was created by the stored procedure that called it. However, for modifications to resolve to the table that was created in the nested procedure, the table must have the same structure, with the same column names, as the table created in the calling procedure. This is shown in the following example. https://github.com/MicrosoftDocs/sql-docs/blob/2582f61116ab01a964f3f054feb67e0ca0ec4fcb/docs/t-sql/statements/create-table-transact-sql.md?plain=1#L1243 "For modifications to resolve" is unclear and the problem is (a) not limited to the caller of the SP (any parent caller in the calling tree is affected) and (b) not limited to modifications. Indeed, if a stored procedure creates a temporary table with a given name and another procedure creates a temporary table with the same name and calls this procedure: - if a plan exists for the first procedure, they each refer to their own temporary table and everything works as expected - if the first procedure hasn't been called, a plan is created but if the columns referred in the callee do not match the columns in the temporary table created by the caller, an error occurs This code crashes: ```sql create or alter procedure f as begin create table #t (x int) select COUNT(*) from #t where x = 42 end go create or alter procedure g as begin create table #t (y int) insert into #t exec f end go exec g ``` while this doesn't: ```sql create or alter procedure f as begin create table #t (x int) select COUNT(*) from #t where x = 42 end go create or alter procedure g as begin create table #t (y int) insert into #t exec f end go exec f go exec g ``` This is related to the plan as this does crash: ``` create or alter procedure f as begin create table #t (x int) select COUNT(*) from #t where x = 42 end go create or alter procedure g as begin create table #t (y int) insert into #t exec f end go exec f go DBCC FREESYSTEMCACHE ('ALL') WITH MARK_IN_USE_FOR_REMOVAL; go exec g go ``` So documentation should be more clear about names of temporary tables and warn users against having potential naming conflicts as obviously the query planner can be confused by nested SPs that create temporary tables with the same name than existing temporary tables.