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

avatar image

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

avatar image

15.6k 22 51 38

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 + '''
 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

avatar image

Magnus Ahlkvist
21.1k 19 39 42

(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.

Follow this question

By Email:

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



Answers and Comments

SQL Server Central

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



asked: Sep 26, 2012 at 08:08 AM

Seen: 6968 times

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

Copyright 2016 Redgate Software. Privacy Policy