SELECT 'ALTER TABLE ' + quotename(s.name) + '.' + quotename(t.name) + 'ADD ' +   
          CASE WHEN cc.is_system_named = 1 THEN ''  
               ELSE ' CONSTRAINT ' + quotename(cc.name)  
          END + ' CHECK (' + cc.definition + ')'  
   FROM   sys.tables t  
   JOIN   sys.schemas s ON t.schema_id = s.schema_id  
   JOIN   sys.check_constraints cc ON cc.parent_object_id = t.object_id  
   ORDER  BY s.name, t.name, cc.name


DECLARE @cur CURSOR, 
        @object_id int,
        @index_id  int,
        @ixcmd nvarchar(MAX)

DECLARE @cmds TABLE (cmd nvarchar(MAX))

SET @cur = CURSOR STATIC FOR
   SELECT i.index_id, t.object_id 
   FROM   sys.indexes i
   JOIN   sys.tables t ON i.object_id = t.object_id
   WHERE  i.filter_definition IS NOT NULL

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @index_id, @object_id
   IF @@fetch_status <> 0
      BREAK
  

   -- Get the mandatory part of the index definition. That is type and columns.
   SELECT @ixcmd = 'CREATE ' +
                   CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END +
                   CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END +
                   CASE WHEN i.type = 3 THEN 'XML '
                        WHEN i.type = 4 THEN 'SPATIAL '
                        WHEN i.type IN (5, 6) THEN 'COLUMNSTORE '
                        ELSE ''
                    END + 'INDEX ' + quotename(i.name) +
                   ' ON ' + quotename(s.name) + '.' + quotename(t.name) +
                   '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
                             len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
   FROM   sys.indexes i
   JOIN   sys.tables t ON t.object_id = i.object_id
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   CROSS  APPLY (SELECT quotename(c.name) +
                        CASE ic.is_descending_key
                             WHEN 1 THEN ' DESC'
                             ELSE ''
                        END + ','
                 FROM   sys.index_columns ic
                 JOIN   sys.columns c ON ic.object_id = c.object_id
                                     AND ic.column_id = c.column_id
                 WHERE  ic.object_id = @object_id
                   AND  ic.index_id  = @index_id
                   AND  (i.type > 2 OR
                         ic.key_ordinal > 0)
                 ORDER  BY ic.key_ordinal
                 FOR XML PATH(''), TYPE) AS ic(collist)
   WHERE   i.object_id = @object_id
     AND   i.index_id  = @index_id
   
   -- Add any included columns.
   IF EXISTS (SELECT *
              FROM   sys.index_columns
              WHERE  object_id = @object_id
                AND  index_id  = @index_id
                AND  is_included_column = 1)
   BEGIN
      SELECT @ixcmd = @ixcmd + ' INCLUDE(' +
                      substring(ic.incllist.value('.', 'nvarchar(MAX)'), 1,
                                len(ic.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
      FROM  (SELECT quotename(c.name) + ','
             FROM   sys.index_columns ic
             JOIN   sys.columns c ON ic.object_id = c.object_id
                                 AND ic.column_id = c.column_id
             WHERE  ic.object_id = @object_id
               AND  ic.index_id  = @index_id
               AND  ic.is_included_column = 1
             ORDER  BY ic.index_column_id
             FOR XML PATH(''), TYPE) AS ic(incllist)
   END

   -- Tack on index filter and IGNORE_DUP_KEY setting. Thet latter is always there,
   -- so that we know that with have WITH section for the rest.
   SELECT @ixcmd = @ixcmd +
                   CASE WHEN filter_definition IS NOT NULL
                          THEN ' WHERE ' + filter_definition + ' '
                          ELSE ''
                   END 
   FROM    sys.indexes
   WHERE   object_id = @object_id
     AND   index_id  = @index_id

   INSERT @cmds (cmd) VALUES(@ixcmd)
END


SET @cur = CURSOR STATIC FOR
   SELECT st.stats_id, t.object_id 
   FROM   sys.stats st
   JOIN   sys.tables t ON st.object_id = t.object_id
   WHERE  st.filter_definition IS NOT NULL
     AND  NOT EXISTS (SELECT *
                      FROM  sys.indexes i
                      WHERE i.object_id = st.object_id
                        AND i.name = st.name)

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @index_id, @object_id
   IF @@fetch_status <> 0
      BREAK

   -- Get the statistics definition. FOr statistics we don't consider
   -- abaobjectstorageproperties, but assume that AbaPerls owns it all.
   SELECT @ixcmd = 'CREATE STATISTICS ' + quotename(st.name) +
                   ' ON ' + quotename(s.name) + '.' + quotename(t.name) +
                   '(' + substring(sc.collist.value('.', 'nvarchar(MAX)'), 1,
                             len(sc.collist.value('.', 'nvarchar(MAX)')) - 1) + ')' +
                   CASE WHEN st.filter_definition IS NOT NULL
                          THEN ' WHERE ' + st.filter_definition + ' '
                          ELSE ''
                   END +
                   CASE WHEN st.no_recompute = 1
                        THEN ' WITH NORECOMPUTE'
                        ELSE ''
                   END
   FROM   sys.stats st
   JOIN   sys.tables t ON t.object_id = st.object_id
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   CROSS  APPLY (SELECT quotename(c.name) + ','
                 FROM   sys.stats_columns sc
                 JOIN   sys.columns c ON sc.object_id = c.object_id
                                     AND sc.column_id = c.column_id
                 WHERE  sc.object_id = @object_id
                   AND  sc.stats_id  = @index_id
                 ORDER  BY sc.stats_column_id
                 FOR XML PATH(''), TYPE) AS sc(collist)
   WHERE   st.object_id = @object_id
     AND   st.stats_id  = @index_id
     
   INSERT @cmds (cmd) VALUES(@ixcmd)
END

SET @cur = CURSOR STATIC FOR
   SELECT i.index_id, t.object_id 
   FROM   sys.indexes i
   JOIN   sys.tables t ON i.object_id = t.object_id
   WHERE  i.filter_definition IS NOT NULL

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @index_id, @object_id
   IF @@fetch_status <> 0
      BREAK
  

   -- Get the mandatory part of the index definition. That is type and columns.
   SELECT @ixcmd = 'CREATE ' +
                   CASE i.is_unique WHEN 1 THEN 'UNIQUE ' ELSE '' END +
                   CASE i.index_id WHEN 1 THEN 'CLUSTERED ' ELSE '' END +
                   CASE WHEN i.type = 3 THEN 'XML '
                        WHEN i.type = 4 THEN 'SPATIAL '
                        WHEN i.type IN (5, 6) THEN 'COLUMNSTORE '
                        ELSE ''
                    END + 'INDEX ' + quotename(i.name) +
                   ' ON ' + quotename(s.name) + '.' + quotename(t.name) +
                   '(' + substring(ic.collist.value('.', 'nvarchar(MAX)'), 1,
                             len(ic.collist.value('.', 'nvarchar(MAX)')) - 1) + ')'
   FROM   sys.indexes i
   JOIN   sys.tables t ON t.object_id = i.object_id
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   CROSS  APPLY (SELECT quotename(c.name) +
                        CASE ic.is_descending_key
                             WHEN 1 THEN ' DESC'
                             ELSE ''
                        END + ','
                 FROM   sys.index_columns ic
                 JOIN   sys.columns c ON ic.object_id = c.object_id
                                     AND ic.column_id = c.column_id
                 WHERE  ic.object_id = @object_id
                   AND  ic.index_id  = @index_id
                   AND  (i.type > 2 OR
                         ic.key_ordinal > 0)
                 ORDER  BY ic.key_ordinal
                 FOR XML PATH(''), TYPE) AS ic(collist)
   WHERE   i.object_id = @object_id
     AND   i.index_id  = @index_id
   
   -- Add any included columns.
   IF EXISTS (SELECT *
              FROM   sys.index_columns
              WHERE  object_id = @object_id
                AND  index_id  = @index_id
                AND  is_included_column = 1)
   BEGIN
      SELECT @ixcmd = @ixcmd + ' INCLUDE(' +
                      substring(ic.incllist.value('.', 'nvarchar(MAX)'), 1,
                                len(ic.incllist.value('.', 'nvarchar(MAX)')) - 1) + ')'
      FROM  (SELECT quotename(c.name) + ','
             FROM   sys.index_columns ic
             JOIN   sys.columns c ON ic.object_id = c.object_id
                                 AND ic.column_id = c.column_id
             WHERE  ic.object_id = @object_id
               AND  ic.index_id  = @index_id
               AND  ic.is_included_column = 1
             ORDER  BY ic.index_column_id
             FOR XML PATH(''), TYPE) AS ic(incllist)
   END

   -- Tack on index filter and IGNORE_DUP_KEY setting. Thet latter is always there,
   -- so that we know that with have WITH section for the rest.
   SELECT @ixcmd = @ixcmd +
                   CASE WHEN filter_definition IS NOT NULL
                          THEN ' WHERE ' + filter_definition + ' '
                          ELSE ''
                   END 
   FROM    sys.indexes
   WHERE   object_id = @object_id
     AND   index_id  = @index_id

   PRINT @ixcmd
END


SET @cur = CURSOR STATIC FOR
   SELECT st.stats_id, t.object_id 
   FROM   sys.stats st
   JOIN   sys.tables t ON st.object_id = t.object_id
   WHERE  st.filter_definition IS NOT NULL
     AND  NOT EXISTS (SELECT *
                      FROM  sys.indexes i
                      WHERE i.object_id = st.object_id
                        AND i.name = st.name)

OPEN @cur

WHILE 1 = 1
BEGIN
   FETCH @cur INTO @index_id, @object_id
   IF @@fetch_status <> 0
      BREAK

   -- Get the statistics definition. FOr statistics we don't consider
   -- abaobjectstorageproperties, but assume that AbaPerls owns it all.
   SELECT @ixcmd = 'CREATE STATISTICS ' + quotename(st.name) +
                   ' ON ' + quotename(s.name) + '.' + quotename(t.name) +
                   '(' + substring(sc.collist.value('.', 'nvarchar(MAX)'), 1,
                             len(sc.collist.value('.', 'nvarchar(MAX)')) - 1) + ')' +
                   CASE WHEN st.filter_definition IS NOT NULL
                          THEN ' WHERE ' + st.filter_definition + ' '
                          ELSE ''
                   END +
                   CASE WHEN st.no_recompute = 1
                        THEN ' WITH NORECOMPUTE'
                        ELSE ''
                   END
   FROM   sys.stats st
   JOIN   sys.tables t ON t.object_id = st.object_id
   JOIN   sys.schemas s ON t.schema_id = s.schema_id
   CROSS  APPLY (SELECT quotename(c.name) + ','
                 FROM   sys.stats_columns sc
                 JOIN   sys.columns c ON sc.object_id = c.object_id
                                     AND sc.column_id = c.column_id
                 WHERE  sc.object_id = @object_id
                   AND  sc.stats_id  = @index_id
                 ORDER  BY sc.stats_column_id
                 FOR XML PATH(''), TYPE) AS sc(collist)
   WHERE   st.object_id = @object_id
     AND   st.stats_id  = @index_id

  
  
END

SELECT * FROM @cmds