Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Generate Synonyms Creation Script for creating a "shell" database
DECLARE
@LinkedServer NVARCHAR(300),
@DBInLinkedServer NVARCHAR(300)
SET @LinkedServer = '111.222.111.222'
SET @DBInLinkedServer = 'SomeOtherDatabase'
-- Generate create script for any non-system schemas:
SELECT CreateStatement = N'CREATE SCHEMA ' + QUOTENAME(name) + N';', DropStatement = N'DROP SCHEMA ' + QUOTENAME(name) + N';'
FROM sys.schemas
WHERE name NOT IN ('dbo','guest','INFORMATION_SCHEMA','sys','db_owner','db_accessadmin','db_securityadmin','db_ddladmin','db_backupoperator','db_datareader','db_datawriter','db_denydatareader','db_denydatawriter')
UNION ALL
-- Generate create script for object synonyms:
SELECT DISTINCT
CASE WHEN isfunction = 0 THEN 'CREATE SYNONYM ' + objname + ' FOR ' + QUOTENAME(@LinkedServer) + N'.' + QUOTENAME(@DBInLinkedServer) + N'.' + objname + N';'
ELSE N'GO
' + def + N'
GO' END
, CASE WHEN isfunction = 0 THEN 'DROP SYNONYM ' + objname ELSE 'DROP FUNCTION ' + objname END + N';'
FROM
(
SELECT objname = QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(name)
, isfunction = 0
, def = NULL
FROM sys.tables
WHERE is_ms_shipped = 0 AND name <> 'sysdiagrams'
UNION ALL
-- the following would include functions, procedures and views
SELECT QUOTENAME(OBJECT_SCHEMA_NAME(object_id)) + '.' + QUOTENAME(OBJECT_NAME(object_id))
, CONVERT(int, OBJECTPROPERTY(object_id, 'IsScalarFunction'))
+ CONVERT(int, OBJECTPROPERTY(object_id, 'IsInlineFunction'))
+ CONVERT(int, OBJECTPROPERTY(object_id, 'IsTableFunction'))
, def = definition
FROM sys.sql_modules
WHERE OBJECT_NAME(object_id) NOT IN (
'sp_helpdiagrams'
, 'sp_helpdiagramdefinition'
, 'sp_creatediagram'
,'sp_renamediagram'
,'sp_alterdiagram'
,'sp_dropdiagram'
,'sp_upgraddiagrams'
,'fn_diagramobjects' )
) AS d
@EitanBlumin

This comment has been minimized.

Copy link
Owner Author

EitanBlumin commented Sep 3, 2018

Important note:
Preserve user permissions via linked servers

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.