How Many Temporary Tables are Created So Far in SQL Server? – Interview Question of the Week #149

Question: How Many Temporary Tables are Created So Far in SQL Server?

Answer: In my career, I have heard many different questions in the interview, however, this one is indeed a very interesting one. There is no direct answer to this question. However, there is definitely a good workaround to get an answer to this question.

The reason one wants to know how many temporary tables are created since last restart can be many. Personally, I do not see much value in this question as this is not the way to judge the consumption of the TempDB. However, let us understand the answer.

First of all, there is no DMV, which provides this kind of information directly. If you wish, you can configure a counter for TempDB and watch how many tables are created and that may not be accurate.

Here is the easiest way to find out how many various temporary tables have been created so far in the SQL Server since the last restart.

First of all create a temporary table.

CREATE TABLE #Test8 (ID INT)
GO

Next just fun following query.

SELECT MAX(RIGHT([name],12)) TempTableCreated
FROM tempdb.sys.tables

You will a number which will be in the Format HEXADECIMAL. Just convert this number to decimal with online function and that is your answer.

If you have alternative answer or can improve this question, please post that in the comment and I will be happy to post it on the blog with due credit.

Reference: Pinal Dave (https://blog.sqlauthority.com

, ,
Previous Post
How to Assign Multiple Variables in a Single T-SQL Query? – Interview Question of the Week #148
Next Post
When to Use IDENT_CURRENT? – Interview Question of the Week #150

Related Posts

3 Comments. Leave new

  • Does this solution also include recreated global temp tables?

    Reply
  • I got value butor_access
    from
    SELECT MAX(RIGHT([name],12))AS TempTableCreated
    FROM tempdb.sys.tables
    which is MSdistributor_access [SQL SERVER 2008 R2]

    Reply
  • Sir,
    I guess..this query is not giving the proper result. In my live environment its giving result as “tendedEvents”..I myself don’t know what does this mean.

    Reply

Leave a Reply Cancel reply

Exit mobile version