Query to Find Column From All Tables of Database


Sometimes we need to find whether a column exists in any of the table in the entire database. If the tables count is smaller like 10 or 20, then we can find it with in few seconds or minutes. But if count is very large and each table has many no of columns then it becomes takes long time.

The following query returns all the table names in the database which have the given column name with in few seconds.

USE  dbName
GO

SELECT SCHEMA_NAME(schema_id) AS SchemaName
,T.name AS TableName
,C.name AS ColumnName
FROM sys.tables AS T
INNER JOIN sys.columns C ON T.OBJECT_ID = C.OBJECT_ID
WHERE C.name='ColumnName'
ORDER BY SchemaName, TableName,C.column_id;


Gopikrishna

    Blogger Comment
    Facebook Comment

0 comments:

Post a Comment