1

I have a Sql database with data. I have been asked to populate a fresh identical database with all the required master data so that the application is able to up and run for a new customer.

First approach

Delete all the data from database, run the application, sure i won't be even able to login. Observe errors, identify tables which need master data(sure User table at least), insert data. Then assume i am going to access a module. But without some master data it'll give me errors. Observe errors, identify tables which need master data, insert data. But this seems not practical.

Second approach

While keeping the data in database, take one table at a time, using queries or sql server management studio tools, find all dependent tables. Keep the parent table data and delete child table data. Do this for all tables. In second round consider the remaining parent tables. Some table's data are inserted from application. Identify those and delete them. This way i can have relevant master data at the end. But i don't know how to approach this.

All these are my thoughts. Sure there might be many more approaches which are more precise and easier than these.I am confused with what to do. Please guide me. Thanks!

Isuru
  • 950
  • 1
  • 13
  • 34
  • I hope it helps, http://stackoverflow.com/questions/3829271/how-can-i-clone-an-sql-server-database-on-the-same-server-in-sql-server-2008-exp – ahmed abdelqader May 08 '17 at 06:13
  • And use `sp_depends` for getting dependencies : https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-depends-transact-sql – ahmed abdelqader May 08 '17 at 06:15

3 Answers3

0

Here's a few queries you could use to figure out which table and column is referencing which table and column...

select * from INFORMATION_SCHEMA.KEY_COLUMN_USAGE
select * from INFORMATION_SCHEMA.columns
select * from INFORMATION_SCHEMA.tables
select * from sys.foreign_keys
select * from sys.foreign_key_columns
select * from [sys].[objects] where [name] = 'your_tablename'

For more, open Object Explorer (View Menu) and expand:

Databases/System Databases/Master/Views/System Views.

Also, check out any database diagrams there might be in Object Explorer:

Databases/Your_db_name/Database Diagrams.

russ
  • 579
  • 3
  • 7
0

How big is the database ?

No matter what you have to make proper documentation ?So better start with documentation. You have to list all table one by one and identity if it is master table.

Remember the diff. between Delete or Truncate.

While doucmenting above query will come in handy.

Save the query and document for future need.

Most importantly,there should not be any error,even if any of the table is empty.

KumarHarsh
  • 5,046
  • 1
  • 18
  • 22
0

To find foreign key dependecies between tables you can use

SELECT FKT.name 'Parent table', CHT.name 'Child table' FROM sys.foreign_keys FK
JOIN sys.tables CHT ON FK.parent_object_id = CHT.object_id
JOIN sys.tables FKT ON FK.referenced_object_id = FKT.object_id

There is also ways to find dependencies in database views using system views.

dvirovec
  • 41
  • 3