SSMS – get DDL of all views in a schema

I have a SQL Server database with several schemas, each of them contains several views.

I need to create the same views in other databases so I would like to get the DDL of all the views in a script, generated through SQL.

In Oracle it was quite easy, accessing ALL_VIEWS and referencing the QUERY column.

How can i do the same in SQL Server?

Thanks

EDIT

I know I can right click and get the script of an object in SSMS, I need to do this for 20/30 views, and don’t want to do it manually

I’m trying to avoid as much as possible to do manual work. I want to have a query which generates the scripts for all objects in one or more schemas.

This is because the schemas are evolving quickly, with new views being created. I need to reproduce them in several other databases and I’m trying to have a solid procedure for deploying

Answer

If you want to get the view definitions from a query, you can use sql_modules.

select   m.definition
from     sys.sql_modules   m
join     sys.objects       o  on o.object_id = m.object_id
join     sys.schemas       s  on s.schema_id = o.schema_id
where    o.type = 'V'
and      s.name in ('your', 'schemas', 'here')

Customize as desired to select the views you want.