Questions tagged [synonyms]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
0 votes
2 answers
47 views

Fully qualified or synonym for table in another database, cloud migration plan

Scenario: In SQL Server on-prem and plan to go the cloud in a year or two. I am writing a stored procedure in database A and I need to access a table from database B. Is it a best practice to create a ...
user avatar
  • 9
0 votes
1 answer
25 views

Creation Synonym among different db schemas

might seem a stupid question, but I'm pretty new to the db world. Is that a good practice to have two oracle schemas which have synonyms pointing to each other ? like SCHEMA_A owns a SYNONYM_1 ...
user avatar
  • 1
0 votes
0 answers
20 views

Linking synonym in ServerA.DatabaseA to ServerB.DatabaseB

There are a handful synonyms in ServerA.DatabaseA that references DatabaseB in ServerB. The synonyms in ServerA are constructed like this: USE [DatabaseA] GO CREATE SYNONYM SynonymName FOR ServerA....
user avatar
-1 votes
1 answer
23 views

How should I construct a "custom synonyms" table?

I have a table called diary. I insert several messages into it almost every day. I sometimes, for example, call Microsoft "Microjunk", "Micro$oft", "M$" and many other ...
user avatar
0 votes
1 answer
49 views

Are there any downsides to referencing tables from another schema using synonyms?

We are developing a modular system composed by an administration module and a few web apps. The database model is also split in that way: we have a schema for the admin module and another for each ...
user avatar
  • 121
1 vote
1 answer
20 views

How to search for multiple synonyms?

I have a database with 2 tables : products, and synonyms. When a user type one or several words to search in the products.description field, I would like to search for all the synonyms/substitutions ...
user avatar
0 votes
1 answer
672 views

What does Oracle impdp do when a name clashes with a materialized view or synonym?

I have a production Oracle database, and I want to export tables, data, views, materialized views, synonyms, etc, to import into a development Oracle database. Actually, this is part of my job, and we ...
user avatar
  • 101
0 votes
0 answers
59 views

Same name for a view and synonym in same schema

Scenario: In a schema (sc1), there is a synonym with name syn1 for table t1. I created a view with name as sc1.syn1. And it got created successfully without any conflict with synonym. Synonymn (syn1) ...
user avatar
1 vote
3 answers
1k views

Create Synonym error for a user-SQL Server

I have a user who has permission to create synonym in my sql server 2016 database.That user also has data reader permission. When the user tried to create synonym as shown below. USE [DEMODB] GO ...
user avatar
  • 1,295
-1 votes
1 answer
4k views

How to generate ddl for a synonym

what is the query used to generate ddl of synonym in one environment so that I can run it in the other environment
user avatar
1 vote
2 answers
88 views

Resetting a new archiving database

I have an archiving question. We have a History DB used for archiving old records from our main system and the application knows to query it However, it becomes too big. I would like to ...
user avatar
1 vote
0 answers
135 views

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 ...
user avatar
  • 111
4 votes
1 answer
491 views

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: ...
user avatar
1 vote
1 answer
25 views

Synonym being recreated randomly - possible to create some kind of trigger/log?

We have a couple of databases with synonyms to link each other, one of the synonyms is randomly being recreate incorrectly with an additional set of square brackets in the database name. I've checked ...
user avatar
6 votes
2 answers
4k views

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 ...
user avatar
1 vote
1 answer
213 views

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 ...
user avatar
  • 207
1 vote
0 answers
68 views

Querying a synonym (for a table using linked server) gives no data in return

I have a function which run in an application at a server. This server have a ODBC connection (user DSN) to the SQL server which is set to use windows authentication. The function is supposed to ...
user avatar
0 votes
1 answer
555 views

What happens with a synonym if the table which its based on gets deleted?

we are working on a project to implement a large ERP system. We found out that one of the tables has a synonym to another database. Both databases are in the same instance. Our project leader has ...
user avatar
0 votes
1 answer
53 views

Is there a way to identify the object behind the database synonym?

I have a script which uses a synonym. I have to alter that code and the problem is I can't understand which object that synonym refers to. Is there a way to find out? I am using the original database ...
user avatar
  • 2,006
0 votes
1 answer
537 views

How to join sys.columns and sys.synonyms table using object_id column [duplicate]

I have used two servers, Server A(local server) and Server B(remote server). I have created synonyms in the Server A for the Server B table. I have created synonym using following syntax. CREATE ...
user avatar
4 votes
2 answers
3k views

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 ...
user avatar
0 votes
1 answer
35 views

Synonyms from instance

I have two different DB instances. Is there any way to find out from instance2 (from all_synonyms view) list of all objects using synonyms from instance2 to instance 1 ...? I want to, for example, ...
user avatar
3 votes
1 answer
320 views

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 ...
user avatar
1 vote
2 answers
2k views

Table behind synonym does not exist

when I do select * from all_synonyms where synonym_name='SYNAAA' I can see synonym SYNAAA is public, and table behind it is TABAAA in schema A. Given SYNAA is public, I should be able to query it ...
user avatar
  • 11
4 votes
2 answers
2k views

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'...
user avatar
1 vote
1 answer
278 views

SQL Server - redirect synonym without downtime

I have multiple application databases that use synonyms point to a shared database with immutable data that needs to be updated periodically by restoring an external backup. We now have a new ...
user avatar
1 vote
2 answers
1k views

how to grant permissions to all the synonyms inside a view?

I have to grant permissions to a view. dbo.my_view, however, this view joins many tables, and other views and synonyms. using the following script I can find all all the synonyms in my server: ...
user avatar
0 votes
1 answer
52 views

View and Synonym hirarchy oracle

The Dba of our company has created views for the tables for all the users. We have 230 users, he has created 230 views for each tables! I want to replace this by public synonym and grant select to the ...
user avatar
0 votes
1 answer
165 views

PostgreSQL: Index Trigger / Hook? For Synonyms

Is it possible, in PostgreSQL, to add a trigger to modify data prior to it going into a full text index? PostgreSQL requires using files to add custom dictionaries for things like stop words, ...
user avatar
  • 103
0 votes
1 answer
1k views

Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema?

Is it possible grant a PRIVATE Synonym from an the Table Owner schema to an User schema? Background: We are doing microservice development and there is an sole owner schema and many different service ...
user avatar
1 vote
1 answer
3k views

Synonym/Alias equivalent for a Database

If we have code in an SP Database1 then queries Database2 (on the same server) we want the same code to work on the databases Database1Dev and Database2Dev. But this currently means editing the full ...
user avatar
  • 1,433
1 vote
0 answers
82 views

listing the permissions for a database object - and take into consideration it can be a synonym

I have a script that gives me all the permissions assigned to a database object: ------------------------------------------------- -- permissions to database object -- marcelo miorelli v. 20170401 ---...
user avatar
0 votes
1 answer
45 views

wildcard in creating synonymys

I want to create a synonyms for the user in my database. These are my user and my schema owner User : Scoot Schema Owner : kitty I would like to create a synonmys "Scoot" for accessing all the ...
user avatar
  • 1
2 votes
1 answer
3k views

Creating Synonym For a Table in PostgreSQL over dblink?

I have two PostgreSQL databases connected over dblink extension. I want to create synonym for a table in first database so the queries that are coming through application I can route them to the table ...
user avatar
4 votes
2 answers
7k views

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 ...
user avatar
-2 votes
1 answer
104 views

Post DB users into replica users the Synonyms still point to Source DB users

I have exported users HR,Fin AND imported into new users HR1,FIN1 in the same DB. The expdb is used for this purpose.The synonyms still point to the source users (HR,FIN) Example : Synonym def in ...
user avatar
-1 votes
2 answers
777 views

INSERT INTO SQL Server : Linked Server without specifying database in semi-fully qualified name

I have a server called DEV, a database called Nus12345, a linked server called SnapshotDev, a schema called Snapshot, and a table called SnapshotPersonEmployee. On the DEV server, there are many ...
user avatar
1 vote
1 answer
3k views

How can I fix these broken synonyms?

When I run the following query: SELECT S.OWNER AS SYN_OWNER, S.SYNONYM_NAME AS SYN_NAME, S.TABLE_OWNER AS OBJ_OWNER, S.TABLE_NAME AS OBJ_NAME, CASE WHEN O.OWNER IS NULL THEN '...
user avatar
1 vote
0 answers
42 views

Synonims in SQL Server 2008 R2 FTS

The Search feature on my website is based on SQL Server's Full Text Search (I insert everything relevant to a single table and this table is in the FTS Catalog). We don't have money to buy a ...
user avatar
3 votes
1 answer
424 views

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: ...
user avatar
  • 31
1 vote
1 answer
91 views

How to synonymize a database name?

Within one database instance, I need to restore a set databases into new names. The new names will be suffixed based on the development phases, like: _DEV and _QA. Where: DBApp_DEV shall access ...
user avatar
0 votes
0 answers
2k views

Postgres Public Synonym Issue

PostgreSQL ver 9.4.5. Linux OS. Application: Web Based Platform: App Server (java) --> jdbc call --> Database Server (PostgreSQL) I do know that PostgreSQL does not support the public ...
user avatar
  • 71
-4 votes
1 answer
996 views

how to create a synonym in Oracle? [closed]

some one tell me the basic syntax for creating synonym for the table employees create synonym for emp for app.employees while creating it shows error ORA-00995: missing or invalid synonym ...
user avatar
  • 3
1 vote
4 answers
3k views

Synonym with same name as linked server

I have a database that is used for reporting services. This database was created by a contractor who is no longer around. Buried all throughout the database are references to a single table on a ...
user avatar
0 votes
1 answer
1k views

Unable to update or insert values table with DateTime column defaulted to GETDATE

I have atlered table by adding Datetime column. This column is not null and also default value from GETDATE(). ALTER TABLE myTable ADD myDateColun DATETIME NOT NULL DEFAULT (GETDATE()) I'm trying ...
user avatar
  • 103
4 votes
2 answers
9k views

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 ...
user avatar
  • 345
2 votes
1 answer
14k views

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. ...
user avatar
2 votes
3 answers
915 views

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 ...
user avatar
1 vote
1 answer
1k views

How do I differentiate between user-defined public synonyms and built-in public synonyms?

I am trying to query the Oracle ALL_SYNONYMS view, but filtering by OWNER=PUBLIC returns both synonyms I have created and Oracle created ones. Is there a way to distinguish between the two?
user avatar
4 votes
2 answers
2k views

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 ...
user avatar