Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [synonyms]

51 questions
6
votes
1 answer

Checking cross-database synonym validity

Background: I have multiple source system products, each in their own database which have very limited dependencies on each other, and which are fairly modular and also kind of optional. They are potentially at different version levels in their own…
Cade Roux
  • 6,684
  • 1
  • 33
  • 55
6
votes
2 answers

How to create a synonym for a column in SQL Server?

The following is not working. I want to create a synonym on a column in SQL Server 2017. How would I go about doing this? CREATE SYNONYM dbo.[ProductTest] for [dbo].[Product].[ProductId] GO select ProductTest from dbo.Product
user168914
5
votes
3 answers

How to fetch the columns of the synonyms for linked servers in SQL server

I am using two servers local (SQL Server running on my system) and remote (SQL Server running on another system). I have created synonyms in my local server for a table which is located on the remote server. Synonym used- CREATE SYNONYM [dbo].[test]…
Subaashini Pushparaju
  • 53
  • 1
  • 5
4
votes
2 answers

How to List SYNONYMS that no longer point to a valid object?

I have a table (OBJECTS_TO_UPDATE) with several objects I need to update, some are SYNONYMs for objects that no longer exist. I want to create a query to list them. But I am stuck. My table OBJECTS_TO_UPDATE has these…
Dzyann
  • 355
  • 3
  • 10
  • 26
4
votes
2 answers

Synonyms and transactions

I'm working with an application that uses SQL synonyms to switch between tables on local and remote databases without duplicating code. BEGIN TRANSACTION if (@isRemote = 1) BEGIN CREATE SYNONYM SY_SUBJECTS FOR…
RaoulRubin
  • 979
  • 2
  • 10
  • 12
4
votes
1 answer

Does restoring a database break external synonyms targeting objects in that database?

If one has a database where synonyms in another database refer to objects in that database, does restoring a backup into that database invalidate the synonyms? To be specific, imagine this situation: Database Synonym_Targ on a SQL 2008 R2 server…
ConcernedOfTunbridgeWells
  • 17,081
  • 2
  • 59
  • 71
4
votes
2 answers

Is Synonyms to tables cost effective within SQL Server 2016

For one of our new projects we are thinking of using lots of Synonyms to tables which refer to another Database and we are fully aware on what it does and how we are going to use them. But what we don't know is, are there any hidden overheads on…
Pallav Hathi
  • 51
  • 3
4
votes
2 answers

Oracle 12c - Create materialized view results in ORA-00942

I have a view that I can access through a database link as such: SQL> select count(*) from REMOTE_SCHEMA.REMOTE_VIEW@REMOTE_DB; COUNT(*) -------- 110 I can create and successfully query the remote view through a synonym: SQL> create synonym…
user109164
  • 61
  • 1
  • 4
3
votes
0 answers

Postgres - want a database to have two names

One of my Postgres databases is named entirely in upper-case, and a new bit of software needs to see it in lower-case. I've tried renaming it, but unfortunately that breaks the huge multitude of things that already rely on it being in upper-case (as…
user25730
  • 131
  • 2
3
votes
1 answer

Any way to make stored procedure code dynamically reference other databases, without dynamic sql?

I've a situation where I have two databases: ABCData ABCUsers One of the stored procedures in ABCData needs to perform an insert to a table in ABCUsers. This could just be hard coded: INSERT ABCUsers.dbo.Table1 VALUES('a'); But the next problem…
Caius Jard
  • 344
  • 3
  • 11
3
votes
1 answer

Changing database name based on project environment

I have three environments for our BI project (DEV, QA, PROD) and two databases in each environment, source(EDB) and target(EDM) DEV environment: Source = EDB_DEV Target = EDM_DEV QA environment: Source = EDB_QA Target = EDM_QA PROD environment:…
DK208
  • 31
  • 1
2
votes
1 answer

ORA-00942: table or view does not exist while creating synonym

We are using oracle 11g, the db structure goes like this: We have users CST and FAS. CST contains a table ST_CAT2, a public synonym ST_CAT has been created for the table CST.ST_CAT2 as a SYS user. ST_ROLE is a role which has given SELECT UPDATE…
Vishwanath gowda k
  • 131
  • 1
  • 1
  • 6
2
votes
3 answers

How to separate Stored Procedures (i.e. all the business logic) from Client Database so that it only contains client data?

Scenario: Our Application Database (in SQL Server 2012) contains entire business logic in the Stored Procedures (SPs). Everytime we have to publish the DB to the client, it unnecessarily results in copying the SPs to the Client DB. Problem: All the…
Ish Goel
  • 131
  • 3
2
votes
1 answer

Getting the database from synonym base object

Risking asking something idiotic, but not easily got by Google-fu, but how to query the database id (and other information) from the base object of an synonym? The synonym in question is an remote one (pointing to a table on another DB on the same…
Fabricio Araujo
  • 957
  • 7
  • 14
2
votes
2 answers

Is it possible to limit the scope of a SYNONYM in SQL Server?

I have a series of scheduled stored procedures that need to be run against different databases that have identical schemas (think multiple instances of an application). So for instance, every database will always have the table member which will…
e_i_pi
  • 217
  • 4
  • 12
1
2 3 4