datps
datps

Reputation: 768

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

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?

Upvotes: 2

Views: 2105

Answers (2)

Devart
Devart

Reputation: 121922

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

Upvotes: 1

Sanu Antony
Sanu Antony

Reputation: 364

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.

Upvotes: 2

Related Questions