Generates views on target database based on objects(tables or views) from a source database.
It has three possible scopes from which it takes source tables.
- Database-wide, generates views for all objects from the source database. Used when only database names are specified.
- Schema-wide, generates views for all objects from the source schema. Used when database and schema names are specified.
- Single-table, generates a single view for given source object. Used when all parameters are supplied.
Generated views are of Join type. They will contain all the source columns. The procedure does not configure caching nor partitioning for views it creates.
Arguments
- @source_database - Name of the database from which procedure will load source objects.
- @target_database - Name of the database in which procedure will generate views.
- @source_schema - Name of the schema from which procedure will load source objects.
- @target_schema - Name of the schema in which procedure will generate views.
- @source_object - Name of the objects which procedure will use to generate the view.
- @target_view - Name of the view that procedure will generate.
- @replace - Specifies whether the procedure will overwrite existing views if one with the same name already exists. The default value is false.
Example
Generates views for all objects in 'AdventureWorksDW2012_mssql' database in 'AdventureWorksDW2012_spark'. Procedure overwrites existing views if necessary.
EXEC sp_generate_views @source_database = 'AdventureWorksDW2012_mssql' , @target_database = 'AdventureWorksDW2012_spark' , @replace = 1 ;