CODEWITHSUNDEEP
sqlsql-server
kingshuk
kingshuk

Reputation: 23

How to get a list of tables which contains a common column value in SQL Server

I have a number of tables in which some of them has common Asset_ID, what will be the query to get a list of table names with common Asset_ID.

Upvotes: 1

Views: 2775

Answers (3)

tsohtan
tsohtan

Reputation: 850

i always use this to find all match keyword, it will list down all match sp, tables, function...

select distinct name 
from syscomments c 
join sysobjects o on c.id = o.id 
where TEXT like '%Asset_ID%'

Upvotes: 0

bansi
bansi

Reputation: 57002

try select table_name from information_schema.columns where column_name='Asset_ID'

If you want to get the table names depending on the value of data in the column you cannot get it by simple query try the following

declare @val_to_search varchar(50), @column_name varchar(50)
Select @val_to_search = 'ddh224', @column_name='Asset_ID'

declare tbl cursor for 
select table_name from information_schema.columns where column_name=@column_name
declare @tablename varchar(200),@qstr varchar(max)
declare @datapen table(table_name varchar(200))

open tbl
fetch tbl into @tablename
while @@fetch_status=0
begin
select @qstr='select top 1 '''+@tablename+''' from '+@tablename+' where '+ @column_name + ' =''' + @val_to_search + ''''
insert into @datapen
exec(@qstr)

fetch tbl into @tablename
end
close tbl
deallocate tbl
select * from @datapen pen

Upvotes: 2

Digital Alchemist
Digital Alchemist

Reputation: 2332

Below Query might help u out!

This query will list down all tables along with Columns Names!

SELECT * FROM INFORMATION_SCHEMA.COLUMNS where COLUMN_NAME LIKE '%columnName%'

This query will list down all tables!

SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_NAME LIKE '%tableName%'

Upvotes: 0

Related Questions