CODEWITHSUNDEEP
sqlsql-server
Naga
Naga

Reputation: 151

Identifying tables from a given database

I have a database called xyz and it has many tables.

Now if I have name of a column and want to know all the tables where this column is present in the given database, is it possible to do that?

I am working on SQL Server.

Upvotes: 0

Views: 60

Answers (2)

Devart
Devart

Reputation: 121932

Try this one -

SELECT SCHEMA_NAME(o.[schema_id]) + '.' + o.name
FROM sys.objects o WITH (NOWAIT)
JOIN sys.columns c WITH (NOWAIT) ON o.[object_id] = c.[object_id]
WHERE o.[type] = 'U'
    AND c.name = 'column_name'

Upvotes: 1

aleroot
aleroot

Reputation: 72636

You have to query information_schema SQL Server internal table : 

SELECT  TABLE_NAME 
FROM INFORMATION_SCHEMA.COLUMNS 
WHERE COLUMN_NAME = 'columnname'

Upvotes: 7

Related Questions