Re: script to find all parent records that have references in at least one table.

On Jan 13, 5:31 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
Inna (med...@xxxxxxxxxxx) writes:
I have multiple databases for the same application; each database is
for different project.
They have exactly the same structure. We decided to move/unify all
corporate tables, like Country, Currency, and Language etc in the
separate database. The problem: since the data is entered manually
from applications we have the same records in different databases, but
spelled differently, even different names (French, English  ...) or
with the different primary keys. All databases are relatively old, so
a good half of the records we want to move most likely has references
within its database. I need a list of all records by table that has
references within database, because for those we will need the
separate treatment. Meaning all the clients will have to approve name
or maybe we can disable some and move the references in archives. For
those that are not referenced we will delete and recreate in corporate
database with proper name. It’s a very slow process, we will be doing
it table by table, but for all databases in one shot. That is why I
would need something more or less generic, to be able to pass a table
name to SP and find all those records in the table that have
references and supply list to a clients.

I'm sorry, but I forgot to ask: which version of SQL Server are you on?
This affects the possible solutions a bit.

I'm not entirely convinced that it's a good idea to move data out to
a centralized database. Keep in mind that if you do this, you cannot
implement referential integrity with constraints, but will have to
resort to triggers. You are using constraints to enforce foreign keys,
aren't you? If not, then your task will be even more difficult.

Possibly you could have a centralised database where this corporate
data is maintained, and which you then replicate to the customer
databases. Then you could maintain data in one place, but still keep
referential integrity.

--
Erland Sommarskog, SQL Server MVP, esq...@xxxxxxxxxxxxx

Links for SQL Server Books Online:
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx- Hide quoted text -

- Show quoted text -

Hello,
It's exactly what I would like to do, to replicate but for that I
will need to update all referenced IDs, in which situation that SP
would be handy again. I can reuse the code
So we are still using 2000 for that application. All my tables are
using foreign keys, not triggers.
Thank you
.

Relevant Pages

  • Re: Syntax to add records if primary key = list
    ... SQL and RDBMS. ... Modern data processing began with punch cards, ... The users might not all have the same database access rights ... This rule would be enforce by a REFERENCES ...
    (comp.databases.ms-sqlserver)
  • SQL statement for left outer join for ranges in the same file
    ... I created the sql statement using the MS-Query tool however ... external database? ... and probable other References to pass syntax check for sqlopen etc. ...
    (microsoft.public.excel.programming)
  • Re: DB Architecture Questions (for joe celko)
    ... So a deck of punch cards or a mag tape is just like an SQL Schema to ... a table has a name in the database. ... OCCURS clause. ... This rule would be enforce by a REFERENCES clause on the Orders table ...
    (microsoft.public.sqlserver.programming)
  • Re: Table Design Question
    ... > requires more than two probes, no matter how large the database. ... > acceptable (in the relational model) to have an Identity attribute to ... the gap in the sequence is not filled in and the sequence ... > vin CHARNOT NULL REFERENCES Motorpool); ...
    (microsoft.public.sqlserver.programming)
  • Re: Mixed up with Relationships..help!
    ... The database you're describing ... The CONSTRAINT statement establishes a Primary Key or a Foreign Key, ... "REFERENCES" table and column. ... QuestionaireAnswers: QuestionaireAnswersID, StudentID, 2005, ...
    (microsoft.public.access.gettingstarted)
Loading