Ask Daniel's CODEX

SQL VIEW

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.

1. 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.

1. 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.

1. 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.

1. 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.

1. 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 '; ``

1. 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!

Ask Daniel's CODEX