none
cancellazione tabelle in tempdb: drop table #objects, #views, #types RRS feed

  • Domanda

  • Buongiorno,

    vorrei esegiire le seguenti istruzioni prima di interrogare gli oggetti di un db per estrarre i tracciati record le PK FK, viste ecc.

    if object_id ('tempdb..#objects') is not null begin drop table #objects end if object_id ('tempdb..#views') is not null begin drop table #views end if object_id ('tempdb..#types') is not null begin drop table #types end

    Dovrei eseguire le operazioni sopra in un db di produzione durante l'attività degli utenti.

    Cortesemente, potete indicarmi se le operazioni  hanno impatto sull'attività dell'utente o eventuali transazioni appese ? In questo caso potete consigliarmi quando e come eseguire questa operazione ?

    Grazie per l'attenzione

    lunedì 27 aprile 2015 09:53

Risposte

Tutte le risposte

  • puoi descrivere cosa hai in produzione ed il motivo della tua richiesta ?

    Edoardo Benussi
    Microsoft MVP - Directory Services
    edo[at]mvps[dot]org

    domenica 3 maggio 2015 14:10
    Moderatore
  • Certamente:

    in produzione ho un db di sql 2000 movimentato da utenti tramite interfaccia grafica client.

    Oltre al tracciato record, vorrei ottenere più informazioni possibili riguardo alla struttura del DB  (PK, FK, VW, INDEX ecc.)

    Ho trovato uno script che estrae queste info e come già evidenziato effettua la drop table delle tabelle temporanee:

    lo script per intero è il seguente:

    set nocount on
    
    -- This does a specific schema.  Set the schema here
    --
    declare @schema varchar (max)
    select @schema = 'dbo'
    
    if object_id ('tempdb..#objects') is not null begin
        drop table #objects
    end
    
    if object_id ('tempdb..#views') is not null begin
        drop table #views
    end
    
    if object_id ('tempdb..#types') is not null begin
        drop table #types
    end
    
    
    -- Gets lists of tables and views belonging to the schema
    --
    select o.name
          ,o.object_id
      into #objects
      from sys.objects o
      join sys.schemas s
        on s.schema_id = o.schema_id
     where o.type in  ('U')
       and s.name = @schema
    
    
    select o.name
          ,o.object_id
      into #views
      from sys.objects o
      join sys.schemas s
        on s.schema_id = o.schema_id
     where o.type in  ('V')
       and s.name = @schema
    
    
    -- Some metadata for rendering types
    --
    select a.* 
      into #types
      from ((select 'decimal' as typename, 6 as format) union all
            (select 'numeric', 6) union all
            (select 'varbinary', 1) union all
            (select 'varchar', 1) union all
            (select 'char', 1) union all
            (select 'nvarchar', 1) union all
            (select 'nchar', 1)) a
    
    
    
    
    
    -- This generates 'drop table' and 'drop view' statements
    --
    select 'if exists (select 1' + char(10) +
           '             from sys.objects o' + char(10) +
           '             join sys.schemas s' + char(10) +
           '               on o.schema_id = s.schema_id' + char(10) +
           '            where o.name = ''' + o.name + '''' + char(10) +
           '              and s.name = ''' + @schema +'''' + char(10) +
           '              and o.type = ''U'') begin' + char(10) +
           '    drop table [' + @schema + '].[' + o.name + ']' + char(10) +
           'end' + char(10) +
           'go' + char(10)
      from sys.objects o
      join #objects o2
        on o.object_id = o2.object_id
     where o.type = 'U'
    
    
    select 'if exists (select 1' + char(10) +
           '             from sys.objects o' + char(10) +
           '             join sys.schemas s' + char(10) +
           '               on o.schema_id = s.schema_id' + char(10) +
           '            where o.name = ''' + o.name + '''' + char(10) +
           '              and s.name = ''' + @schema + '''' + char(10) +
           '              and o.type = ''V'') begin' + char(10) +
           '    drop view [' + @schema + '].[' + o.name + ']' + char(10) +
           'end' + char(10) +
           'go' + char(10)
      from sys.objects o
      join #objects o2
        on o.object_id = o2.object_id
     where o.type = 'V'
    
    
    -- This generates table definitions
    --
    select case when c.column_id = 
                     (select min(c2.column_id)
                        from sys.columns c2
                       where c2.object_id = o.object_id)
                then 'create table [' + @schema + '].[' + isnull(o.name, 'XYZZY') + '] (' + char(10)
                else ''
                end +
           left('        [' +rtrim(c.name) + '] ' +
           '                                                  ', 48) +
           isnull(calc.text, 
                  t.name +
                  case when tc.format & 2 = 2 
                       then ' (' +convert (varchar, c.precision) +
                       case when tc.format & 2 = 2
                            then ', ' + convert (varchar, c.scale)
                            else ''
                       end + ')'
                       when tc.format & 1 = 1
                       then ' (' + convert (varchar, c.max_length) + ')'
                       else ''
                  end + ' ' + 
                  case when c.is_nullable <> 0 then 'null'
                       else 'not null'
                  end + isnull(ident.text, isnull(con.text, ''))) +
           case when c.column_id =
                (select max(c2.column_id)
                   from sys.columns c2
                  where c2.object_id = o.object_id)
                then char(10) + ')' + char(10) + 'go' + char(10)
                else ','
                end
      from sys.objects o
      join #objects o2
        on o.object_id = o2.object_id
      join sys.columns c
        on c.object_id = o.object_id
      join sys.types t
        on c.user_type_id = t.user_type_id
      left join 
           (select object_id,
                   column_id,
                   'as ' + definition as text
              from sys.computed_columns) calc
        on calc.object_id = o.object_id
       and calc.column_id = c.column_id
      left join
           (select parent_object_id,
                   parent_column_id,
                   ' default ' + definition as text
              from sys.default_constraints) con
        on con.parent_object_id = o.object_id
       and con.parent_column_id = c.column_id
      left join
           (select o.object_id,
                   col.column_id,
                   ' identity (' + convert(varchar, ident_seed(o.name)) + ', ' +
                                   convert(varchar, ident_incr(o.name)) + ')' as text
              from sys.objects o
              join sys.columns col
                on o.object_id = col.object_id
             where columnproperty (o.object_id, col.name, 'IsIdentity') = 1) as ident
        on ident.object_id = o.object_id
       and ident.column_id = c.column_id
      left join #types tc
        on tc.typename = t.name
     where o.type = 'U'
     order by o.name,
              c.column_id
    
    
    -- This generates view definitions 
    --
    select definition + char(10) + 'go' + char(10)
      from sys.sql_modules c
      join sys.objects o
        on c.object_id = o.object_id
      join #views o2
        on o.object_id = o2.object_id
    
    
    
    -- This generates PK and unique constraints
    --
    
    select case when ik.key_ordinal = 
                (select min(ik2.key_ordinal)
                    from sys.index_columns ik2
                   where ik2.object_id = ik.object_id
                     and ik2.index_id = ik.index_id)
                then 'alter table [' + rtrim (s.name) + '].[' + rtrim(t.name) + ']' + char(10) +
                     '  add constraint [' + rtrim (pk.name) + '] ' + 
                     case when pk.type = 'PK' then 'primary key'
                          when pk.type = 'UQ' then 'unique'
                          else 'foobar'
                          end + char(10) +
                     '      ('
                else '      ,'
                end +
                '[' + rtrim(c.name) + ']' +
            case when ik.key_ordinal =
                 (select max(ik2.key_ordinal)
                    from sys.index_columns ik2
                   where ik2.object_id = ik.object_id
                     and ik2.index_id = ik.index_id)
                 then ')' + char(10) + 'go' + char(10)
                 else ''
                 end
       from sys.objects t           -- table
       join #objects o
         on t.object_id = o.object_id
       join sys.schemas s
         on s.schema_id = t.schema_id
       join sys.objects pk          -- key
         on pk.parent_object_id = t.object_id
       join sys.columns c           -- columns
         on c.object_id = t.object_id
       join sys.indexes i           -- get index for constraint
         on i.object_id = t.object_id
        and i.name = pk.name
       join sys.index_columns ik        -- index column and name
         on ik.object_id = i.object_id
        and ik.index_id = i.index_id
        and ik.column_id = c.column_id     -- vvv Get the right index
      where c.name = index_col('[' + s.name + '].[' + t.name + ']', i.index_id, ik.key_ordinal)
        and pk.type in ('PK', 'UQ')   --probably redundant
      order by t.object_id,
               pk.object_id,
               ik.key_ordinal
    
    
    
    -- This generates indexes
    --
    select case when ik.key_ordinal = 
                 (select min(ik2.key_ordinal)
                    from sys.index_columns ik2
                   where ik2.object_id = ik.object_id
                     and ik2.index_id = ik.index_id)
                then 'create ' +
                case when is_unique_constraint = 1 then 'unique '
                     else ''
                     end +
                'index [' + rtrim(i.name) + ']' + char (10) +
                '    on [' + rtrim(t.name) + ']' + char (10) +
                '       ('
           else '       ,'
            end +
           '[' + c.name + ']' +
           case when ik.key_ordinal = 
                (select max(ik2.key_ordinal)
                   from sys.index_columns ik2
                  where ik2.object_id = ik.object_id
                    and ik2.index_id = ik.index_id)
                then ')' + char(10) + 'go' + char(10)
                else ''
                end
      from sys.objects t           -- table
      join #objects o
        on o.object_id = t.object_id
      join sys.columns c           -- columns
        on c.object_id = t.object_id
      join sys.indexes i           -- get index for constraint
        on i.object_id = t.object_id
      join sys.index_columns ik        -- index column and name
        on ik.object_id = i.object_id
       and ik.index_id = i.index_id
       and ik.column_id = c.column_id     -- vvv Get the right index
     where c.name = index_col(t.name, i.index_id, ik.key_ordinal)
       and t.type = 'U'
       and i.name <> t.name
       and i.name not in
           (select c2.name
              from sys.objects c2
             where c2.parent_object_id = t.object_id
               and c2.type in ('PK', 'UQ'))
     order by t.name,
              i.name,
              ik.key_ordinal
    
    
    -- This generates foreign keys
    --
    select con.constraint_text as [--constraint_text]
      from ((select case when kc.constraint_column_id = 
                         (select min(k2.constraint_column_id)
                            from sys.foreign_key_columns k2
                           where k2.constraint_object_id = k.object_id)
                         then 'alter table [' + @schema + '].[' + rtrim(t.name) + ']' + char(10) +
                              '  add constraint [' + rtrim (k.name) + '] ' + char(10) +
                              '      foreign key ('
                         else '                  ,'
                         end +
                    '[' + tc.name + ']' +
                    case when kc.constraint_column_id =
                         (select max(k2.constraint_column_id)
                            from sys.foreign_key_columns k2
                           where k2.constraint_object_id = k.object_id)
                         then ')' 
                         else ''
                         end as constraint_text,
                    t.name as table_name,
                    k.name as constraint_name,
                    kc.constraint_column_id as row_order,
                    t.object_id
               from sys.foreign_keys k
               join sys.objects t
                 on t.object_id = k.parent_object_id
               join sys.columns tc
                 on tc.object_id = t.object_id
               join sys.foreign_key_columns kc
                 on kc.constraint_object_id = k.object_id
                and kc.parent_object_id = t.object_id
                and kc.parent_column_id = tc.column_id
               join sys.objects r
                 on r.object_id = kc.referenced_object_id
               join sys.columns rc
                 on kc.referenced_object_id = rc.object_id
                and kc.referenced_column_id = rc.column_id)
            union all
            (select case when kc.constraint_column_id = 
                         (select min(k2.constraint_column_id)
                            from sys.foreign_key_columns k2
                           where k2.constraint_object_id = k.object_id)
                         then '      references [' + rtrim(r.name) + ']' + char(10) +
                              '                 ('
                         else '                 ,'
                         end +
                    '[' + rc.name + ']' +
                    case when kc.constraint_column_id = 
                         (select max(k2.constraint_column_id)
                            from sys.foreign_key_columns k2
                           where k2.constraint_object_id = k.object_id)
                         then ')' + char(10) + 'go' + char(10)
                         else ''
                         end as constraint_text,
                    t.name as table_name,
                    k.name as constraint_name,
                    kc.constraint_column_id + 100 as row_order,
                    t.object_id
               from sys.foreign_keys k
               join sys.objects t
                 on t.object_id = k.parent_object_id
               join sys.columns tc
                 on tc.object_id = t.object_id
               join sys.foreign_key_columns kc
                 on kc.constraint_object_id = k.object_id
                and kc.parent_object_id = t.object_id
                and kc.parent_column_id = tc.column_id
               join sys.objects r
                 on r.object_id = kc.referenced_object_id
               join sys.columns rc
                 on kc.referenced_object_id = rc.object_id
                and kc.referenced_column_id = rc.column_id)) con
      join #objects o
        on con.object_id = o.object_id
     order by con.table_name, 
              con.constraint_name, 
              con.row_order

    Vorrei capire se ci sono delle controindicazioni nell'eseguirlo durante le transazioni in corso.

    Grazie ancora dell'attenzione

    lunedì 4 maggio 2015 07:41
  • ciao,

    da come sembra, questo script genera solamente degli statement DDL per la creazione ed il drop di oggetti, non esegue nulla. Quindi puoi lanciarlo. In ogni modo, provalo prima su un tuo database locale (perchè lavora su un database) e noterai che i risultati sono semplicemente statement di CREATE TABLE, VIEW, ALTER TABLE, DROP, ecc.

    Ora, non sapendo a cosa può servirti in produzione, ti posso dire che è una serie di query che vanno in sola lettura e, agendo sul catalog, non dovrebbe bloccare l'esecuzione dei processi in essere.


    Alessandro Alpi - SQL Server MVP

    lunedì 4 maggio 2015 07:48
    Moderatore
  • per il momento ho accesso al server solo durante l'orario di lavoro è sto effettuando della reverse engineering per interfacciare il db ad un'altro applicativo.

    Ho provato su un DB locale e mi sono accorto appunto del risultato, purtroppo non ho un'elaborazione così lunga che "appende"delle transazioni mentre lo script è in esecuzione.

    Per cui  risulta essere uno script "non invasivo", quindi mi confermi che in pratica lo script non fa una drop table ma restituisce la stringa di testo da copiare e utilizzare come script per eseguire, in un secondo momneto, la drop-table ?



    • Modificato fantagruel lunedì 4 maggio 2015 09:04 riposta troppo frettolosa
    lunedì 4 maggio 2015 08:04
  • Da quel che vedo fa solo select, quindi, nella peggiore delle ipotesi, ci mette tanto se lo fai in produzione. Ma se è lanciato una volta sola, puoi farlo senza problemi. Tuttavia, se è reverse enginering, ma perchè non ti crei un db identico (tipo via diff o via backup restore) su un altro server in modo da non utilizzare la produzione?

    Alessandro Alpi - SQL Server MVP

    lunedì 4 maggio 2015 09:47
    Moderatore
  • Si ho pensato a farmi un backup, ma attualmente sto operando in remoto, la rete risulta essere assai lenta. Nel caso le procedure di backup e restore sono incluse nel Sql server management studio ? Grazie

    lunedì 4 maggio 2015 14:49
  • Non ti serve necessariamente sulla tua macchina, basta, se lo devi fare una volta, un backup su disco e un restore su un nuovo db, sempre sulla stessa macchina (nella peggiore delle ipotesi rallenti un po' il server, ma se non è molto usato, non dovresti nemmeno sentirlo).

    Con management studio hai le interfacce grafiche per effettuare operazioni di backup e restore. Puoi usare comandi oppure le finestre di SSMS che alla fine, lanciano quei comandi t-sql.


    Alessandro Alpi - SQL Server MVP

    lunedì 4 maggio 2015 15:04
    Moderatore
  • ok c'è una lieve complicazione:

    il Db di origine è di sql server 2000. Io uso il management studio si sql server 2014 da un altro server in LAN, per tale motivo l'operazione non è supportata.

    Purtroppo non ho un ambiente di gestione DB per sql server 2000.

    dovrei trovare un altro modo per fare il backup... ma poi posso restorarlo dal mgmnt 2014?
    lunedì 4 maggio 2015 15:52
  • Se devo essere sincero non ho mai provato a fare un restore da 2014 di un database 2000. Ma se lo restori su un db 2014, dovresti poterlo fare.

    Alessandro Alpi - SQL Server MVP

    martedì 5 maggio 2015 07:37
    Moderatore