By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE #MYTEMPTEBLE(I INT);
SELECT * FROM TempDB.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME IN (
SELECT NAME FROM TempDB.SYS.TABLES WHERE OBJECT_ID=OBJECT_ID('TempDB.dbo.#MYTEMPTEBLE')
);
| TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_PRECISION_RADIX | NUMERIC_SCALE | DATETIME_PRECISION | CHARACTER_SET_CATALOG | CHARACTER_SET_SCHEMA | CHARACTER_SET_NAME | COLLATION_CATALOG | COLLATION_SCHEMA | COLLATION_NAME | DOMAIN_CATALOG | DOMAIN_SCHEMA | DOMAIN_NAME |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| tempdb | dbo | #MYTEMPTEBLE________________________________________________________________________________________________________00000000001F | I | 1 | null | YES | int | null | null | 10 | 10 | 0 | null | null | null | null | null | null | null | null | null | null |
IF EXISTS(SELECT * FROM sys.tables WHERE SCHEMA_NAME(schema_id) LIKE 'dbo' AND name like '#MYTEMPTEBLE')
SELECT 1;
ELSE
SELECT 0;
| (No column name) |
|---|
| 0 |
Drop Table IF Exists #MYTEMPTEBLE;