August 27, 2002 at 8:31 am
I need to parse every column of every table in my database and replace any commas with spaces
August 27, 2002 at 9:30 am
Extend the procedure from Antares in your previous thread "parsing a column to replace all commas with spaces".
Add an cursor in the loop :
SELECT id FROM sysobjects where type='U'
--Returns the ID from all of the 'USER' tables
Use the ID it returns instead of the 'OBJECTID("yourtablenamehere") part...
August 27, 2002 at 12:31 pm
This is what I came up with:
DECLARE @maxcol int
DECLARE @posmark int
DECLARE @col VARCHAR(255)
declare @cname varchar(255)
declare tablecursor CURSOR for
SELECT id FROM sysobjects where type = 'U'
open tablecursor
fetch next from tablecursor into @cname
declare @tablename varchar(255)
declare tablename CURSOR for
SELECT name FROM sysobjects where type = 'U'
open tablename
fetch next from tablename into @tablename
SET @posmark = 0
SET @maxcol = (select max([colid]) from syscolumns WHERE [id] = @cname)
WHILE @posmark < @maxcol
BEGIN
SET @posmark = @posmark + 1
--If this is SQL 7 SP3 or lower you need to check for colid found in loop as there may not a sequential list.
SET @col = (SELECT [name] FROM syscolumns WHERE colid = @posmark AND [id] = @cname)
print @col
print 'this is before the statement '+ @cname
EXECUTE ('UPDATE'+ @tablename +'SET [' + @col + '] = REPLACE([' + @col + '], '','', '' '')')
print 'this is after the statement '+ @cname
fetch next from tablecursor into @cname
fetch next from tablename into @tablename
END
close tablecursor
close tablename
deallocate tablecursor
deallocate tablename
There are some "print" statements in there to verify that the variable is being passed which it is but I'm getting the following error at the EXECUTE statement:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
Any enlightenment would be appreciated.
August 27, 2002 at 12:57 pm
It's hard to read. Do you have spaces afteer UPDATE and before SET on the dynamic SQL statement?
K. Brian Kelley
http://www.truthsolutions.com/
Author: Start to Finish Guide to SQL Server Performance Monitoring
http://www.netimpress.com/shop/product.asp?ProductID=NI-SQL1
K. Brian Kelley
@kbriankelley
August 27, 2002 at 3:26 pm
Or to make things easiest and reuse code use the sp_MSForEachTable stored procedure, it will cursor thru all the user tables without you having to code for it. You just plug the code I provided last thread properly with ? in place of yourtablenamehere and you have to double single quotes up with the code to be used.
This is what you would have.
-----------Start Code----------
sp_MSForEachTable '
DECLARE @maxcol int
DECLARE @posmark int
DECLARE @col VARCHAR(255)
SET @posmark = 0
SET @maxcol = (select max([colid]) from syscolumns WHERE [id] = OBJECT_ID(''?''))
WHILE @posmark < @maxcol
BEGIN
SET @posmark = @posmark + 1
--If this is SQL 7 SP3 or lower you need to check for colid found in loop as there may not a sequential list.
SET @col = (SELECT [name] FROM syscolumns WHERE colid = @posmark AND [id] = OBJECT_ID(''?''))
EXECUTE (''UPDATE ? ['' + @col + ''] = REPLACE(['' + @col + ''], '''','''', '''' '''')'')
END'
-----------End Code----------
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply