---
source: Grok
title: "SQL VIEW - String Replacement"
conv_id: "e9f0a1b2-c3d4-e5f6-a7b8-c9d0e1f2a3b4"
share_url: "none"
created: "2025-09-10"
message_count: 8
category:
  - "Technical Support"
  - "Database Administration"
summary: "Technical debugging conversation: Daniel requests SQL VIEW strategy for batch string replacement in a database. Grok provides syntax guidance and architectural patterns for efficient updates without stored procedures. Off-framework technical work supporting underlying infrastructure."
keypoints:
  - "SQL VIEW approach vs. direct UPDATE statements for scalability"
  - "String replacement logic for templating systems and bulk data transformation"
summary_note: "This conversation contains no Initium framework content and is included for completeness of the conversation archive."
tags:
  - "sql"
  - "technical"
  - "database"
  - "string-replacement"
  - "devops"
monomyth_stage: ""
gameboard_position: "N/A"
truth_score: 75
entropy_score: "Medium"
tone:
  sentiment: "Transactional"
  emotions: []
  sycophancy_level: "None"
  notes: "Pure technical support; no framework engagement"
related_events: []
syndication_status:
  arnie: false
  chris: false
  redmond: false
  clint: false
  pilot: false
ma5_inheritance: "None"
pre_silicon: false
voice_print: false
codex_candidate: false
phase: "Infrastructure Support"
enriched_date: "2026-04-13"
---

---

# SQL VIEW - String Replacement

**[1] DANIEL:**

I am running a bunch of databases on SQL Server Management Studio 20.2. Each of the databases has a bunch of views that simply need a string replaced. Is there a way to update 9replace) a database VIEW without opening each VIEW and manually copying the query. Example:
 
SELECT CAST(IN9_WBC__LCS.dbo.DnnForge_NewsArticles_Article.ArticleID AS

---

**[3] DANIEL:**

I am running a bunch of databases on SQL Server Management Studio 20.2. Each of the databases has a bunch of views that simply need a string replaced. Is there a way to update 9replace) a database VIEW without opening each VIEW and manually copying the query. Example:
 
SELECT     CAST(IN9_WBC__LCS.dbo.DnnForge_NewsArticles_Article.ArticleID AS 
needs to be...
SELECT     CAST(RevivalPharms__LCS.dbo.DnnForge_NewsArticles_Article.ArticleID AS

So all I need to change is  'IN9_WBC' to 'RevivalPharms' and th only way i know how to do this is by viewing each VIEW in DESIGN mode, and using a REPLACE in NOTEPAD++ to do it, then pasting the new query back into the VIEW.

I have about 100 VIEWS to change.

---

**[4] GROK:**

To automate the replacement of 'IN9_WBC' with 'RevivalPharms' in the definitions of all views across your databases in SQL Server Management Studio (SSMS), you can use a T-SQL script that dynamically generates and executes `ALTER VIEW` statements. This avoids manually opening each view.

### Important Warnings
- **Backup first**: Before running any script, back up your databases (right-click the database in SSMS > Tasks > Back Up). This change is irreversible without a backup.
- **Test on a single view**: Modify the script to target one view initially (add a `WHERE` clause) to verify it works as expected.
- **Assumptions**: This assumes the string 'IN9_WBC' only appears where you want it replaced (e.g., in table references) and not in view names, comments, or strings that could break the view. If the views have dependencies (e.g., schema-bound), indexes, or triggers, test carefully.
- **Multiple databases**: You'll need to run the script in each database separately. In SSMS, select the target database from the dropdown or use `USE [DatabaseName];` at the top of the script.
- **Permissions**: You need ALTER VIEW permissions (typically db_owner or similar).
- **Limitations**: If any view definitions exceed ~4,000 characters or contain complex elements (e.g., multiple 'CREATE VIEW' strings in comments), manual review may be needed. Views with `WITH CHECK OPTION` or other clauses will be preserved as-is.

### The Script
Run this in a query window in SSMS, connected to the desired database:

```sql
DECLARE @view_name NVARCHAR(512);
DECLARE @definition NVARCHAR(MAX);
DECLARE @new_definition NVARCHAR(MAX);

DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
    SELECT 
        QUOTENAME(SCHEMA_NAME(v.schema_id)) + '.' + QUOTENAME(v.name) AS view_name,
        m.definition
    FROM sys.views v
    INNER JOIN sys.sql_modules m ON v.object_id = m.object_id;
    -- Optional: Add WHERE clause to filter, e.g., WHERE v.name LIKE 'SpecificView%' for testing

OPEN cur;
FETCH NEXT FROM cur INTO @view_name, @definition;

WHILE @@FETCH_STATUS = 0
BEGIN
    -- Replace the target string in the entire definition
    SET @new_definition = REPLACE(@definition, 'IN9_WBC', 'RevivalPharms');
    
    -- Change 'CREATE VIEW' to 'ALTER VIEW' (assumes standard definition format; replaces all occurrences but typically only one at the start)
    SET @new_definition = REPLACE(@new_definition, 'CREATE VIEW', 'ALTER VIEW');
    
    -- Execute the altered definition
    EXEC sp_executesql @new_definition;
    
    FETCH NEXT FROM cur INTO @view_name, @definition;
END

CLOSE cur;
DEALLOCATE cur;

PRINT 'All views updated successfully.';
```

### How It Works
1. It queries `sys.views` and `sys.sql_modules` to get all view names and their definitions.
2. For each view, it replaces 'IN9_WBC' with 'RevivalPharms' in the definition text.
3. It then replaces 'CREATE VIEW' with 'ALTER VIEW' to convert it to an alter statement.
4. It executes the modified statement dynamically using `sp_executesql`.
5. Repeat for each database by switching context in SSMS.

### If You Want to Generate Scripts Without Executing
If you'd rather review the changes first (recommended for 100+ views), modify the script to output the ALTER statements instead of executing them:

```sql
SELECT 
    REPLACE(
        REPLACE(m.definition, 'IN9_WBC', 'RevivalPharms'), 
        'CREATE VIEW', 'ALTER VIEW'
    ) AS AlterScript
FROM sys.views v
INNER JOIN sys.sql_modules m ON v.object_id = m.object_id;
-- Optional: WHERE v.name LIKE 'Pattern%'

```

Run this, copy the results from the "AlterScript" column, and paste/execute them in batches.

### Alternatives
- **SSMS Generate Scripts Wizard**: Right-click the database > Tasks > Generate Scripts > Select views > Advanced > Set "Script CREATE" to False and "Script ALTER" to True. This generates base scripts, but you'd still need to manually replace the string (e.g., in Notepad++ on the generated file).
- **Third-party tools**: If you have Redgate SQL Search or ApexSQL Refactor, they can do bulk searches/replaces in object definitions.
- **PowerShell**: If you're comfortable, use SMO (SQL Management Objects) in PowerShell to script and modify views across servers/databases.

If this doesn't work due to specific errors or view complexities, provide more details (e.g., error messages or a sample view definition).

---

**[5] DANIEL:**

OK. Got it, and the fist run I get:
 
Msg 208, Level 16, State 6, Procedure vw_BWN_Fractome_GeoCount, Line 1 [Batch Start Line 0]
Invalid object name 'dbo.vw_BWN_Fractome_GeoCount'.
 
But, this is old and a ghost or something because there is no object named 'dbo.vw_BWN_Fractome_GeoCount'

---

**[6] GROK:**

The error `Msg 208, Invalid object name 'dbo.vw_BWN_Fractome_GeoCount'` suggests that SQL Server is trying to alter a view that it thinks exists in the `dbo` schema, but it can't find it. This can happen if the view `vw_BWN_Fractome_GeoCount` is listed in `sys.views` or `sys.sql_modules` but is a "ghost" (e.g., metadata is corrupted, or the view was partially dropped). This is rare but can occur due to incomplete drops, schema mismatches, or database corruption.

### Steps to Resolve

1. **Verify the View Exists in Metadata**
   Run this query to check if `vw_BWN_Fractome_GeoCount` is listed in the system catalog:

   ```sql
   SELECT 
       QUOTENAME(SCHEMA_NAME(v.schema_id)) + '.' + QUOTENAME(v.name) AS view_name,
       v.object_id,
       m.definition
   FROM sys.views v
   LEFT JOIN sys.sql_modules m ON v.object_id = m.object_id
   WHERE v.name = 'vw_BWN_Fractome_GeoCount';
   ```

   - **If it returns a row**: The view exists in metadata but may be invalid. Note the `object_id` and definition (if any).
   - **If no rows**: The view isn't in `sys.views`, so the issue might be a lingering reference elsewhere.

2. **Check for Orphaned Metadata**
   If the view appears in `sys.views` but can't be accessed, it might be corrupted. Try dropping it explicitly:

   ```sql
   DROP VIEW IF EXISTS dbo.vw_BWN_Fractome_GeoCount;
   ```

   Then re-run the original script. If the `DROP` fails with the same error, the metadata is likely inconsistent.

3. **Skip Problematic Views in the Script**
   Modify the original script to exclude `vw_BWN_Fractome_GeoCount` and log any errors for review:

   ```sql
   DECLARE @view_name NVARCHAR(512);
   DECLARE @definition NVARCHAR(MAX);
   DECLARE @new_definition NVARCHAR(MAX);
   DECLARE @error_message NVARCHAR(MAX);

   DECLARE cur CURSOR LOCAL FAST_FORWARD FOR
       SELECT 
           QUOTENAME(SCHEMA_NAME(v.schema_id)) + '.' + QUOTENAME(v.name) AS view_name,
           m.definition
       FROM sys.views v
       INNER JOIN sys.sql_modules m ON v.object_id = m.object_id
       WHERE v.name <> 'vw_BWN_Fractome_GeoCount'; -- Exclude problematic view

   OPEN cur;
   FETCH NEXT FROM cur INTO @view_name, @definition;

   WHILE @@FETCH_STATUS = 0
   BEGIN
       BEGIN TRY
           -- Replace the target string
           SET @new_definition = REPLACE(@definition, 'IN9_WBC', 'RevivalPharms');
           SET @new_definition = REPLACE(@new_definition, 'CREATE VIEW', 'ALTER VIEW');
           
           -- Execute the altered definition
           EXEC sp_executesql @new_definition;
           
           PRINT 'Successfully updated view: ' + @view_name;
       END TRY
       BEGIN CATCH
           SET @error_message = ERROR_MESSAGE();
           PRINT 'Error updating view ' + @view_name + ': ' + @error_message;
       END CATCH
       
       FETCH NEXT FROM cur INTO @view_name, @definition;
   END

   CLOSE cur;
   DEALLOCATE cur;

   PRINT 'View update process completed.';
   ```

   This script:
   - Excludes `vw_BWN_Fractome_GeoCount`.
   - Adds error handling to log issues without stopping the process.
   - Prints success or failure for each view.

4. **Check for Database Corruption**
   If the view persists in metadata but can't be dropped, there might be minor corruption. Run:

   ```sql
   DBCC CHECKDB ('YourDatabaseName') WITH NO_INFOMSGS, ALL_ERRORMSGS;
   ```

   Replace `YourDatabaseName` with your database name. If corruption is found, you may need to repair (e.g., `DBCC CHECKDB` with `REPAIR_ALLOW_DATA_LOSS` on a backup copy) or restore from a clean backup.

5. **Alternative: Generate Scripts for Review**
   If errors persist, generate the `ALTER VIEW` scripts without executing them to inspect the problematic view's definition:

   ```sql
   SELECT 
       v.name AS ViewName,
       REPLACE(
           REPLACE(m.definition, 'IN9_WBC', 'RevivalPharms'), 
           'CREATE VIEW', 'ALTER VIEW'
       ) AS AlterScript
   FROM sys.views v
   INNER JOIN sys.sql_modules m ON v.object_id = m.object_id
   WHERE v.name = 'vw_BWN_Fractome_GeoCount';
   ```

   If this returns a row, check the `AlterScript` for issues (e.g., invalid syntax or references). If it’s empty or null, the view’s definition might be missing, confirming a metadata issue.

6. **If the Issue Persists Across Databases**
   Since you mentioned multiple databases, run this in each database to identify where `vw_BWN_Fractome_GeoCount` appears:

   ```sql
   IF EXISTS (
       SELECT 1 
       FROM sys.views 
       WHERE name = 'vw_BWN_Fractome_GeoCount'
   )
   BEGIN
       PRINT 'Found vw_BWN_Fractome_GeoCount in database: ' + DB_NAME();
   END
   ```

   You can script this across all databases using `sp_MSforeachdb` (use with caution):

   ```sql
   EXEC sp_MSforeachdb '
   USE [?];
   IF EXISTS (
       SELECT 1 
       FROM sys.views 
       WHERE name = ''vw_BWN_Fractome_GeoCount''
   )
   BEGIN
       PRINT ''Found vw_BWN_Fractome_GeoCount in database: [?]''; 
   END
   ';
   ```

7. **Manual Cleanup**
   If the view is truly a ghost and can’t be dropped normally, you might need to:
   - Restore a backup to a test database and compare metadata.
   - Contact SQL Server support or use a tool like SQL Server Data Tools (SSDT) to rebuild the database schema.

### Recommendations
- **Start with Step 1** to confirm the view’s metadata status.
- **Use the modified script** (Step 3) to process the remaining 99+ views while logging errors.
- **Backup before proceeding**, as always.
- If you see more errors or need help with a specific view’s definition, share the output of Step 1 or any new error messages, and I’ll refine the solution.

---

**[7] DANIEL:**

excellent help - thank you. Hours of work eliminated

---

**[8] GROK:**

Glad to hear that! You're welcome—happy to save you those hours. If you need any more assistance with this or anything else, just let me know!