Need to Script Only Certain Views

SQL Server Administration
1

hi experts,
I need to change a hard-coded server name in about 100 views. Is there a way to loop thru the list of views and script each one with the output going to a single window? I think I've asked this before but I lost the thread. Thanks

2

Before you go down that road - and modify every view with yet another hard-coded server name, you really need to lookup synonyms. You can then setup your synonyms with a reference to the server name - use the synonym in your views/stored procedures/functions - and the next time this happens all you have to do is update the synonyms.

As for scripting - I would look at http://dbatools.io where you could use Find-DbaView to get the list of views and then Export-DbaScript to export the view definition. If you want separate files you would get the list and loop over that list calling export for each item found.

2 Likes
3 
SELECT N'GO
' + m.definition
FROM sys.sql_modules m
INNER JOIN sys.views v ON m.object_id=v.object_id
WHERE m.definition LIKE N'%linkedservername%'

Copy and paste the results into a new query window and make your changes. Grid mode for results, unless you change the max text length for Text Results. Also make sure to check the setting for "Retain CR/LF on copy or save" in Tools->Options->Query Results->SQL Server->Results to Grid.

In SSMS, you can also open Object Explorer, go to View:Object Explorer Details, and click the Views folder in Object Explorer. In the Details pane, you can Ctrl-Click on all the views you want to script out, then right-click and "Script..." them all to a new window.

The GO is necessary to separate each view creation in its own batch.

1 Like
4

Looks promising, @robert_volk. Thank you!

5

Yes, this is exactly what I need. Thanks!

6

@robert_volk This works fairly well with 2 exceptions I'll note here for the benefit of others who may use this technique to find views that reference a specific string.

  1. The script generates the CREATE VIEW statemente in a single line of code. In views where the developer added comments denoted by dashes -- (rather than /* */), any code after -- is treated as comments so code gets lost

  2. Some views that have been renamed are still scripted as CREATE VIEW the original view name. Not sure how this can happen. I ran DBCC UPDATE STATISTICS (DB name) then collected again but the non-existing view still shows up in the results. I'm really not sure why this is happening.

7

  1. That's why I mentioned the "Retain CR/LF" setting. It's also possible the views were created without the standard Windows CR/LF line feed combination. I had hundreds of views/procs at my last job with just LF (Unix) line feeds. It's possible they weren't copied correctly, or they're being interpreted incorrectly in your editor. Might want to consider pasting into NotePad++ and use the line feed conversion under the Edit menu. (I would convert all line feeds to Mac, then Unix, then Windows)

  2. I haven't seen that before and can't understand how that would happen. UPDATE STATISTICS has no effect on object names. Maybe run sp_refreshview on those views, but that's generally for fixing object/column references in the view definition.

8

@robert_volk I had adjusted that setting in one window but not the other. Now it works beautifully.
I have 180 views to modify - thanks so much, this will save me so much time!
The #2 issue is minor, I can work around those anomalies easily.
Thanks again!

9

@robert_volk I more question. As you know, this technique generates CRETA VIEW statements. Some of the views contian this at the end, causing ALTER VIEW to fail. Is there any way to prevent the extended property info from being scripted? Thanks
EXEC sys.sp_addextendedproperty @name=N'MS_DiagramPane1', @value=N'[0E232FF0-B466-11cf-A24F-00AA00A3EFFF, 1.00]
Begin DesignProperties =
Begin PaneConfigurations =
Begin PaneConfiguration = 0
NumPanes = 4
Configuration = "(H (1[5] 4[34] 2[12] 3) )"
End
Begin PaneConfiguration = 1
NumPanes = 3
Configuration = "(H (1 [50] 4 [25] 3))"
End
Begin PaneConfiguration = 2
NumPanes = 3
Configuration = "(H (1 [50] 2 [25] 3))"

10

I can't understand why that would be there, if you're using the query I posted. If you used "Generate Scripts" in SSMS, there's a setting under the "Advanced" to include extended properties, you can uncheck that.

Extended properties are kept in sys.extended_properties system table, and the sp_XXX_extended_property procedures can't be included in a view definition. That code is also part of a database diagram, if you don't really need it you can delete the entire diagram.

1 Like
11

All is good. It worked fine, we altered 180 views by using this technique. Thanks again!