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)
3 Comments. Leave new
Does this solution also include recreated global temp tables?
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]
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.