Cursor to Scan through all Columns for data

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Cursor to Scan through all Columns for data

Author

Topic

yalnikim
Starting Member

1 Post

Posted - 2002-08-21 : 19:46:25

I was attempting to build a cursor which looks through all columns (either in the database or a slected group of tables) searching for particular data.

The initial intention of this cursor was to find dummy data such as spaces ' %' or dashes '-'.

The cursor I've used follows. What appears to be happening is that the line "where @column = '-'" is looking for columns of the name '-' (which there are none, of course!). It should be counting columns where data in @column = '-'.

Any help or advice will be appreciated.

Regards
Stu

----------------------------------------------------------------------

--declares variables.
declare @table varchar(32)
,@column varchar(32)
,@count varchar(10)
,@message varchar(45)

--selects table names.

declare c_tables cursor for
select name
from sysobjects
where xtype = 'u'
and name = 'PS_Columns' --comment this next line out for full user table search.
order by name

open c_tables
fetch next from c_tables into @table

while @@fetch_status = 0
begin

--selects column names for tables already selected.
declare c_columns cursor for
select b.name
from sysobjects a
,syscolumns b
where a.xtype = 'u'
and a.id = b.id
and a.name = @table
order by b.name

open c_columns
fetch next from c_columns into @column

while @@fetch_status = 0
begin

--selects count for columns where spaces are used at start of column.
declare c_counts cursor for
select count(*)
from ps_columns
where @column like ' %'
--add this next line in to remove columns where space isn't found.
--having count(*) > 0

open c_counts
fetch next from c_counts into @count

while @@fetch_status = 0
begin

--prints results of all three cursors
select @message = @table + ',' + @column + ',' + @count
print @message
fetch next from c_counts into @count

end

close c_counts
deallocate c_counts

fetch next from c_columns into @column

end

close c_columns
deallocate c_columns

fetch next from c_tables into @table

end

close c_tables
deallocate c_tables
----------------------------------------------------------------------

nr
SQLTeam MVY

12543 Posts

Posted - 2002-08-22 : 05:04:27

Not really sure what this is trying to do but something like this maybe?

declare @char char(1)
set @char = ' '
select tbls.name, c.name, (select count(*) from syscolumns c where tbls.id = c.id and left(c.name,1) = @char)
from (select distinct o.id, o.name from sysobjects o, syscolumns c
where o.xtype = 'U'
and o.id = c.id
and (left(o.name,1) = @char or left(c.name,1) = @char)
) as tbls
left outer join syscolumns c on tbls.id = c.id and left(c.name,1) = @char

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources