Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin.
Search for scripts directly from SSMS, and instantly access any saved scripts in your
SSC briefcase from the favorites tab.
Download now (direct download link)
Thank this author by sharing:
By Mark Stockwell,
This procedure is called usp_mview, it will create and maintain a set of tables based on a view. In reporting application (Cognos, BusinessObjects) views are often created to simplify SQL or perform some business logic. Often the views are complex, resulting in poor performance. An indexed view can be created, but the schema binding and update frequency often presents problems. This procedure will create and update a set of materialized views similar to Oracle's dbms_mview package.
The procedure depends on the mview_config table, DDL in comments section of procedure. Create this table and add the views you wish to materialize. A primary key (single column only) should be specified. Note that the primary key specified need not be unique, only high enough cardinality to ensure that multiple rows with the same key return different CHECKSUM values. Procedure can be scheduled from any scheduling tool that can execute an SQL command.
Hope you find this useful-
what is difference b/w view and mview
create Procedure within Procedure
Store Procedure to update
This stored procedure saves time by creating a standard procedure to create or update a row.
Single config file for multiple SSIS packages