Jagan Mohan Reddy Rajidi
- Sep 25, 2022
- 1 min read

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

Recent Posts

See All

SQL Server performance tip - Do not use NOT IN clause in Subquery use LEFT OUTER JOIN instead

Do not use NOT IN clause in sub query, instead of this use LEFT OUTER JOIN shown in below example query. This will reduce the execution time and improves the performance. To know more details with exa

SQL server query to search object name in database

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

SQL query to display total number of rows for each table in database

Following query displays the total number of rows corresponding to each table in the database. select o.name 'Table Name',rowcnt 'Total Number of Rows' from sysindexes i inner join sysobjects o on i.

SQL Articles with examples

SQL query to search a column name in all tables

Recent Posts