0

I am new to SQL server. Database: SQL Server 2012, Size 2TB

We are planning to consolidate multiple databases.dbo.* objects under a single database as different schemas (databaseN.SchemaN.). Thus we need to prepare scripts to move database1.dbo. objects in another database as a different schema (e.g. database2.schema2.*) including all the dependent objects (need exact replica). This needs to be done without using any tools (SSMS, ApexSQL etc).

How should I go about scripting this. I was thinking on the lines of below approach:

  1. Extract complete metadata (including all constraints/triggers/indexes/keys/partitions etc)

  2. Extract data

  3. Execute metadata scripts on target

  4. disable all relational constraints and triggers

  5. insert all the extracted data

  6. enable all the relational constraints and triggers.

If this approach is fine, can I get some assistance in how to go about scripting this. Also, please suggest if any other approach. Some tables are partitioned and 50-100GB in size.

James Z
  • 12,209
  • 10
  • 24
  • 44
PrashantLG
  • 1
  • 1
    What do you exactly mean with "how to go about scripting"? Do you mean doing the scripting for you? Or just advise to look into DMVs? If you're not going to use any tools, including SSMS, then you need to do it yourself, right? – James Z Mar 12 '20 at 06:04
  • Hi James, Thanks for your post. I am looking for best approach, DMVs and may be a link which has a sample stub or even manual way for migrating single object (so that I can script for entire set of objects). Thanks! – PrashantLG Mar 12 '20 at 06:19
  • Can someone please assist on this? – PrashantLG Mar 18 '20 at 05:05
  • You can find the views, functions etc. from here: https://learn.microsoft.com/en-us/sql/t-sql/language-reference?redirectedfrom=MSDN&view=sql-server-ver15 and then go through things like `sys.tables`, `sys.indexes`, `sys.foreign_keys` etc – James Z Mar 18 '20 at 06:30

0 Answers0