We have 2 different versions in operation - 2012 and 2014, migration is planned for 2016. I am also interested in temporary tables that are with a grid.
- For example: select * from [INFORMATION_SCHEMA]. [TABLES] where TABLE_NAME = 'Clients' Temporary tables exist only in the context of execution. - Konst
- It's also a good idea to check TABLE_SCHEMA in WHERE - otherwise you can find a table in another database ... - Akina
- @Akina, in another database - it is impossible, INFORMATION_SCHEMA contains only the objects of the current database, but in a different scheme - yes, it is possible, therefore the scheme should be checked. - minamoto
- @minamoto Yes, sorry, inattentively looked at what server we are talking about ... - Akina
- @Konst Thank you, as I understand it for the current DB, but is there a way for all the databases on the server to check? - Aleksandr Titenko
|
1 answer
We usually use object_id table retrieval (this is suitable for all other objects as well - functions, CP, views, etc.). If it is null, then the object does not exist. Type indication is not necessary, but desirable - because there may be an object of another type with the same name.
if object_id('dbo.myImportantTable', 'U') is null ... Accordingly, temporary tables live in tempdb, so you need to look for them there:
select object_id('tempdb.dbo.#testtable', 'U') 
minamoto minamoto
3,981 9 nineteen
|