Reputation: 768
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
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
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