Synonym creation for database mirroring via linked server (does not work for function-objects)

LinkedServerSynonyms.sql

-- 0a) Create Linked server 
-- 0b) Enable SPs for linked server

-- EXEC sp_serveroption @server='CORDB2008R2', @optname='rpc', @optvalue='true'
-- EXEC sp_serveroption @server='CORDB2008R2', @optname='rpc out', @optvalue='true'

-- 1 Create database 
-- 2 Create types 
-- 3 Create table-synonyms 
-- 4 Create view-synonyms 
-- 5 Create SP-synonyms 
-- 6 Create functions (scalar, table-valued, CLR) 

DECLARE @linkedServerName nvarchar(255)
SET @linkedServerName = 'CORDB2008R2'



SELECT cmd 
FROM 
(
	SELECT 
		'IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = ''' + REPLACE(TABLE_NAME, '''', '''''') + ''' AND SCHEMA_NAME(schema_id) = ''' + REPLACE(TABLE_SCHEMA, '''', '''''') + ''' ) 
		CREATE SYNONYM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' FOR ' + @linkedServerName + '.' + QUOTENAME(TABLE_CATALOG) + '.' +  QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '; '  AS cmd 
		,table_schema 
		,table_name 
		,1 AS precedence 
	FROM INFORMATION_SCHEMA.TABLES 
	WHERE TABLE_TYPE = 'BASE TABLE' 
	AND table_name NOT IN ('sysdiagrams', 'dtproperties', 'VWSsessions')


	UNION 


	SELECT 
		'IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = ''' + REPLACE(TABLE_NAME, '''', '''''') + ''') 
		CREATE SYNONYM ' + QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + ' FOR ' + @linkedServerName + '.' + QUOTENAME(TABLE_CATALOG) + '.' +  QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) + '; ' 
		,table_schema 
		,table_name 
		,2 AS precedence 
	FROM INFORMATION_SCHEMA.VIEWS 
	
	
	UNION 
	
	
	SELECT 
		'IF NOT EXISTS (SELECT * FROM sys.synonyms WHERE name = ''' + REPLACE(SPECIFIC_NAME, '''', '''''') + ''') 
		CREATE SYNONYM ' + QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME) + ' FOR ' + @linkedServerName + '.' + QUOTENAME(SPECIFIC_CATALOG) + '.' +  QUOTENAME(SPECIFIC_SCHEMA) + '.' + QUOTENAME(SPECIFIC_NAME) + '; ' 
		,SPECIFIC_SCHEMA 
		,SPECIFIC_NAME 
		,3 AS precedence 
	FROM INFORMATION_SCHEMA.routines 
) AS tempT 
ORDER BY precedence, table_schema, table_name 



-- Note: 
-- Four-part names for CLR, scalar & table-valued functions are not supported.
--CREATE SYNONYM T_Benutzer FOR CORDB2008R2.RoomPlanning.dbo.T_Benutzer 

SELECT 
	 schem.name AS schema_name 
	,syn.* 
FROM sys.synonyms AS syn 
LEFT JOIN sys.schemas AS schem ON schem.schema_id = syn.schema_id 

WHERE syn.name = 'T_ZO_AP_Gebaeude_DWG'
AND schem.name = 'dbo'


SELECT 
	 SCHEMA_NAME(syn.schema_id) AS schema_name 
	,syn.* 
FROM sys.synonyms AS syn 
WHERE syn.name = 'T_ZO_AP_Gebaeude_DWG'
AND SCHEMA_NAME(syn.schema_id) = 'dbo' 


/*
CREATE TYPE [dbo].[GeschossIdentifierTableType] AS TABLE(
	[GeschossIdentifierValue] [varchar](76) NULL
)
;


CREATE TYPE [dbo].[GuidTableType] AS TABLE(
	[GuidValue] [uniqueidentifier] NULL
)
;


CREATE TYPE [dbo].[IdTableType] AS TABLE(
	[IdValue] [int] NULL
);

GO
*/