Questions tagged [synonyms]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
6 votes
2 answers
5k 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 ...
0 votes
1 answer
2k 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 ...
2 votes
1 answer
340 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 ...
0 votes
1 answer
146 views

SQL Server export data from synonyms table to excel

I tried to export synonyms table from my server database to excel using SMSS, I choose database, then I choose export, and manage source and dest, then I choose Excel File and I rename sheet 1 to name ...
0 votes
2 answers
117 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 ...
0 votes
1 answer
123 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 ...
-1 votes
1 answer
65 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 ...
0 votes
1 answer
424 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 ...
1 vote
1 answer
69 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 ...
0 votes
1 answer
1k 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 ...
0 votes
1 answer
993 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 ...
-1 votes
2 answers
970 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 ...
-1 votes
1 answer
9k 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
1 vote
3 answers
2k 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 ...
1 vote
2 answers
126 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 ...
3 votes
0 answers
614 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 ...
4 votes
1 answer
658 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: ...
1 vote
1 answer
372 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 ...
1 vote
1 answer
40 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 ...
1 vote
0 answers
131 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 ...
3 votes
1 answer
451 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 ...
0 votes
1 answer
192 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 ...
0 votes
1 answer
755 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 ...
4 votes
2 answers
4k 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 ...
0 votes
1 answer
53 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, ...
1 vote
2 answers
3k 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 ...
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'...
1 vote
2 answers
2k 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: ...
0 votes
1 answer
89 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 ...
1 vote
1 answer
4k 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 ...
1 vote
1 answer
264 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, ...
1 vote
0 answers
87 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 ---...
4 votes
2 answers
10k 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 ...
0 votes
1 answer
53 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 ...
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 ...
1 vote
1 answer
4k 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 '...
4 votes
2 answers
8k 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 ...
-2 votes
1 answer
114 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 ...
1 vote
0 answers
49 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 ...
3 votes
1 answer
757 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: ...
1 vote
1 answer
166 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 ...
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 ...
-4 votes
1 answer
1k 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 ...
1 vote
4 answers
4k 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 ...
0 votes
1 answer
2k 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 ...
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. ...
2 votes
3 answers
1k 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 ...
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?
4 votes
2 answers
3k 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 ...
2 votes
1 answer
2k views

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