Questions tagged [synonyms]
The synonyms tag has no usage guidance.
50
questions
0
votes
1
answer
100
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
94
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 ...
- 9
0
votes
1
answer
97
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
-1
votes
1
answer
50
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 ...
- 61
0
votes
1
answer
335
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 ...
- 121
1
vote
1
answer
62
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 ...
- 121
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 ...
- 101
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,295
-1
votes
1
answer
7k
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
2
answers
122
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 ...
- 575
2
votes
0
answers
477
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 ...
- 121
4
votes
1
answer
618
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
39
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 ...
- 125
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 ...
user168914
2
votes
1
answer
315
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 ...
- 217
1
vote
0
answers
119
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 ...
- 11
0
votes
1
answer
889
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 ...
0
votes
1
answer
124
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 ...
- 2,120
0
votes
1
answer
692
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
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 ...
0
votes
1
answer
46
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, ...
3
votes
1
answer
406
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 ...
- 334
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 ...
- 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'...
- 51
1
vote
1
answer
355
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 ...
Jussi Kosunen
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:
...
- 15.8k
0
votes
1
answer
80
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
250
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, ...
- 113
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 ...
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,453
1
vote
0
answers
86
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
---...
- 15.8k
0
votes
1
answer
47
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 ...
- 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 ...
- 532
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 ...
- 61
-2
votes
1
answer
110
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
votes
2
answers
935
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 ...
- 607
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 '...
- 3,197
1
vote
0
answers
47
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 ...
- 13
3
votes
1
answer
694
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: ...
- 31
1
vote
1
answer
136
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 ...
- 149
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 ...
- 71
-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 ...
- 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 ...
- 113
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 ...
- 103
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 ...
- 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.
...
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 ...
- 131
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?
- 146
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 ...
- 969
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 ...
- 947