![]() ![]() ![]() Return - 1 END END set sql = 'select schema_id from. Exiting.', 16, 1, targetSchema, targetdb) Raiserror( 'Error: Target schema %s not found in the %s database. If not exists ( select * from targetschema_id) sys.schemas where name = ''' + targetschema + '''' insert into targetschema_id exec ( sql ) Return - 1 end set sql = 'select schema_id from. Raiserror( 'Error: The target database %s not found or not ONLINE. If (db_id( targetDB ) is null or databasepropertyex( targetDB, 'Status' ) != 'ONLINE' ) ![]() Return - 1 end - If the SP will try to create the sysnonyms - Therefore the target db and the target schema must already exist Raiserror( 'Error: Source database %s not found or not ONLINE. If db_id( sourcedb) is null or databasepropertyex( sourcedb, 'Status' ) != 'ONLINE' begin Raiserror( 'Error: Source and taget databases + schemas cannot be the same. ![]() If targetdb = sourcedb and sourceSchema = targetSchema Raiserror( 'Error: Source and target database names are required and cannot be null. If targetdb is null or sourcedb is null begin Select sourcedb SourceDB, sourceSchema SourceSchema, targetdb TargetDB, targetSchema TargetSchema defaults to dbo targetSchema sysname = 'dbo', - defaults to dbo PrintOnly bit = 1 - whether to also create the synonums or only print the SQL statements to create themĪs set nocount on declare sql varchar ( 4000 ), def nvarchar( max )ĭeclare sourceschema_id table (schema_id int )ĭeclare targetschema_id table (schema_id int ) PRINT 'Modifying stored procedure spGenerateSynonyms.' GO alter PROCEDURE sourceDB sysname, - aka base database where the objecgs are located targetDB sysname, - database where the synonyms will be created object_type varchar ( 100 ) = 'Tables', - type of objects to create synonyms sourceSchema sysname = 'dbo', - base schema name. PRINT 'Stored procedure spGenerateSynonyms already exists.' PRINT 'Creating stored procedure spGenerateSynonyms' exec ( 'create PROCEDURE spGenerateSynonyms as select '''' empty_string' ) If object_id( 'spGenerateSynonyms' ) is null BEGIN Use AdminDBA - Change this to the whatever database you are using to store DBA objects go Examples: exec spGenerateSynonyms = 'EnterpriseSub', = 'Enterprise', = 1 exec spGenerateSynonyms = 'AdminDBA', = 'Enterprise', = 1 exec spGenerateSynonyms = 'AdminDBA', = 'Enterprise', = 'appuser1', = 1 exec spGenerateSynonyms = 'AdminDBA', = 'AdminDBA', = 0 exec spGenerateSynonyms = 'ProductUserMaster' - INVALID exec spGenerateSynonyms = 'AdminDBA', = 'AdminDBA' - INVALID, SOURCE AND TARGET DATABASES + SCHEMAS CANNOT BE SAME */ set nocount on go Synonyms on other types of objects (views, stored procedures etc.) are also supported by sql server. Caveats: For now this procedure only generates scripts for tables. exec spGenerateSynonyms = 'AdminDBA', = 'AdminDBA', 'dbo', = 1 /* Description: Generates script to create synonyms in target db (usually, a blank db) for all tables that exist in source db (usually a subscriber db) Requirements: Source database and source schema (defaults to dbo so it can be omitted) must exist If parameter is set to 0, meaning the script would also run the create synonyms statements then the target database and schema must exist too. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |