Synonyms or creating a Database table links that point to other database tables
CREATE PROCEDURE CreateSynonymsForTargetDatabase (
@databaseName sysname
)
AS BEGIN
DECLARE @TSQL nvarchar(max) = N''
DECLARE @rn char(2),
@SchemaName sysname;
SET @rn = char(13) + char(10)
CREATE TABLE #DBSynonym(
[Schema] sysname NOT NULL,
[Table] sysname NOT NULL
)
SET @TSQL = N'
INSERT INTO #DBSynonym ([Schema], [Table])
SELECT Schemas.name, Tables.name
FROM [' + @databaseName + '].sys.tables
INNER JOIN [' + @databaseName + '].sys.schemas on tables.schema_id = schemas.schema_id
'
EXEC (@TSQL)
SET @TSQL = N''
DECLARE MissingSchemasCursor CURSOR
READ_ONLY
FOR
SELECT newSchemas.[Schema]
FROM #DBSynonym newSchemas
LEFT JOIN sys.schemas on newSchemas.[Schema] = schemas.name
WHERE schemas.schema_id is null
GROUP BY newSchemas.[Schema]
OPEN MissingSchemasCursor
FETCH NEXT FROM MissingSchemasCursor INTO @SchemaName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @TSQL = N'CREATE SCHEMA ' + QUOTENAME(@SchemaName) + N';'
EXEC sp_executesql @TSQL
END
FETCH NEXT FROM MissingSchemasCursor INTO @SchemaName
END
CLOSE MissingSchemasCursor
DEALLOCATE MissingSchemasCursor
SELECT @TSQL = @TSQL +
N'
GO
CREATE SCHEMA ' + QUOTENAME([Schema]) + N';'
FROM #DBSynonym newSchemas
LEFT JOIN sys.schemas on newSchemas.[Schema] = schemas.name
WHERE schemas.schema_id is null
GROUP BY newSchemas.[Schema]
PRINT 'CREATE SCHEMAS : ' + ISNULL(@TSQL,'')
EXEC sp_executesql @TSQL
SET @TSQL = N''
SELECT @TSQL = @TSQL +
N'
CREATE SYNONYM ' + QUOTENAME([Schema]) + N'.' + QUOTENAME([Table]) + N'
FOR ' + QUOTENAME(@databaseName) + N'.' + QUOTENAME([Schema]) + N'.' + QUOTENAME([Table]) + N';'
FROM #DBSynonym
EXEC sp_executesql @TSQL
SET @TSQL = N''
END
GO
--Create the empty shell database.
--Go into the Source database.
--Create this proc.
--Run it from the source db,
--passing it the parameter of the database you just created
EXEC CreateSynonymsForTargetDatabase 'IDXRAD'
Connecting to localdb
SqlLocalDB.exe info MAPToolkit
Which should return details about who owns the instance, when it was last started, and its state (running, for instance):
LocalDB instance "MAPToolkit" started.
C:\Users\AUser>SqlLocalDb.exe info MapToolkit
Name: MAPToolkit
Version: 11.0.2100.60
Shared name:
Owner: MyDomain\AUser
Auto-create: No
State: Running
Last start time: 4/23/2013 9:04:31 AM
Instance pipe name: np:\\.\pipe\LOCALDB#SH2C50D1\tsql\query
Source: http://www.ssistalk.com/2013/04/23/map-toolkit-connecting-to-localdb/