asked
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.
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.
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?
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.
Exactly my thought. I can't think of any time, past or future where I'd need something like that...
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.
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;
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][1] [1]:
http://www.sqlservercentral.com/articles/Search/64877/
10 People are following this question.
