CODEWITHSUNDEEP
sqldatabase
Iffi
Iffi

Reputation: 588

Need to find specific column name from database

I have one database on my server I want to filter one column name from the whole database where that column is used.

E.g: If column name is "AlternativeID" is exist in 5 tables and than I want the query that find this column name exists in which tables.?

I find the following query to find specific column name from database.

SELECT * FROM sys.columns WHERE name LIKE '%AlternativeID%'

I hope this is make sense to everyone. Any help will appreciated. Thanks!!

Upvotes: 0

Views: 83

Answers (2)

Evans Murithi
Evans Murithi

Reputation: 3257

Try:

SELECT DISTINCT TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN ('AlternativeID')
    AND TABLE_SCHEMA='YourDatabaseName';

Upvotes: 0

PKirby
PKirby

Reputation: 889

Try this:

SELECT c.name AS ColName, t.name AS TableName
FROM sys.columns c
    JOIN sys.tables t ON c.object_id = t.object_id
WHERE c.name LIKE '%AlternativeID%'

OR...

SELECT COLUMN_NAME, TABLE_NAME  
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME LIKE '%AlternativeID%'

Source : Find all tables containing column with specified name

Hope this helps...

Upvotes: 1

Related Questions