Get Tables by column name

This script search the tables in the database whcih has the given column name. This query looks for the column Employee_Id field in tables and list out the table names. This uses sysobjects , syscolumns system table to search the column name. sysobjects.xtype = 'u' means to look for only the user tables.

You can also search for multiple columns by adding the condition in the where clasue as follows.

syscolumns.name like '%Employee_Id%' or syscolumns.name like '%Employee_Number%'

Here it search the tables with column Employee_Id and Employee_Number.

 

SELECT distinct
sysobjects.name as "Table", 
syscolumns.name as "Column",
syscolumns.xusertype as "type",
sysobjects.xtype as "Objtype"
from sysobjects , syscolumns 
where sysobjects.id = syscolumns.id
and sysobjects.xtype = 'u'
and syscolumns.name like '%Employee_Id%'
order by sysobjects.name, syscolumns.name

Rate

4 (5)

Share

Share

Rate

4 (5)