x

how to find table name,column name by column value in sqlserver

I am trying to get the all table name, column name in the database by using the column value.
more ▼

asked Sep 26, 2012 at 08:08 AM in Default

mohittyagi gravatar image

mohittyagi
0 1 1 1

Before jumping into solutions, is this what you want: Based on a name, find all tables which has a column with that name and display the tablename + column definition for the column with the matched name? Or do you want all column definitions for all tables which has a column name that matches?
Sep 26, 2012 at 08:15 AM Magnus Ahlkvist

Dear Magnus, I want all the table name and column name having value = 'xyz' in database. Example... Table name : table1 col1 col2 col3 col4 col5 table2 col1 col2 col3 table3 col1 col2 col3 col4

I want the solution like table1 col1 xyz tbale2 col3 xyz table3 col2 xyz etc.... pls reply me with suitable solution thanks.
Sep 26, 2012 at 08:23 AM mohittyagi
Why would you need to do that? You're talking about scanning every column on every table. That's going to be incredibly expensive peformance-wise.
Sep 26, 2012 at 10:07 AM Grant Fritchey ♦♦
Exactly my thought. I can't think of any time, past or future where I'd need something like that...
Sep 26, 2012 at 10:15 AM Magnus Ahlkvist
I have run into real world instances where I needed something similar. I have never needed to search every table, but I have had cases where I needed to search most columns of a huge number of tables. But that involved a proverbial "needle in a haystack" situation where I had to find specific data in a database created by someone else with non-normalized tables that were mostly generated by an automated import process. So we had a lot of tables with similar, but not always the same (the process changed over time) schema and the data could have been in any of them. It took a very long time to process.
Sep 26, 2012 at 04:23 PM TimothyAWiseman
show all comments (comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I wrote an article about searching for a value without knowing what column it is in a while ago that included a stored procedure which would search an entire table for a particular value. You could then put that stored procedure into a loop or use sp_msforeachtable (undocumented) to search the entire database.

However, much like Magnus' answer and as I mention in the article, this can be agonizingly slow for even one table. It is detailed at: Finding Values With Numerous Columns

more ▼

answered Sep 26, 2012 at 04:24 PM

TimothyAWiseman gravatar image

TimothyAWiseman
15.5k 20 23 32

Thanks TimothyAWiseman ,Thanks imothyAWiseman
Sep 27, 2012 at 06:03 AM mohittyagi
(comments are locked)
10|1200 characters needed characters left

I think something like this could work. But be aware that this will be a heavy operation on the database, since it will make a SELECT on all rows, for all string-type columns in all databases. For all columns which are not indexed, this will mean a table scan on the table every time. I haven't tested this query and I don't take any responsibility for the results and/or the impact on your server.

declare @value varchar(10);
set @value='xyz';
declare @s nvarchar(max);
set @s='';
select @s = @s +
'SELECT DISTINCT ''' + SCHEMA_NAME(t.schema_id) + '.' + t.name + ''' as TableName, ''' + c.name + ''' as ColumnName,''' + @value + ''' as Value
FROM [' + SCHEMA_NAME(t.schema_id) + '].[' + t.name + '] WHERE [' + c.name + '] = ''' + @value + '''
UNION ALL
'
from sys.columns c
inner join sys.tables t
on t.object_id=c.object_id 
and c.system_type_id in (35,99,175,231,167,239)
AND t.type='U';
SET @s = LEFT(@s,LEN(@s)-LEN('UNION ALL')) + ';';
exec sp_executesql @s;
more ▼

answered Sep 26, 2012 at 09:28 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x1842
x18
x6

asked: Sep 26, 2012 at 08:08 AM

Seen: 3321 times

Last Updated: Sep 27, 2012 at 05:24 PM