SQL Articles

SQL query to search a column name in all tables


Sometimes you get a scenario where you know the column name but don't remember which table it belongs to.

Following query will search for a given column name in database schema and returns all objects that contains given column name.

SELECT C.NAME[Column Name],O.NAME [Found in Object],
CASE O.XTYPE 
    WHEN 'U' THEN 'Table'
    WHEN 'P' THEN 'Stored Procedure'
    WHEN 'FN' THEN 'Scalar Function'
    WHEN 'V' THEN 'View' 
    WHEN 'S' THEN 'System Table'
    WHEN 'IT' THEN 'Internal table'
ELSE 
'Sysobjects.XTYPE ='''+O.XTYPE +'''' END 'Object Type'							
FROM SYSCOLUMNS C 
INNER JOIN SYSOBJECTS O ON C.ID=O.ID
AND C.NAME LIKE '%employee%'
                

Output

Column search example