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
