0

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

Carlo Prato
  • 326
  • 4
  • 21
  • 1
    Does this answer your question? [Generate script in SQL Server Management Studio](https://stackoverflow.com/questions/9429902/generate-script-in-sql-server-management-studio) – Jayasurya Satheesh Sep 17 '21 at 10:41
  • i need to do this for like 20+ views. i dont want to do it manually on all of them – Carlo Prato Sep 17 '21 at 10:42
  • 2
    You don't have to, You'll see the option there to select all views irrespective of there Schema – Jayasurya Satheesh Sep 17 '21 at 10:43
  • 1
    i see, still in my DB i have several other views i dont need to generate the script for and have to manually deselect them in the process. i would like to do this via a query, if possible. thanks for the heads up tho – Carlo Prato Sep 17 '21 at 10:45
  • 1
    Consult your Source Control? – Thom A Sep 17 '21 at 10:46
  • @Larnu please elaborate, i am quite new to SSMS – Carlo Prato Sep 17 '21 at 10:49
  • *Ticking* 20 boxes, or unticking some others, isn't really a *hard* thing to do though. In the time since you have asked this question, said that "ticking 20 boxes is too hard" you could have ticked those 20 boxes some 30 times (if we assume a box a second). – Thom A Sep 17 '21 at 10:49
  • *"please elaborate,"* Source Control isn't something specific to SSMS, *any* "good" development work uses Source Controlled software... [What is Source Control?](https://www.google.co.uk/search?q=What+is+Source+Control%3F) – Thom A Sep 17 '21 at 10:50
  • 1
    @Larnu i specified inmy question i'm trying to avoid manual stuff. human error happens, i want to avoid it. thanks – Carlo Prato Sep 17 '21 at 10:50
  • @AaronBertrand Perhaps the SQL Server experts who did not recognise the reference to the `ALL_VIEWS` Oracle database dictionary view as being a reference to a programmatic solution should have declined to participate rather than having to to get their licks in about "good developers using source control" and claiming that the OP's question implied that they expected the computer to read their mind. I stand by my comment completely. – allmhuran Sep 17 '21 at 11:38
  • @AaronBertrand I was not hesitant to participate, I answered the OP's question immediately with an actual solution, as did you. Neither of us seemed to feel the need to get the knives out. But as I am clearly not close enough to the SO clique, I will bow out of this discussion at this point. – allmhuran Sep 17 '21 at 11:45
  • @allmhuran I think you're just over-reacting to Larnu's attempts at pulling out the real requirement, which multiple people (myself included, and the first commenter as well) were not clear on _until Larnu's questions prompted a clarifying edit_. And what I meant about you knowing to be hesitant meant for all questions not this one. Do you think anyone here is genuinely not trying to help? Do you think the general advice for everyone trying to help is to skip to the next question because some external detail about another platform is unfamiliar? Or should we seek clarification and keep trying? – Aaron Bertrand Sep 17 '21 at 11:48

3 Answers3

5

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.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
  • views follow a specific naming, plus i need all the views in the schemas. that's why i say in oracle i can just run a query and that's it. trying to do the same in SQL server. Thanks for the polite and useful answer tho – Carlo Prato Sep 17 '21 at 10:56
  • after the edit this answer is very useful. thanks a lot – Carlo Prato Sep 17 '21 at 11:02
  • 1
    @CarloPrato Same for the question - the edits (and comments) made your requirements more clear. – Aaron Bertrand Sep 17 '21 at 11:02
  • i'll try to be clearer next time for sure! – Carlo Prato Sep 17 '21 at 11:03
  • 1
    related to the source control: you guys are totally right and i'm aware of what you are saying. I work for a company though, and am not entitled to this decision, i do what i'm allowed to. not my project, not my rules. (+ not what i asked) – Carlo Prato Sep 17 '21 at 11:09
  • `(+ not what i asked)` - @Carlo, in a peer-to-peer environment where the peers know nothing about your situation, you're going to get questions seeking clarification, and sometimes advice you didn't ask for and maybe can't even take. This is people trying to help you, and it's a good thing. Please try to embrace it instead of complaining - it's one of the things that makes this site great. Sure, sometimes we offer things you already knew about, but sometimes we help solve _more_ than just the immediate question. – Aaron Bertrand Sep 17 '21 at 12:14
2

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.

allmhuran
  • 4,154
  • 1
  • 8
  • 27
  • 2
    @CarloPrato Aaron's answer is a bit less typing, this one is a bit more flexible if you want to add other object types at some point. Pick what works best for you! – allmhuran Sep 17 '21 at 11:00
1

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

select * from information_schema.views
Arvo
  • 10,349
  • 1
  • 31
  • 34
  • 1
    But see Aaron Bertrand's article on that [here](https://sqlblog.org/2011/11/03/the-case-against-information_schema-views) – allmhuran Sep 17 '21 at 11:02