CODEWITHSUNDEEP
sqlsql-server
HansDampf
HansDampf

Reputation: 121

How do I find table column in different databases?

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?

Upvotes: 0

Views: 239

Answers (1)

Matt
Matt

Reputation: 98

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>%'

Upvotes: 1

Related Questions