Product:

Microsoft SQL server 2016

Issue:

When you try to script out the database schema, to be able to create a empty database on other server, we get a error when we select to much. If you have more than 42000 views in a database you will get issues with the wizard.

The wizard – Generate Scripts – from task menu on the database, will give error if the database contain to many objects.

Can also be a an issue if you use an older versions of SSMS or that you have encrypted SP in the database.

SSMS can crash when you try to generate the script with to many objects selected.

Solution:

Manually script out all the views in a table.

select schema_name(v.schema_id) as schema_name,

       v.name as view_name,

       v.create_date as created,

       v.modify_date as last_modified,

       m.definition

from sys.views v

join sys.sql_modules m 

     on m.object_id = v.object_id

 order by schema_name,

          view_name;

 

Then copy the result from the table column to a new query window for the new database.

Change that each line (command) end with ;

Then run the new SQL statements, to recreate the views in the new database.

More information:

https://www.gethynellis.com/2020/07/ssms-error-generating-scripts.html

https://dataedo.com/kb/query/sql-server/list-views-with-their-scripts

https://dwhanalytics.wordpress.com/tag/generate-create-script-for-all-views-in-sql-server-database/

https://sqlblog.org/2011/11/03/the-case-against-information_schema-views