CODEWITHSUNDEEP
sqlsql-server
Carlo Prato
Carlo Prato

Reputation: 313

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

Upvotes: 0

Views: 3900

Answers (3)

anon
anon

Reputation:

With the Views node highlighted in Object Explorer, open Object Explorer Details (F8 or Shift+F8 if I recall correctly).

This will open a window with all the views in a list, and you highlight the views you want and right-click > script. Here you can sort by name, filter by schema (probably what you mean by "owner"), etc.

I'm not sure how you can possibly select 20 or 30 views in any scenario without introducing the possibility of human error.

You can write a script that does something similar, with Results to Text, e.g.

SET NOCOUNT ON;

SELECT 'GO' + OBJECT_DEFINITION(object_id)
  FROM sys.views
  WHERE schema_id = SCHEMA_ID(N'schema_name');

The problem is Management Studio has crippling limits to text output, even after customizing Tools > Options > Query Results > SQL Server > Results to Text and setting that to the max (8,192). So if any of your views are longer than 8,192 characters, you'll need to work around that somehow.

But really, Larnu is right. Your views should be in source control, and that should be your source for deployment, not some manual extraction from the database.

Upvotes: 5

Arvo
Arvo

Reputation: 10570

You can get views, schemas and definitions by quite standard way:

select * from information_schema.views

Upvotes: 1

allmhuran
allmhuran

Reputation: 4454

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.

Upvotes: 2

Related Questions