[Bug]: Queries in `tempdb.sys.tables` returns no results

[PauloHMattos]

What happened?

Queries in tempdb.sys.tables returns no results.

Running this in SSMS the return is a empty dataset

CREATE TABLE #MyTempTable (Id INT)
SELECT * FROM tempdb.sys.tables

tempdb.sys.objects also returns empty.

Version

BABEL_4_X_DEV (Default)

Extension

babelfishpg_tsql (Default)

Which flavor of Linux are you using when you see the bug?

Ubuntu (Default)

Relevant log output

No response

Code of Conduct

• [X] I agree to follow this project's Code of Conduct.

[shalinilohia50]

FYI, this issue has been fixed: https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/2679

[tanscorpio7]

Does not look like this is fixed, https://github.com/babelfish-for-postgresql/babelfish_extensions/pull/2679 fixed a different issue

1> CREATE TABLE #t (id INT)
2> GO
1> SELECT name FROM tempdb.sys.tables WHERE name like '#t%'
2> GO
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------

(0 rows affected)

1> SELECT name FROM sys.tables WHERE name like '#t%'
2> GO
name                                                                                                                            
--------------------------------------------------------------------------------------------------------------------------------

(0 rows affected)

[priyansx]

While using Babelfish we can use query "SELECT RELNAME FROM sys.babelfish_get_enr_list() WHERE RELNAME LIKE '#%' to get the session local temp table names.

[PauloHMattos]

While using Babelfish we can use query "SELECT RELNAME FROM sys.babelfish_get_enr_list() WHERE RELNAME LIKE '#%' to get the session local temp table names.

My application performs introspection in the regular and temp tables to extract columns, indexes and constraints. We need a way to do this in babelfish.

[robverschoor]

Metadata for #tmp table columns is not currently in the Babelfish catalogs. Only object name can be obtained as shown above. There is a trick to get the metadata anyway: copy the temp table into a permanent table - then you can obtain the metadata from the regular catalogs (at least for the columns). When doing this you should make sure the table being created has a unique name. See this example: https://github.com/babelfish-for-postgresql/babelfish_extensions/blob/2da1db5aa02928e770e1bd21dc9ea9897912a1d4/contrib/babelfishpg_tsql/sql/babelfishpg_tsql.sql#L3330