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
SteveWell 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.
Andrew J. Kelly SQL MVP
"Steve" <stevesusenet@.yahoo.com> wrote in message
news:1139531021.868263.148350@.g47g2000cwa.googlegroups.com...
>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
>|||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:
> 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
A 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.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--
