What are the tables whose names start with `TT_`?

Question

Using this SQL statement to find in my database all tables the have columns whose names contain ItemId:

SELECT o.name,
       c.name
FROM sys.columns c
INNER JOIN sys.objects o ON c.object_id = o.object_id
WHERE c.name LIKE '%ItemId%'
ORDER BY o.name, c.column_id

I received a result containing two tables:

ResourceData TT_Data_117F9D94

I know about the ResourceData table and I can find it in the list of my tables.

But I have not been able to find in my database any table name TT_Data_117F9D94.

Any idea where/how did this table show up in the result of my query?

score 2 · Answer 1 · answered Jan 28 '16 at 08:18

2

First of all You should have used

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME LIKE '%itemid%';

Probably TT_Data_117F9D94 is a data table in your database. Please check the database design of that table. Or simply do a select query from that table.

answered Jan 28 '16 at 08:18

Sanu Antony

364
4
15

1

Thanks +1 for the improved SQL statement. It has much friendlier output than the statement I originally used. It also does *not* list that weird looking `TT_` table name. When I do a select query from TT_Data_117F9D94 I get "Invalid object named TT_Data_117F9D94". Which brings me back to the original question: *Why would that SQL statement produce a non-existent table?* – datps Jan 28 '16 at 08:26
Simple is always better – Sanu Antony Jan 28 '16 at 09:09

score 1 · Accepted Answer · answered Jan 28 '16 at 08:20

1

Check about table types -

CREATE TYPE dbo.TT_Type AS TABLE (a INT)

SELECT o.name, c.name
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
WHERE c.name = 'a'

Correct query -

SELECT SCHEMA_NAME(o.[schema_id]), o.name, c.name
FROM sys.columns c
JOIN sys.objects o ON c.[object_id] = o.[object_id]
WHERE o.[type] = 'U' -- only user tables

answered Jan 28 '16 at 08:20

Devart

119,203
23
166
186

@datps just add filter for `sys.objects` or select table objects from `sys.tables` – Devart Jan 28 '16 at 08:35
Indeed it looks like TT stands for table types as described in this [sys.objects page](https://msdn.microsoft.com/en-us/library/ms190324%28v=sql.110%29.aspx). Are you saying that this table is created on-the-fly for the lifetime of my statements run? – datps Jan 28 '16 at 08:36
1

@datps table type is a object which is stored in `sys.table_types` (`TT_Type`) and also exists as reference with system name in `sys.objects` (`TT_TT_Type_5A6F5FCC`) – Devart Jan 28 '16 at 08:40
Thanks. I can see `sys.table_types` now in `Databases` > `System Databases` > `Views` > `System Views`. In an SQL statement it would be specified as `SELECT * FROM [master].[sys].[table_types]`. Interesting. I am just beginning to learn about the fine details of MS SQL Server. – datps Jan 28 '16 at 08:56

What are the tables whose names start with `TT_`?

2 Answers2