none
Copy constraints and indexes from one database to another RRS feed

  • Question

  • I have all the tables copied on to a different database with the 'Select * Into...' clause from one server to another. But this statement does not transfer constraints and indexes. How do we transfer the constraints and indexes to the destination server through Transact SQL. Please help...
    Ashutosh.
    Sunday, April 5, 2009 5:33 AM

Answers

  • Hello Ashuosh

    Use the Generate Scripts Wizard in SSMS for generating the script for constraints and indexes.

    Follow these steps:

    1. Open SSMS
    2. Expand databases and select the database->tasks->generate scripts to launch the GSW
    3. click next and set "Script Check Constraints" to true, "Script Indexes " to true, "ScriptDependencies" to true and set whatever is needed.
    4. Select the tables for which the script is needed
    5. Click next ->next->finish

    This generates the script for table with indexes, constraints etc.

    More information: http://msdn.microsoft.com/en-us/library/ms178078.aspx

    Thanks
    Sreekar
    Sunday, April 5, 2009 5:49 AM

All replies

  • Hello Ashuosh

    Use the Generate Scripts Wizard in SSMS for generating the script for constraints and indexes.

    Follow these steps:

    1. Open SSMS
    2. Expand databases and select the database->tasks->generate scripts to launch the GSW
    3. click next and set "Script Check Constraints" to true, "Script Indexes " to true, "ScriptDependencies" to true and set whatever is needed.
    4. Select the tables for which the script is needed
    5. Click next ->next->finish

    This generates the script for table with indexes, constraints etc.

    More information: http://msdn.microsoft.com/en-us/library/ms178078.aspx

    Thanks
    Sreekar
    Sunday, April 5, 2009 5:49 AM
  • Thanks Sreekar but I have a constraint of doing it through Transact SQL and not through the wizard as it will be used every month to sync servers. Is there a way of doing it through TSQL. Even scripting through TSQL would be great.
    Ashutosh.
    • Proposed as answer by GregK-PPCDev Thursday, June 4, 2009 4:58 AM
    Sunday, April 5, 2009 6:03 AM
  • Ashutosh,

    I'm looking for the same thing, just to save time, hoping that someone had already posted the code. If not, here's what I will do next:

    a) start SQL Profiler and perform one table transfer with index and constraint, and see the code that SSMS runs.

    b) use what I know of sysobjects, sys.tables, sys.constraints, and all the lovely system tables and views, to create exec('sql to create index goes here'), using info about indexes from src db, and writing the statement so same would be created in dest db.

    Please let me know if you discover someone else's sample code to save us all time.

    -Greg

    EDIT:
    Here's what I got from Profiler for a table called [T_Role] (substitute with your own):

    -- Column descriptions
    select col.name, col.column_id, st.name as DT_name, schema_name(st.schema_id) as DT_schema, col.max_length, col.precision, col.scale, bt.name as BT_name, col.collation_name, col.is_nullable, col.is_ansi_padded, col.is_rowguidcol, col.is_identity, case when(idc.column_id is null) then null else CONVERT(nvarchar(40), idc.seed_value) end, case when(idc.column_id is null) then null else CONVERT(nvarchar(40), idc.increment_value) end, CONVERT(bit, case when(cmc.column_id is null) then 0 else 1 end) as is_computed, convert(bit, ColumnProperty(col.object_id, col.name, N'IsIdNotForRepl')) as IsIdNotForRepl, col.is_replicated, col.is_non_sql_subscribed, col.is_merge_published, col.is_dts_replicated, col.rule_object_id, robj.name as Rul_name, schema_name(robj.schema_id) as Rul_schema, col.default_object_id, OBJECTPROPERTY(col.default_object_id, N'IsDefaultCnst') as is_defcnst, dobj.name as def_name, schema_name(dobj.schema_id) as def_schema, CONVERT(bit, case when (ftc.column_id is null) then 0 else 1 end) as is_FullTextCol, col_name(col.object_id, ftc.type_column_id) FT_type_column, ftc.language_id as FT_language_id, case when(cmc.column_id is null) then null else cmc.definition end as formular, case when(cmc.column_id is null) then null else cmc.is_persisted end as is_persisted, defCst.definition, COLUMNPROPERTY(col.object_id, col.name, 'IsDeterministic') as IsDeterministic, xmlcoll.name as xmlSchema_name, schema_name(xmlcoll.schema_id) as xmlSchema_schema, col.is_xml_document, col.is_sparse, col.is_column_set from sys.columns col left outer join sys.types st on st.user_type_id = col.user_type_id left outer join sys.types bt on bt.user_type_id = col.system_type_id left outer join sys.objects robj on robj.object_id = col.rule_object_id and robj.type = 'R' left outer join sys.objects dobj on dobj.object_id = col.default_object_id and dobj.type = 'D' left outer join sys.default_constraints defCst on defCst.parent_object_id = col.object_id and defCst.parent_column_id = col.column_id left outer join sys.identity_columns idc on idc.object_id = col.object_id and idc.column_id = col.column_id left outer join sys.computed_columns cmc on cmc.object_id = col.object_id and cmc.column_id = col.column_id left outer join sys.fulltext_index_columns ftc on ftc.object_id = col.object_id and ftc.column_id = col.column_id left outer join sys.xml_schema_collections xmlcoll on xmlcoll.xml_Collection_id = col.xml_Collection_id where col.object_id = object_id(N'dbo.T_Role') order by col.column_id

    -- Keys and Indexes
    select ind.name, dsp.name as space_name, ind.index_id, ind.type, ind.is_unique, ind.ignore_dup_key, ind.is_primary_key, ind.is_unique_constraint, ind.fill_factor, ind.is_padded, ind.is_disabled, ind.is_hypothetical, ind.allow_row_locks, ind.allow_page_locks, convert(bit, IndexProperty(ind.object_id, ind.name, N'IsFulltextKey')) as is_FulltextKey, sta.no_recompute, ind_col.index_column_id, col.name as column_nName, ind_col.key_ordinal, ind_col.is_descending_key, ind_col.is_included_column, ind_col.partition_ordinal , ind_col.column_id , ind.data_space_id  from sys.indexes ind left outer join sys.stats sta on sta.object_id = ind.object_id and sta.stats_id = ind.index_id left outer join (sys.index_columns ind_col inner join sys.columns col on col.object_id = ind_col.object_id and col.column_id = ind_col.column_id )  on ind_col.object_id = ind.object_id and ind_col.index_id = ind.index_id left outer join sys.data_spaces dsp on dsp.data_space_id = ind.data_space_id  where ind.object_id = object_id(N'dbo.T_Role')  and ind.index_id >= 0 and ind.type <> 3 and ind.type <> 4 and ind.is_hypothetical = 0   order by ind.index_id, ind_col.key_ordinal

    -- XML Indexes
    select IndA.name, IndB.name as ref_name, IndA.using_xml_index_id, IndA.index_id, IndA.fill_factor, IndA.is_padded, IndA.is_disabled, IndA.is_hypothetical, IndA.allow_row_locks, IndA.allow_page_locks, IndA.secondary_type, st.no_recompute, ind_col.index_column_id, col.name as column_nName, ind_col.key_ordinal, ind_col.is_descending_key, ind_col.column_id from (sys.xml_indexes IndA left outer join sys.xml_indexes IndB on IndB.object_id = IndA.object_id and IndB.index_id = IndA.using_xml_index_id) inner join (sys.internal_tables IntT inner join sys.stats st on IntT.internal_type = 202 and st.object_id = IntT.object_id) on IntT.parent_id = IndA.object_id and st.name = IndA.name inner join sys.index_columns ind_col on ind_col.object_id = IndA.object_id and ind_col.index_id = IndA.index_id inner join sys.columns col on col.object_id = ind_col.object_id and col.column_id = ind_col.column_id where IndA.object_id = object_id(N'dbo.T_Role') order by IndA.index_id, ind_col.key_ordinal

    -- Spatial Indexes
    select  IndA.name, IndA.index_id, IndA.fill_factor, IndA.is_padded, IndA.is_disabled,IndA.is_hypothetical, IndA.allow_row_locks, IndA.allow_page_locks,IndA.spatial_index_type, IndB.bounding_box_xmin, IndB.bounding_box_ymin, IndB.bounding_box_xmax, IndB.bounding_box_ymax, IndB.level_1_grid, IndB.level_2_grid, IndB.level_3_grid, IndB.level_4_grid, IndB.cells_per_object, ind_col.index_column_id, col.name as column_nName, ind_col.key_ordinal, ind_col.is_descending_key, ind_col.column_id, st.no_recompute from (sys.spatial_indexes IndA left outer join sys.spatial_index_tessellations IndB on IndB.object_id = IndA.object_id and IndB.index_id = IndA.index_id) inner join(sys.internal_tables IntT inner join sys.stats st on st.object_id = IntT.object_id and st.stats_id = 1) on IntT.parent_id = IndA.object_id and  IntT.parent_minor_id  = IndA.index_id inner join sys.index_columns ind_col on ind_col.object_id = IndA.object_id and ind_col.index_id = IndA.index_id inner join sys.columns col on col.object_id = ind_col.object_id and col.column_id = ind_col.column_id where IndA.object_id = object_id(N'dbo.T_Role') order by IndA.index_id, ind_col.key_ordinal

    -- Full-text Indexes
    select  FtInd.unique_index_id, Ind.name as Index_Name, FtInd.fulltext_catalog_id, FtCat.name as Catalog_Name, FtInd.is_enabled, FtInd.change_tracking_state, FtInd.has_crawl_completed, FtInd.crawl_type, cast(0 as int), cast(0 as int), FtInd.crawl_start_date, FtInd.crawl_end_date, FtInd.incremental_timestamp  from  sys.fulltext_indexes FtInd left outer join sys.fulltext_catalogs FtCat on FtCat.fulltext_catalog_id = FtInd.fulltext_catalog_id inner join sys.indexes Ind on Ind.object_id = FtInd.object_id and Ind.index_id = FtInd.unique_index_id  where FtInd.object_id = object_id(N'dbo.T_Role')

    -- Foreign Keys (Relationships)
    select Fk.name, Fk.object_id, Fk.is_disabled, Fk.is_not_for_replication, Fk.delete_referential_action, Fk.update_referential_action, object_name(Fk.parent_object_id) as Fk_table_name, schema_name(Fk.schema_id) as Fk_table_schema, TbR.name as Pk_table_name, schema_name(TbR.schema_id) Pk_table_schema, col_name(Fk.parent_object_id, Fk_Cl.parent_column_id) as Fk_col_name, col_name(Fk.referenced_object_id, Fk_Cl.referenced_column_id) as Pk_col_name, Fk_Cl.constraint_column_id, Fk.is_not_trusted from sys.foreign_keys Fk left outer join sys.tables TbR on TbR.object_id = Fk.referenced_object_id inner join sys.foreign_key_columns Fk_Cl on Fk_Cl.constraint_object_id = Fk.object_id where Fk.parent_object_id = object_id(N'dbo.T_Role') or Fk.referenced_object_id = object_id(N'dbo.T_Role') order by Fk.object_id, Fk_Cl.constraint_column_id

     


    That's plenty of info to re-compose your own ALTER TABLE xyz WITH CHECK ADD ...blah...blah... on the newly copied tables. I bet if I think a minute, I'll be able to create a set-based operation that spews out the code for all updates and changes as a single string (will be much larger than nvarchar(4000), so it's a bad idea), or a set of strings, and then apply exec to each row (string). That would probably be lightning fast even for a DB with hundreds of tables.



    Give me your 2 cents' worth, Receive a penny for your thoughts... I'll keep the change, if you don't mind
    • Edited by GregK-PPCDev Thursday, June 4, 2009 4:57 AM code update
    • Proposed as answer by GregK-PPCDev Thursday, June 4, 2009 4:58 AM
    Thursday, June 4, 2009 4:11 AM
  • There it is, got it! Below code works in SQL2005 or later and only for regular indexes(clustered and non, unique and not) and primary keys. Will not work with full-text, spatial, or XML indexes.
    Relatively graceful, creates and runs T-SQL strings to do the job.

    IMPORTANT: Don't forget to make adjustments for 2 databases. Code below would re-create all its own indexes since db is being compared to itself. Insert reference to sourceDb where necessary when comparing.

    declare @tbl nvarchar(255) = N'tableName';
    create table #tmpIndxs (rnum int IDENTITY(1,1) PRIMARY KEY NOT NULL,execStr nvarchar(MAX));
    
    with qry(name,type_desc,is_unique,ignore_dup_key,fill_factor,pad_index,row_locks,page_locks,column_nName,key_ord,key_desc) as
    (
    select ind.name, ind.type_desc, ind.is_unique, ind.ignore_dup_key, ind.fill_factor, ind.is_padded, ind.allow_row_locks, ind.allow_page_locks, col.name as column_nName, ind_col.key_ordinal, ind_col.is_descending_key
      from sys.indexes ind
      left outer join sys.stats sta on sta.object_id = ind.object_id and sta.stats_id = ind.index_id
      left outer join (sys.index_columns ind_col
                      inner join sys.columns col on col.object_id = ind_col.object_id and col.column_id = ind_col.column_id ) 
                   on ind_col.object_id = ind.object_id and ind_col.index_id = ind.index_id
      left outer join sys.data_spaces dsp on dsp.data_space_id = ind.data_space_id
      inner join sys.tables st on ind.object_id = st.object_id
     where st.name = @tbl and ind.index_id >= 0 and ind.is_disabled=0 and ind.is_primary_key=0 and ind.type <> 3 and ind.type <> 4 and ind.is_hypothetical = 0
    ),
    bigQ(indName,cols,isUnique,type,options) as
    (
    SELECT DISTINCT name COLLATE DATABASE_DEFAULT, 
     ( SELECT column_nName + case key_desc when 1 then ' DESC' else '' end + ','
    	 FROM qry q2                    
    	WHERE q2.name = q1.name
    	ORDER BY name,key_ord
    	  FOR XML PATH('') ),
     is_unique, type_desc, 'DROP_EXISTING=ON,ONLINE=ON,IGNORE_DUP_KEY='+case ignore_dup_key when 1 then 'ON' else 'OFF' end+',FILLFACTOR='+STR(fill_factor,3,0)+',PAD_INDEX='+case pad_index when 1 then 'ON' else 'OFF' end+',ALLOW_ROW_LOCKS='+case row_locks when 1 then 'ON' else 'OFF' end+',ALLOW_PAGE_LOCKS='+case page_locks when 1 then 'ON' else 'OFF' end
      FROM qry q1
    )
    insert into #tmpIndxs(execStr)
    select 'CREATE '
          +case isUnique when 1 then 'UNIQUE ' else '' end
          +case type when 'HEAP' then null else type end
          +' INDEX ['+indName+'] ON ['+@tbl+'] ('+LEFT(cols,LEN(cols)-1)+') WITH ('+options+')'
    from bigQ;
    
    declare @RowCnt int = 1;
    declare @MaxRows int = (select COUNT(*) from #tmpIndxs);
    declare @execStr nvarchar(MAX);
    while @RowCnt<=@MaxRows
    begin
    select @execStr=execStr from #tmpIndxs where rnum=@RowCnt;
    if @execStr is not null exec(@execStr);
    set @RowCnt=@RowCnt+1;
    end
    
    declare @curPk nvarchar(255);
    select top 1 @curPk=name from sys.indexes where object_id = object_id(@tbl) and is_primary_key=1;
    declare @execPK nvarchar(255)=null;
    select @execPK=coalesce(@execPK+',',case @curPk when null then '' else 'ALTER TABLE ['+@tbl+'] DROP CONSTRAINT ['+@curPk+'];' end+'ALTER TABLE ['+@tbl+'] ADD CONSTRAINT ['+ind.name+'] PRIMARY KEY (')+'['+col.name+']' from sys.indexes ind
      left outer join (sys.index_columns ind_col
                      inner join sys.columns col on col.object_id = ind_col.object_id and col.column_id = ind_col.column_id ) 
                   on ind_col.object_id = ind.object_id and ind_col.index_id = ind.index_id
    where ind.object_id = object_id(@tbl) and is_primary_key=1
    order by ind.index_id, ind_col.key_ordinal;
    set @execPK=@execPK+');';
    
    if @execPK is not null exec(@execPK);

    Give me your 2 cents worth, Receive a penny for your thoughts... I think I'll keep the change!
    • Proposed as answer by GregK-PPCDev Thursday, June 4, 2009 7:51 AM
    Thursday, June 4, 2009 7:51 AM
  • Ashutosh,

    I suggest you re-read Sreekar's reply. SSMS has the ability to to generate TSQL scripts of everything you do in the GUI. But in the case mentioned above there is a specific "Generate Scripts Wizard" to create the TSQL code suitable to put directly into your Monthly batch job. It is a very nice feature.

          Of course this assumes that you want to the same thing every months & the schema never changes. If the schema in the source database is changing & you need to completely delete the target & rebuild it each month.
    Why not try either :-

    1. Snapshot replication. (set to run each month) OR
    2. Take a "COPY" backup & then Restore it with a different name. OR
    3. Transactional Replication

    Running scripts to completely reverse engineer the system tables only to rebuild them elsewhere & then copy the data is computationally inefficient. Possibly only worth doing if you are attempting to create a Delta Script OR planning on shipping it as part of a new product instalation.

    Warning: It is really tempting & fairly easy to create code that references the system tables to do things. Hypocritically I've been doing so for the past 20 years. It is a bad practice. They've changed with every release of SQL. Often in very subitle ways. If used in production you are potentially creating a maintenance nightmare. Remember it is not just the structure of the tables you need to think about but also the new values they might contain. eg: Internal Tables, Queues & Functions weren't in sysobjects a few releases ago.


     

     

    Saturday, June 25, 2011 5:21 AM