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 characters needed characters left characters exceeded

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

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 ·
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 ·
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 ·
Exactly my thought. I can't think of any time, past or future where I'd need something like that...
0 Likes 0 ·
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 characters needed characters left characters exceeded

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 characters needed characters left characters exceeded

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

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.