• Jagan Mohan Reddy Rajidi

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



3 views0 comments

Recent Posts

See All

When you create any new object(table, stored procedure, view,...etc.) in database then same object name will be stored in system table Sysobjects. Following query returns all the object names along wi