question

mohittyagi avatar image
mohittyagi 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.
sql-server-2008columntablenames
7 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
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.
3 Likes 3 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
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?
0 Likes 0 ·
mohittyagi avatar image mohittyagi commented ·
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.
0 Likes 0 ·
Magnus Ahlkvist avatar image Magnus Ahlkvist commented ·
Exactly my thought. I can't think of any time, past or future where I'd need something like that...
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
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.
0 Likes 0 ·
Show more comments
Magnus Ahlkvist avatar image
Magnus Ahlkvist answered
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;
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

TimothyAWiseman avatar image
TimothyAWiseman answered
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/
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

mohittyagi avatar image mohittyagi commented ·
Thanks TimothyAWiseman ,Thanks imothyAWiseman
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.