How to script views by a pattern

Question

How to script views by a pattern

Naomi Nosonovsky 8,906

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.

0 comments

Answer accepted by question author

Michael Taylor 61,221

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.

Naomi Nosonovsky 8,906 Reputation points

2025-05-09T23:42:19.95+00:00

I wanted to script those views into one .sql file. Thanks, Michael.
Michael Taylor 61,221 Reputation points

2025-05-09T23:53:16.44+00:00

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.
Naomi Nosonovsky 8,906 Reputation points

2025-05-11T02:26:03.73+00:00

I tried to use Tasks/Generate scripts but it didn't allow to select pattern or schema and there are too many views in that database. This is sort of one time thing.
Michael Taylor 61,221 Reputation points

2025-05-11T19:33:08.9633333+00:00

Too many views to filter through in the UX or SSMS couldn't generate a script that big?

Personally I would just have SSMS generate a single script file that contained all the views. Save that into a file. If all the views you want are in a specific schema then presumably they would group together. You could then just jump to that place in the file, remove everything before and after the views you don't care about.

If that wasn't an option then I'll use Powershell to scan the file for CREATE VIEW <schema>. Anything that doesn't match I'd remove from the file. But it would probably take longer to write the script then it would to do it manually.

If you really want to solve it using T-SQL then the earlier given script will properly get the views you care about. You can then write the view definitions to a file. As I mentioned that isn't something SQL is really set up to do although the xp_cmshell(?) can do it. Here's a link to someone's sproc that supposedly does it as well. I've never used it myself so I cannot answer to how well it works. Nevertheless you could use a CURSOR (if necessary) to enumerate the results of your query and for each row, append to the file. Since this is a one-off thing performance isn't that big of a deal.

1 additional answer

Your answer

Naomi Nosonovsky 8,906 Reputation points

2025-05-09T23:42:19.95+00:00

I wanted to script those views into one .sql file. Thanks, Michael.
Michael Taylor 61,221 Reputation points

2025-05-09T23:53:16.44+00:00

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.
Naomi Nosonovsky 8,906 Reputation points

2025-05-11T02:26:03.73+00:00

I tried to use Tasks/Generate scripts but it didn't allow to select pattern or schema and there are too many views in that database. This is sort of one time thing.
Michael Taylor 61,221 Reputation points

2025-05-11T19:33:08.9633333+00:00

Too many views to filter through in the UX or SSMS couldn't generate a script that big?

Personally I would just have SSMS generate a single script file that contained all the views. Save that into a file. If all the views you want are in a specific schema then presumably they would group together. You could then just jump to that place in the file, remove everything before and after the views you don't care about.

If that wasn't an option then I'll use Powershell to scan the file for CREATE VIEW <schema>. Anything that doesn't match I'd remove from the file. But it would probably take longer to write the script then it would to do it manually.

If you really want to solve it using T-SQL then the earlier given script will properly get the views you care about. You can then write the view definitions to a file. As I mentioned that isn't something SQL is really set up to do although the xp_cmshell(?) can do it. Here's a link to someone's sproc that supposedly does it as well. I've never used it myself so I cannot answer to how well it works. Nevertheless you could use a CURSOR (if necessary) to enumerate the results of your query and for each row, append to the file. Since this is a one-off thing performance isn't that big of a deal.

Answer 1

Naomi Nosonovsky 8,906

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.

0 comments

Share via

How to script views by a pattern

1 additional answer

Your answer