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 pointing to the TABLE_1 in the SCHEMA_B - and the SCHEMA_B owns a SYNONYM_2 pointing to the TABLE_2 in the SCHEMA_A Apparently technically is possible, but is that a good practice ? Does not sound as a kind of circular reference if you compare this to the "Software modules" concept ? Thanks in advance for your responses.

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 always have the columns member_number, name, and dob. The stored procedure find_new_members() would use exactly the same SQL no matter which database it was executed against. I've found the command CREATE SYNONYM which looks like it will do the trick if I do something like this: DECLARE @database NVARCHAR(16) = 'ClientA'; DECLARE @statement NVARCHAR(MAX) = 'CREATE SYNONYM CurrentDB FOR ' + @database; EXECUTE sp_executesql @statement; // Run generic statements This allows me to farm the generic SQL out to a stored proc, and then just pass in the target database name. The problem is, it looks like the SYNONYM is scoped at a database level, so this means I can't have an SSIS package that runs the scripts against each client in their own stream. Not without the SYNONYM changing and affecting all scripts. Is it possible to scope a SYNONYM statement to a user-level or (ideally) a PID-level, so that I can at least kick off multiple jobs at once, each using a different value for the SYNONYM CurrentDB? ----- EDIT: To explain my use case a little more, we are running queries from a database we have full read-write-execute on, against multiple linked databases we only have read rights on. Those databases are snapshots of source systems that we have no control over, and are simply databases sitting in a data lake. If we start throwing around `sp_executesql statements on the master scripts, we're going to lose a lot of refactoring and code control aspects in our IDEs. Ideally, we're looking at "aliasing" a linked database at a PID level.

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 accounts (schemas) that have been granted access to the tables created by the owner. At the moment, it's a nuisance to have to log into each service account to create synonyms. Is there a way to run a script to do it from the owner schema? We're trying to automate this process.

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 maintenance. I have a separate, data collection system database which gathers usage information from the other systems, and I would like it to continue to be generally forwardly and backwardly compatible with the other systems. This system is all internal to the database - scheduled jobs, procs etc. No external Windows programs, services etc. Ideally, the source systems would expose consistent interfaces and manage what they expose themselves in a future-proof way, and I do try to utilize that where possible. But there are always exceptions to what the other product's roadmaps are able to accommodate. I would like newer versions of the data collection system to work whether or not a table (or tables) exists in the source system, and simply note that or skip it or log it as appropriate if it's not appropriate to be used. If that system is upgraded later, I do not want to necessarily have to touch the data collection system. So any procs should not have to be altered or re-created. We generally use synonyms for each source system so that database renaming is handled in as few places as possible (ideally in the synonyms only). I have experimented a bit and it seems like you can CREATE SYNONYM for a table/view that doesn't exist yet without a failure. And you can CREATE PROC using those synonyms without failure as well. So it would be possible to have the system installed identically on system with and without one of the source databases and should be able to run un-modified when the database for that module is installed (or removed). I am currently just looking for a way to do the following two things - to know the synonym points to something, and to check that thing and make sure it meets whatever requirements: 1. To know reliably if a synonym is valid. When the first column here is NULL, is that sufficient to know it is invalid? > SELECT object_id(base_object_name), * FROM sys.synonyms 2. If a synonym is valid, is there a way to get the metadata to check it out? The columns, datatypes could be gotten from sys.columns (or INFORMATION_SCHEMA), but that needs to be qualified with a known database name - the answer below selects into a temp table first and then looks at the schema of the temp table. Is there any better alternative? > https://stackoverflow.com/a/27126581/18255

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] FOR remoteserver_name .[database_name].[schema_name].object_name **Requirement** I need to fetch the columns of the synonym. I can retrieve the columns of the synonym using below query in SQL server. select * from dbo.test **where** dbo is synonym created schema name (local server schema name) and test is synonym name. I have tried to retrieve the columns of the synonyms using below query, but I cannot fetch the columns SELECT sys.schemas.name AS SCHEMA_NAME, sys.synonyms.name AS view_name, sys.columns.name AS COL_NAME, sys.types.name AS data_typename FROM sys.columns INNER JOIN sys.synonyms ON OBJECT_ID(sys.synonyms.base_object_name) = sys.columns.object_id INNER JOIN sys.schemas ON sys.schemas.schema_id = sys.synonyms.schema_id LEFT OUTER JOIN sys.types ON sys.columns.system_type_id = sys.types.system_type_id WHERE sys.types.system_type_id = sys.types.user_type_id AND sys.schemas.name = N'scehmaname' AND sys.synonyms.name = N'synonymname' Can you please suggest some other way to retrieve the columns of the synonym or correct the above query?

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 SP each time we push to Live. We want a single of line of code such as select col1, col2 from databaseName..ourTable Where databaseName is somehow set at a server level. I understand synonyms are the wrong solution for this. Is there something similar we can use that doesn't include writing a synonym for every single object?

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

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 of synonyms table. The base table that the synonym points to is on another server and another database, but I just handle db server with table synonym, I just want to use the synonym to pull data across the linked server. when I execute, I got error about table x don't exist but I have synonyms :)m and I can choose it from export wizard, anyone know about how to export data from table of synonyms to excel file?

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 synonym that points to the table in database B or just fully qualify the table in the package?

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 names. If I later search for "Microsoft", it won't find the posts where I called them "Micro$oft", etc.: SELECT * FROM diary WHERE message ILIKE '%Microsoft%'; So I need to construct some kind of "custom synonyms" table, so that any search for "Microsoft" also finds diary entries containing "M$", etc. If I simply make it a table with two columns, which seems like the most logical approach to me, how can I integrate that into my ILIKE search? "source word", "synonym" 'Microsoft', 'Microjunk', 'Microsoft', 'Micro$oft', 'Microsoft', 'M$', 'McDonald\'s', 'EvilBurger', 'McDonald\'s', 'McEvil', ... If possible, please don't refer me to the "fulltext search" documentation, because I've loaded that part of the manual 100+ times over the last 15 years and never came back understanding one bit about it! Something about it just seems impossible for my brain to grasp, even though I want to understand and be able to use it.

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 application. For example: - Users will be shared across the system (you register and are given access to one or more applications), so the Users table is in the admin schema. - Application ACME will generate reports and store them, so the ACME Reports table is in the ACME schema. Simple enough, except ACME reports are associated with a user. We could simply have copies of the Users table in each schema, and update them each time the "users master table" (admin schema) is changed, but that would be _extremely_ painful to work with. Instead, we are sharing the table, and creating synonyms to abstract the code from that fact. So, in our admin schema:

create table admin_users ( ... );
grant select, references on admin_users to acme;

And in our ACME schema:

create synonym acme_users for admin.admin_users;
alter table acme_reports add constraint fk_user foreign key (user_id)
      references acme_users (id);

This works just fine and will keep the referential integrity across schemas , but **are there any downfalls to this approach we should be aware of, or any best practices we should follow here?** With regards to either accessing tables from another schema, or using the (private) synonyms. All the schemas will be on the same Oracle database, so there shouldn't be any significant performance impact (compared to having all the tables in the same schema). Right? If this approach is valid, **is there any practical way to manage the grants?** This works:

grant select, references on admin_table to app_schema

But the number of tables which need to be granted will grow over time, and so will the number of schemas. Whenever we add a new schema, we would have to grant it the permissions for all the shared tables. We thought of granting the permissions to a role instead, and just giving that role to the new schemas. But apparently that doesn't work:

ORA-01931: cannot grant REFERENCES to a role

Is there any other way to manage it?

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 as well. The **synonyms** table is basic, and have to columns : **word1** and **word2**. Example of its content : - chiptune 8bit - lofi lo-fi - lo-fi 8bit In SQL, How can I present to the user results feturing the word "**lofi**" when he type "**chiptune**"? What I am trying to achieve is a "loop between synonyms", in both ways. Thank you if you have some thoughts.

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 have a script in place for doing this, which calls expdp and impdp. However, I'm experiencing some issues now, because there have been a lot of recent structural changes to the production database that seem to be causing problems in conjunction with the parfile being passed to impdp. I should say that I'm quite new to expdp and impdp. I understand about TABLE_EXISTS_ACTION and the 4 options SKIP | APPEND | TRUNCATE | REPLACE, but how can I deal with a situation where a table name exported from the source database clashes with a materialized view name in the destination database, or vice versa? What about when the name belongs to a table in one database and a synonym in the other - what does impdp do in that case, or how can/should I deal with that? Really I don't care about any existing tables, views, materialized views or synonyms in the development database; if impdp tries to import some object that was exported from the production database, I just want it to be created, and any object whose name clashes can just be dropped basically. Also, I don't fully understand what actually happens when you export/import a materialized view from one database to another. A materialized view has a definition - so, is the actual data in the source materialized view exported, or just the definition, and the destination materialized view is refreshed according to the definition?

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 decided that one table has to be deleted. This table turns out to be the source table of the synonym. In my opinion does this also effect the synonym. Am I right? I think, because the source table is deleted, the synonym cannot retrieve any data anymore and will have empty columns.

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 other databases. On the linked server, there are the same databases as on DEV. Right now I have a stored procedure that inserts into the table with INSERT INTO Snapshot.SnapshotPersonEmployee We are instead conceptually wanting to change to inserting into a table on a linked server by using INSERT INTO [SnapshotDev].[Nus12345].[Snapshot].[SnapshotPersonEmployee] My question is, can I insert into the table on the linked server **without** changing the stored procedure? Is there a way (like making a synonym) that will reference the table on the linked server with just INSERT INTO Snapshot.SnapshotPersonEmployee **EDIT** I should add that this stored procedure appears once on every database, and that is why I want to try to do this without change the stored procedure. By not naming the database it allows us to keep one version of the stored procedure--one SP on each database, but they are all the same. We are thinking of removing the SnapshotPersonEmployee table in the current (DEV) server from all of the databases. Would this help/hinder any possible solutions?

what is the query used to generate ddl of synonym in one environment so that I can run it in the other environment

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 CREATE SYNONYM [dbo].[MYSYN] FOR [SERVER01].[DEMODB].[dbo].[CHILD] GO` The user gets the below error message. The specified schema name "dbo" either does not exist or you do not have permission to use it. I checked the login/user settings. And the login belongs to public server role and user is mapped to the database with default schema as [dbo] And also create synonym permission is granted for that database for that user as mentioned earlier. How can i fix this.

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 reinitialize the History DB. My idea was - in a nutshell: - Close down this DB, - Make it READ-ONLY, - Rename it to *old - Create a similar history DB (same structure/tables). - With the help of views/synonyms, the application will be able to query the data for both DBs without a code change. My question is about the last part Will that work ? Can I create view/synonym (the view will query both of table DBs with UNION ALL) that will be the default go-to object name instead of the actual original table name ? Our main problem is the backups, as the Archiving is done daily, we need also to back up this DB, and as it's huge DB, it's a waist of time and resources. Thanks, Roni.

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 they've got quotes in the connection string so need it in upper case). Going through and fixing those would be a risky, mammoth undertaking as there is nobody in the company left who originally created the database and all the things that rely on it, and documentation is scarce. As such, I've come to the conclusion that the best way forwards is to leave the existing database as it is, but to try and have it available by two names - its original name, and a new name. I would imagine just about everyone here already knows of something like this anyway, but I'm relatively new. I've seen terms like alias and synonym in my Googling. However, I don't believe either of these can be done to an entire database (if I'm wrong then I would be very happy with some information to go on). I've also tried creating an empty database (with the nice new lower-case name) and creating simple views to the original's tables, but of course views don't generally go cross-database. Anyone got any ideas? EDIT - also quickly tried dblink, which looks like it could do the job. Its an unknown function, so I'm guessing the version of postgres we're running is old. May see if we can upgrade it.

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 has some database objects in it, e.g. a table called dbo.foo * Database Synonym_Home has a synonym dbo.foo referring to the table dbo.foo in the database Synomym_Targ. * A backup of the database normally resident in Synonym_Targ is restored into it. This contains an object dbo.foo. Should one expect this process to invalidate the dbo.foo synonym on Synonym_Host?