Finding value 2

[alex12]

How would I find what columns in database have data 'Optic'?

Thanks.

 

[dbomrrsm]

See thread183-932088

[blue]DBomrrsm[/blue]

 

[donutman]

Very nice DBomrrsm.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[alex12]

Thanks guys. But this procedure works only with pubs database. How do I search in my database?

Thanks.

 

[dbomrrsm]

Thanks donutman

[blue]DBomrrsm[/blue]

 

[dbomrrsm]

Create the function in your database then run it against your database

[blue]DBomrrsm[/blue]

 

[andrea96]

Thanks dbomrrsm. I modified it to search for numeric values, and it seems to work great.

 

[alex12]

Obviously, I am missing a lot here.
How do I "Create the function in your database then run it against your database."???
How do I do it or is it some other way to make this procedere work for another db except pubs.

Thanks.

 

[dbomrrsm]

sorry posted under wrong thread:

Paste the create procedure code into a Query Analyser (QA)window and select the database you want to create it in from the drop down box at the top of the QA window.

Run the code - this will create the procedure in the selected database.

Then stay in QA and the same database then do

EXEC SearchAllTables 'opt'

This will find all columns that have letters opt in them. It will find opt at the beginning or at any position within the column field so it will return 'opt in' but will also find 'helicopter'.

Good luck.

[blue]DBomrrsm[/blue]

 

[alex12]

It is very frustrating, but it doesn't work in my db. The procedure works perfectly in pubs, but not in my db. What should I do??????

Thanks.

 

[dbomrrsm]

have a look in either QA or Enterprise manager to see if the SearchAllTables exists in the stored procedure list for your database - if not you need to create it in the DB using the guide above.

[blue]DBomrrsm[/blue]

 

[alex12]

Thank you a lot, DBomrrsm. Now it seems to be working, just taking really long time... Please, explain to me what the following means:

'SELECT ''' + @TableName + '.' + @ColumnName + ''', LEFT(' + @ColumnName + ', 3630)

What is the bunch of '''' means?

Thanks.

 

[dbomrrsm]

its how you create the SQL statement you really want to run the ' are used to separate text from variables so you can build a string with text and variables:

'SELECT ''' + @TableName + '.' + @ColumnName

might translate in the run SQL statement to:

SELECT MYTABLE.MYColumn etc

which is what needs to be passed to the server to run the query correctly.

[blue]DBomrrsm[/blue]

 

[alex12]

Thanks for the explanation, but why do we need three times ''' in 'select''' :

'SELECT ''' + @TableName + '.' + @ColumnName

Thanks again.

 

[dbomrrsm]

maybe overkill but it may also depend on what comes later

'Select '

may work just as well.

[blue]DBomrrsm[/blue]