This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
Hi,
I'm wondering if there is a simple way to script views definitions by some pattern, e.g. views in a particular schema and named using 'ndb' in the name?
Thanks in advance.
I'm not sure I follow exactly what you want but it sounds like you want to capture the view definition for a subset of views. While you can get this from sys.sql_modules I personally would find it easier to just get the views from sys.views you care about and then reach into sys.sql_modules for the definition. I'd probably do something like this.
SELECT v.object_id, v.name, s.name, m.definition
FROM sys.views v
JOIN sys.schemas s ON v.schema_id = s.schema_id
JOIN sys.sql_modules m ON v.object_id = m.object_id
WHERE s.name = 'stg' AND v.name like '%ndb%'
Given the above query you can do whatever you want with the definition. I'm unclear what your goal is. It seems like you're trying to generate dynamic SQL which takes the existing view definition, replaces CREATE VIEW with CREATE OR ALTER VIEW and then injecting some GO commands. Seems like your SELECT logic for that is correct.
I wanted to script those views into one .sql file. Thanks, Michael.
Is this a one time thing or something that needs to be automated? SSMS already supports exporting definitions to a script file under Tasks \ Generate Scripts. You can then select the object(s) you want and it'll a single script containing everything you've asked for.
If you're trying to do this in T-SQL then I think you're going to end up having to use the xp_cmdshell(?) procedure to do that as SQL scripts aren't going to be writing content to a file directly. But honestly I've never tried to have SQL write to a text file directly. I just use SSMS.
I used the following code which seems to work:
DECLARE @SQL NVARCHAR(MAX)
SET NOCOUNT ON
SELECT @SQL = STRING_AGG(REPLACE(definition, ' VIEW', ' OR ALTER VIEW'), CHAR(13)+CHAR(10) + 'GO' + CHAR(13) + CHAR(10)) FROM sys.sql_modules m
WHERE OBJECT_NAME(m.object_id) LIKE 'view_stg_ndb%'
select @SQL
I needed to output results to text and set it to big number in the Tools/Options.