Listing 3: Creating Multiple Synonyms by Using a Stored Procedure

USE master;
GO
IF OBJECT_ID('dbo.sp_create_synonyms', 'P') IS NOT NULL
  DROP PROC dbo.sp_create_synonyms;
GO
CREATE PROC dbo.sp_create_synonyms @object NVARCHAR(392)
AS

IF OBJECT_ID(@object) IS NULL
BEGIN
  RAISERROR('Object does not exist.', 16, 1);
  RETURN;
END

DECLARE
  @db_name     AS sysname,
  @schema_name AS sysname,
  @object_name AS sysname,
  @sql         AS NVARCHAR(MAX);

SELECT
  @db_name     = PARSENAME(@object, 3),
  @schema_name = PARSENAME(@object, 2),
  @object_name = PARSENAME(@object, 1);

IF @db_name IS NULL OR @schema_name IS NULL OR @object_name IS NULL
BEGIN
  RAISERROR(
    '@object_name must be a fully qualified object name 
(database_name.schema_name.object_name).',
    16, 1);
  RETURN;
END

SET @sql =
N'IF N''?'' <> N''' + @db_name + '''
  BEGIN
    USE [?];
    IF OBJECT_ID('''
         + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@object_name)
         + N''', N''SN'') IS NOT NULL
      DROP SYNONYM '
         + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@object_name) + N';
    IF OBJECT_ID('''
         + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@object_name)
         + N''') IS NULL
      CREATE SYNONYM '
         + QUOTENAME(@schema_name) + N'.' + QUOTENAME(@object_name)
         + N' FOR ' + @object + N';
  END';

EXEC sp_MSforeachdb @sql;
GO






3