What does # indicate in SQL Server SELECT FROM?-CodePudding

Can someone quickly explain to me (as in without directing me to an illegible mountain of documentation) what a construct such as this (below) means in a SQL Server stored procedure?

Note, this is in a stored procedure on a database which resides on a server with many other databases and this is a generalization not the exact query

INSERT INTO [schema].TableName (Field1, Field2)
    SELECT
        OtherField1,
        OtherField2
    FROM 
        #Something TMP
    INNER JOIN 
        [schema].[Field] A ON A.Whatever = TMP.Whatever;

Note: I've been using MySQL and Postgres for years so understand the basics, but this SQL Server has a lot of features that are new to me. What I can tell here is that we are filling a table with the results from another table (or something) and that is what I want to know. What is the or something? In this example, what does #Something actually reference?

I am aware of linked servers, but I don't see one in this system with the name referenced. And I also don't see any definition in this stored procedure of what #Something references.

One thought I had was that perhaps a stored procedure can use another stored procedure and in that way perhaps this #Something is defined in the calling procedure? Is this even possible?

CodePudding user response：

This indicates that the table in question is a temp table.

You can find the table by checking the system database.

SELECT * 
FROM tempdb.sys.table 
WHERE name LIKE '#something%'

You can find other procedures that reference the same table by using the following query.

DECLARE @s VARCHAR(255) = '#Something';

SELECT DISTINCT
       O.name sproc_name,
       O.type_desc,
       (
         SELECT TOP (1)
                SUBSTRING(definition,CHARINDEX(@s,definition),100)
         FROM sys.sql_modules
         WHERE object_id=M.object_id
               AND CHARINDEX(@s,definition)>0
       ) DEF
FROM sys.sql_modules M
  JOIN sys.objects O
    ON M.object_id=O.object_id
WHERE M.definition LIKE '%'   @s   '%'
ORDER BY 2,1;

CodePudding user response：

Object names that start with a # are temporary objects. A single # means that the object is limited to the local scope (so therefore must have been created in the same scope of a parent of it), and 2 (or more) #s at the start denote "global" temporary objects, where they can be referenced in any scope.

Either way, a temporary object is dropped once the scope that created it ends or the object is no longer in use (which ever is last).