A while back a db expert I was talking to expressed the opinion that
cursors were overused, mostly by programmers who were thinking like
programmers instead of db people.

I have a task to do and I'm not sure I can do it without a cursor.
I'm curious to see if anyone can think of a way around it.

In a nutshell I want to query the database to find all tables that have
a particular field( "comment"), include only results where that field
has one of 20 substrings in the content, and then print it all out.

So I have
(substring1, substring2 substring3 ...substring20)

and I would like to get output like

table_name    comment     substring
----------------      -------------    -------------

If I can find another way than using cursors or brute force ( many
cut-n-pasted tsql statements )  I would be grateful.

Thanks in advance for any thoughts

Steve

Well this sounds like a one of a kind request.  If it isn't you have some
serious design flaws. A cursor is useful for something like that where you
need to navigate multiple objects dynamically.  What you should not use a
cursor for are things that can get the results via a SET based approach. For
instance you would not create a cursor to navigate each row of the table to
search for yoru string. You would do that in a SET based fashion.

Show quoteHide quote

Hello, Steve

Let's take it one step at a time. First, we need a list of tables that
have a column named "comment":

SELECT o.name FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'

Then, for each of the above tables, we would want to do something like
this:

SELECT 'Some table' as table_name, comment, sub_string
FROM [Some table] t INNER JOIN (
    SELECT 'substring1' sub_string
    UNION SELECT 'substring2'
    UNION SELECT 'substring3'
    /* ... */
) x ON t.comment LIKE '%'+sub_string+'%'

Instead of writing the substrings like above, we can use a temporary
table to store them:

CREATE TABLE #substrings (sub_string varchar(50) PRIMARY KEY)
INSERT INTO #substrings VALUES ('substring1')
INSERT INTO #substrings VALUES ('substring2')
INSERT INTO #substrings VALUES ('substring3')
/* ... */

Now we can generate the SELECT-s for all the tables, using the
following query:

SELECT '
SELECT '''+o.name+''' as table_name, comment, sub_string
FROM ['+o.name+'] t INNER JOIN #substrings x
ON t.comment LIKE ''%''+sub_string+''%''
' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'

After executing the above query, copy the results into another query
window and execute them.

We can stop here, but let's make it more automatic: we can use a WHILE
loop to execute each statement (yes, this resembles a cursor very
much...) that puts the results in a temporary table:

CREATE TABLE #results (table_name sysname, comment ntext, sub_string
varchar(50))

create table #statements (id int identity primary key, sql
nvarchar(4000))
insert into #statements
SELECT '
INSERT INTO #results SELECT '''+o.name+''' as table_name, comment,
sub_string
FROM ['+o.name+'] t INNER JOIN #substrings x
ON t.comment LIKE ''%''+sub_string+''%''
' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype='U' AND c.name='comment'

declare @id int, @sql nvarchar(4000)
set @id=0
while 1=1 begin
    set @id=(select min(id) from #statements where id>@id)
    if @id is null break
    select @sql=sql from #statements where id=@id
    exec (@sql)
end
drop table #statements

select * from #results
drop table #results

If you don't like the WHILE loop, we can write something shorter, using
an undocumented stored procedure: sp_execresultset (of course, using
undocumented features is not recommended, but this is a one-time job,
isn't it?)

CREATE TABLE #results (table_name sysname, comment ntext, sub_string
varchar(50))

EXEC sp_execresultset '
SELECT ''
INSERT INTO #results SELECT ''''''+o.name+'''''' as table_name,
comment, sub_string
FROM [''+o.name+''] t INNER JOIN #substrings x
ON t.comment LIKE ''''%''''+sub_string+''''%''''
'' FROM syscolumns c INNER JOIN sysobjects o ON c.id=o.id
WHERE o.xtype=''U'' AND c.name=''comment''
'
select * from #results
drop table #results

Razvan

Thanks for the code Razvan!

Steve wrote:
Show quoteHide quoteA strange thing to want to do at runtime. Why don't you know what
columns exist in your database? Sensible data manipulation requirements
against a known, static database schema can indeed be done 99.99% of
the time without using cursors.

Problem updating two tables in a transaction.
Does dynamic SQL allow table variables?
Insert Trigger
DBCC SHOWCONTIG question
Query assistance or advice
Problem with Cursor and Union in select
Optimizing query with UDF and table vars and IN
SELECT problem in stored procedure
Moving indexes from a filegroup to another
Misunderstood INSERT when using CASE on ORDER BY