StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Output the results of this query to text. Don't forget to change the values of the variables! Take the result of this and run it.</p> <pre><code>SET NOCOUNT ON DECLARE @ColumnName VARCHAR(200) = 'ReplaceColumn' , @ReplaceIdStr VARCHAR(200) = 'ExampleReplaceIdStr' , @FindIdStr VARCHAR(200) = 'ExampleFindIdStr' PRINT 'BEGIN TRAN' PRINT 'SET XACT_ABORT ON' SELECT 'UPDATE ' + C.TABLE_NAME + CHAR(13) + 'SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ''' + @ReplaceIdStr + ''', ''' + @FindIdStr + ''')' + CHAR(13) + 'WHERE ' + @ColumnName + ' LIKE ''%' + @ReplaceIdStr + '%'' AND PROJID = ''1000''' + CHAR(13) FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.COLUMN_NAME = 'PROJID' PRINT 'COMMIT TRAN' SET NOCOUNT OFF </code></pre> <p>EDIT: Also, some reasoning: You said you want update all tables where they contain a column called <code>PROJID</code>. Your first query just says that if the table <code>@TableName</code> has a <code>PROJID</code> column, then update <code>@ColumnName</code> on it. But it doesn't guarantee that it has @ColumnName on it. The query I gave doesn't check that either, because I'm assuming that all tables that have <code>PROJID</code> also have <code>@ColumnName</code>. If that isn't the case, let me know and I can update the answer to check that. That you're getting an <code>Invalid Column Name</code> error points to <code>@ColumnName</code> not existing.</p> <p>Your query would have updated one table (<code>@TableName</code>) at most, whereas the one I gave you will update every table that has <code>PROJID</code>. I hope that's what your going for.</p> <p>EDIT 2: Here is a version that would run it all at once:</p> <pre><code>DECLARE @ColumnName VARCHAR(200) = 'Value' , @ReplaceIdStr VARCHAR(200) = 'ExampleReplaceIdStr' , @FindIdStr VARCHAR(200) = 'ExampleFindIdStr' DECLARE @Sql NVARCHAR(MAX) DECLARE UpdateCursor CURSOR FOR SELECT 'UPDATE ' + C.TABLE_NAME + ' SET ' + @ColumnName + ' = REPLACE(' + @ColumnName + ', ''' + @ReplaceIdStr + ''', ''' + @FindIdStr + ''')' + ' WHERE ' + @ColumnName + ' LIKE ''%' + @ReplaceIdStr + '%'' AND PROJID = ''1000''' FROM INFORMATION_SCHEMA.COLUMNS C WHERE C.COLUMN_NAME = 'PROJID' OPEN UpdateCursor FETCH NEXT FROM UpdateCursor INTO @Sql WHILE @@FETCH_STATUS = 0 BEGIN EXEC sp_executesql @Sql FETCH NEXT FROM UpdateCursor INTO @Sql END CLOSE UpdateCursor DEALLOCATE UpdateCursor </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload