Find specific Column in all Table in Sql Server

Rajesh_Kumar
Posted by Rajesh_Kumar under Sql Server category on | Points: 40 | Views : 826
There are a lots of way to achieve this,some are listed below:-
Declare @column_name varchar(50);
Set @column_name = 'employee_id';
1st way:
select t.name as table_name,
schema_name(schema_id) as schema_name,
c.name as column_name
from sys.tables as t
inner join sys.columns c on t.object_id = c.object_id
where c.name like '%'+@column_name+'%'
order by schema_name, table_name;
2nd way:
select table_name,table_schema as [schema_name],column_name,data_type from information_schema.columns
where column_name like '%'+@column_name+'%' order by table_name;
3rd way:
select name as table_name from sysobjects where id IN (select id from syscolumns where name like '%'+@column_name+'%');
4th way:
select table_name,table_schema as [schema_name],column_name,data_type
from information_schema.columns
where table_name in (select name from sysobjects
where xtype = 'U')
and column_name like '%'+@column_name+'%'
order by table_schema,table_name;

Comments or Responses

Login to post response