I have a number of tables in the database and i have a column value as "abc" coming from one of the tables in the database,Now i need to find the table name from where this column value is coming?
I have a number of tables in the database and i have a column value as "abc" coming from one of the tables in the database,Now i need to find the table name from where this column value is coming?
you can check the below process, this process is bit lenghty but this will give you table name separted by comma value where column value matched with any table's column value.
--you need to iterate the whole columns of entire table to find the matched record
--another thing is that you need dynamic sql to find the table name
Declare @Value varchar(50) --value for that find the column Name
Set @Value='ABC'
--for that I m creaating one tamp table
Create Table #Table
(
TableName Varchar(500),ColumnName Varchar(500),
Id int Identity(1,1) --use for iteration
)
insert into #Table
SELECT TABLE_SCHEMA + '.' + TABLE_NAME As TableNam,Column_name As ColumnName
FROM INFORMATION_SCHEMA.COLUMNS
where Data_type In ('char','nchar','varchar','nvarchar')
--use char supported datatype because you provide sample data as 'ABC'
Declare @Count Int --total record to iterated
Set @Count=0;
Select @Count=Count(*) From #Table
Declare @I int --initial value one to iterate
Set @I=1;
Declare @TableName varchar(500)
Set @TableName=''
Declare @ColumnName varchar(500)
Set @ColumnName=''
Declare @Str nvarchar(1000)
Set @Str=''
Declare @param nvarchar(1000)
set @param=''
Declare @TableNameFound varchar(max)
Set @TableNameFound=''
Declare @Found bit
Set @Found=0;
While @I<=@Count
Begin
Set @Found=0;
Select @TableName=TableName,@ColumnName=ColumnName From #Table where Id=@I;
Set @param='@TableName varchar(500),@ColumnName varchar(500),@Value varchar(50),@TableNameFound varchar(max),@Found bit output'
Set @str='Select @Found=1 From '+@TableName+' where '+@ColumnName+'=@Value'
-- here we are using tablename and actual value to find in table
print @str
exec sp_executesql @str,@param,@TableName,@ColumnName,@Value,TableNameFound,@Found output
if @Found=1
Begin
Set @TableNameFound=@TableNameFound+','+@TableName
End
print @I
--increament value of @I
Set @I=@I+1;
End
Select substring(@TableNameFound,2,len(@TableNameFound)) As TableFound
--Table name where column value matched
drop table #Table
Lokesh Reddy
Member
56 Points
56 Posts
How to get a Table name from the column Value in Sql Server?
Aug 30, 2010 06:00 AM|LINK
Hi All,
I have a number of tables in the database and i have a column value as "abc" coming from one of the tables in the database,Now i need to find the table name from where this column value is coming?
asp.net SQL Server 2000
Rajneesh Ver...
All-Star
37034 Points
6810 Posts
Re: How to get a Table name from the column Value in Sql Server?
Aug 30, 2010 06:09 AM|LINK
Hi,
also can check below link
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=58983
Thanks..
www.rajneeshverma.com
Keep Forums Clean || Use Alert Moderators.
Lokesh Reddy
Member
56 Points
56 Posts
Re: How to get a Table name from the column Value in Sql Server?
Aug 30, 2010 06:49 AM|LINK
the solution proposed by you will be based on the column name,i am looking to find the table name based on the column value.
yrb.yogi
Star
14460 Points
2402 Posts
Re: How to get a Table name from the column Value in Sql Server?
Aug 30, 2010 06:51 AM|LINK
you can check the below process, this process is bit lenghty but this will give you table name separted by comma value where column value matched with any table's column value.
--you need to iterate the whole columns of entire table to find the matched record --another thing is that you need dynamic sql to find the table name Declare @Value varchar(50) --value for that find the column Name Set @Value='ABC' --for that I m creaating one tamp table Create Table #Table ( TableName Varchar(500),ColumnName Varchar(500), Id int Identity(1,1) --use for iteration ) insert into #Table SELECT TABLE_SCHEMA + '.' + TABLE_NAME As TableNam,Column_name As ColumnName FROM INFORMATION_SCHEMA.COLUMNS where Data_type In ('char','nchar','varchar','nvarchar') --use char supported datatype because you provide sample data as 'ABC' Declare @Count Int --total record to iterated Set @Count=0; Select @Count=Count(*) From #Table Declare @I int --initial value one to iterate Set @I=1; Declare @TableName varchar(500) Set @TableName='' Declare @ColumnName varchar(500) Set @ColumnName='' Declare @Str nvarchar(1000) Set @Str='' Declare @param nvarchar(1000) set @param='' Declare @TableNameFound varchar(max) Set @TableNameFound='' Declare @Found bit Set @Found=0; While @I<=@Count Begin Set @Found=0; Select @TableName=TableName,@ColumnName=ColumnName From #Table where Id=@I; Set @param='@TableName varchar(500),@ColumnName varchar(500),@Value varchar(50),@TableNameFound varchar(max),@Found bit output' Set @str='Select @Found=1 From '+@TableName+' where '+@ColumnName+'=@Value' -- here we are using tablename and actual value to find in table print @str exec sp_executesql @str,@param,@TableName,@ColumnName,@Value,TableNameFound,@Found output if @Found=1 Begin Set @TableNameFound=@TableNameFound+','+@TableName End print @I --increament value of @I Set @I=@I+1; End Select substring(@TableNameFound,2,len(@TableNameFound)) As TableFound --Table name where column value matched drop table #Table.Net All About
Lokesh Reddy
Member
56 Points
56 Posts
Re: How to get a Table name from the column Value in Sql Server?
Aug 30, 2010 10:02 AM|LINK
tried the above suggested solution ,but no luck.....
yrb.yogi
Star
14460 Points
2402 Posts
Re: How to get a Table name from the column Value in Sql Server?
Aug 30, 2010 10:14 AM|LINK
Let me know where you were not able to find the solution..?
I think this method will work...cent percent. this is very tricky one where I m going to iterate the whole columns record found in database.
Please provide what will be the output you were got when attemptd to run above solution
.Net All About
KumarHarsh
All-Star
15133 Points
3647 Posts
Re: How to get a Table name from the column Value in Sql Server?
Aug 30, 2010 12:39 PM|LINK
refer this,
http://forums.asp.net/p/1596130/4049340.aspx#4049340
Re-explain ur problem.
Show the work u hv done so far so that we catch the error.
Kumar Harsh
Lokesh Reddy
Member
56 Points
56 Posts
Re: How to get a Table name from the column Value in Sql Server?
Sep 01, 2010 04:39 AM|LINK
Set @Value='ABC' i have replaced the "ABC" with the text required and searched and ran the script but i did nt get any results for the script
yrb.yogi
Star
14460 Points
2402 Posts
Re: How to get a Table name from the column Value in Sql Server?
Sep 01, 2010 04:46 AM|LINK
Are you sure that your table's column contains the value of "ABC". I doubt that.
Please replace the value with existing data in column. you will surely get the table name....
.Net All About
dheerajchoud...
Member
11 Points
19 Posts
Re: How to get a Table name from the column Value in Sql Server?
Jul 03, 2012 07:26 AM|LINK
Hello,
I have used your sql code for searching column by column name ,this column name exit various table but I are not getting any table name
I am give column name in value parameter.
please help.