Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Hommer
Aged Yak Warrior
808 Posts |
 Posted - 2013-10-16 : 13:33:20
|
| Hi,First, I totally understand my question is against the sql norm, which is given known tablename/columnname to query the return values.I want to have this query that returns the table names and column names that contain the given value(s).Business needs dictated that I have to start from given values instead. That is to say we own the apps developed by vendors, and own the dbs, but no middle tier code, nor data dictionary.So, I started with this select distinct object_Name(Object_ID) myTbls, [name] from sys.columns where name like '%mycolname%'and object_Name(Object_ID) not like 'fn%'I have to guess the possible column name as the starter. I realize if the condition value is not very unique, the return dataset could be huge. It is a performance nightmare and it is useless. So I hope the solution will let me specify, say return the first 1000 matches. Any one know any this kind of script? I don't even know how to google it. But the business case is there. I don't think I am along.Thanks! |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-16 : 13:39:55
|
| this is not against the sql norm - you're still getting values from known tables/columns (the system tables). select TOP 1000 <blah blah> will limit your results to 1000 rows.Be One with the OptimizerTG |
 |
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-10-16 : 13:47:34
|
| The problem is that I don't know the blahblah to start with.Select top 1000 colname_I_dont_have from table_name_I_dont_havewhere col_2_I_dont_know = 'this is the value I have from the app UI'That is what I meant by against the norm, not the 1000 part. |
|
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2013-10-16 : 13:53:18
|
| Sorry, I thought the columns were [object_id], [name] and the table was [syscolumns]. So you mean you are constructing the query based on the results of the query you posted? If so then why can't you build the "top 1000" into your query string you are constructing?Perhaps you should explain the big picture objective maybe someone can suggest a better way.Be One with the OptimizerTG |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
James K
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-10-16 : 13:55:55
|
Are you searching for string values, or for numeric values? If you are searching for string values, there are scripts availabe on the web that can do that for you. For example:http://gallery.technet.microsoft.com/scriptcenter/c0c57332-8624-48c0-b4c3-5b31fe641c58http://beyondrelational.com/modules/2/blogs/78/posts/11138/how-to-search-a-string-value-in-all-columns-in-the-table-and-in-all-tables-in-the-database.aspxIf you are searching for an integer value, the same method could be adapted - you can cast the integer column to varchar and then search.If you are looking for floating point values, you would need to develop a query that looks through all numeric columns having values within an upper and lower bound.Edit:  |
|
|
|
Hommer
Aged Yak Warrior
808 Posts |
Posted - 2013-10-16 : 14:21:45
|
| Thanks everyone! I should be able to work from these samples. |
|
|
|
|
|
|
|
|
query tablename/columnname given value(s)