How do I find table column in different databases?

Question

I am trying to find table columns in different tables and different databases.

So far, I do have a query to do that, however it does not tell me in which database that column and table lays in.

Current code:

SELECT sys.columns.name AS ColumnName, tables.name AS TableName FROM sys.columns JOIN sys.tables ON sys.columns.object_id = tables.object_id WHERE sys.columns.name LIKE '%COLUMNNAME%'

Does anyone have an idea what I need to add to display the database name as well?

"however it does not tell me in which database that column and Table lays in" That query is run in the context of the current database; so it's whatever database you're currently connected to. It won't tell you the details of columns/tables in any other database, just the current database. If you can't remember what database you're connect to, use DB_NAME(). — Thom A
– Thom A ♦, Commented Oct 11, 2019 at 14:19
Thanks! Good to know. Could it be that some tables are hidden? I only got read access, and even though the above code tells me that the key i am looking for is in Table X, i cant find that table in the object explorer. — HansDampf
– HansDampf, Commented Oct 11, 2019 at 14:35
Perhaps the table has been created since you connected the to server and you haven't refreshed the object explorer? — Thom A
– Thom A ♦, Commented Oct 11, 2019 at 14:45
I dont think that will be the case. I am a mediocre user of SQL and i just build queries for some analytical usage. I am not in charge of building the servers etc... — HansDampf
– HansDampf, Commented Oct 11, 2019 at 14:51
So, if you're not in charge of the objects, then wouldn't that mean that there well could have been an object since you connected that you're not aware of? If the query is telling you there's an object there, then you'll be able to see it in the Object Explorer, as it gets the list of objects from the sys objects when you first connect. Perhaps is on a different schema and you're looking in the wrong place; but not having refreshed the Object Exp[lorer is the very likely candidate in my opinion. I can only guess though; i can't see what you do. — Thom A
– Thom A ♦, Commented Oct 11, 2019 at 14:54

Matt · Accepted Answer · 2019-10-11 14:38:27Z

1

I'd suggest using INFORMATION_SCHEMA.tables or .columns like:

SELECT table_name, table_schema, table_catalog from INFORMATION_SCHEMA.tables where Table_name like '%<table>%'

Same with .columns. Just replace table_name with column_name

SELECT column_name, table_schema, table_catalog, * from INFORMATION_SCHEMA.COLUMNS where column_name like '%<Column>%'

edited Oct 11, 2019 at 14:38

answered Oct 11, 2019 at 14:25

Matt

988 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Sean Lange Over a year ago

Be careful here. These views are a bit sketchy at best, they are there for backwards compatibility. The column table_schema is not accurate and MS even states that in the documentation. learn.microsoft.com/en-us/sql/relational-databases/…

HansDampf Over a year ago

Thanks, I gave your query a go, but I still cant seem to find that table, although it tells me the database :/

Matt Over a year ago

@SeanLange I agree, with intricate DB's. The DB_Name() is good as well SELECT DB_NAME(database_id) AS [Database], database_id FROM sys.databases. Just providing alternate answers to the other(s)

Matt Over a year ago

@HansDampf I edited my post. If you're trying columns. Try the bottom version. It'll display column, table, catalog, etc.

Collectives™ on Stack Overflow

How do I find table column in different databases?

1 Answer 1

4 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Related