none
SQLSERVER2005 Eliminar varias tablas ala vez RRS feed

  • Pregunta

  • Hola a todos:
    Restaure un bk de base de datos con mas de 10 000 (diez mil)tablas, es muy pesado, requiero eliminar alrededor de 9000 de ellas, bajo cierto criterio, por ejemplo aquellas tablas cuyo nombre empicen con 'r01' o aquelas que terminen con '2004', etc;
    Estuve investigando sobre el sp_help y el sys.sysobjects , este ultimo me resulta muy util, pretendo armar un procedimiento con instrucciones drop que filtre de acuerdo a lo encontrado en el sys.sysobjects ;
    Si alguno tiene un methodo mucho mas sencillo u algun consejo, por favor.

    Gracias.

    jueves, 18 de junio de 2009 17:14

Respuestas

  • Puedes usar un cursor para atravesar el resultado de todos los nombres de tablas en tu db, y generar una sentencia "drop table" dinamica. Tambien puedes usar un procedimiento no documentado (por favor no escribir codigo de produccion basado en facilidades no documentadas) llamado sp_MSforeachtable.

    Ejemplo:


    USE tempdb;
    GO
    
    CREATE TABLE t1_2004(c1 int);
    CREATE TABLE t2_2004(c1 int);
    GO
    
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    GO
    
    EXEC sys.sp_MSforeachtable N'if parsename(''?'', 1) like ''%_2004'' begin print ''?''; drop table ?; end';
    GO
    
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    GO
    
    CREATE TABLE t1_2004(c1 int);
    CREATE TABLE t2_2004(c1 int);
    GO
    
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    GO
    
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @tn NVARCHAR(256);
    DECLARE @c CURSOR;
    
    SET @c = CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    
    OPEN @c;
    
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM @c INTO  @tn;
    	
    	IF @@ERROR <> 0 OR @@FETCH_STATUS <> 0
    		BREAK;
    	
    	SET @sql = N'drop table ' + @tn;
    	
    	PRINT @sql;
    	EXEC sp_executesql @sql;	
    END
    GO
    
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    GO


    Fijate que ninguno de los dos metodos chequea si la tabla que se esta eliminando es referenciada por alguna restriccion de clave foranea, en cuyo caso la sentencia "drop table" dara error. En caso de que tus tablas tengan restricciones de clave foranea, entonces habria que cambiar el script y la cosa se pone mas complicada.

    AMB

    jueves, 18 de junio de 2009 18:40

Todas las respuestas

  • Podrias hacer algo parecido a esto.
    Te falta agregar el esquema.


    use

     

    AdventureWorks

    go

    Declare

     

    @SQL as varchar(max)

    set

     

    @SQL = ''

    select

     

    @SQL =@SQL + 'drop table ' + name + '; GO ' + CHAR(13)+ CHAR(10)

    from

     

    sys.objects

    where

     

    type = 'U'

    --and <agregar condicion de eliminacion>

    select

     

    @SQL

    -- reemplazar por exec

    --exec(@SQL )




    Saludos



    Ing. Jose Mariano Alvarez http://blog.josemarianoalvarez.com/ Microsoft MVP SQLTotal Consulting Mi.Correo.es.j0se.marian0.alvarez@gmail.c0m.Corregirl0 (Cambia los ceros por O y saca lo que sobra) Este mensaje se proporciona tal como es, SIN GARANTIAS de ninguna clase
    jueves, 18 de junio de 2009 18:28
  • Puedes usar un cursor para atravesar el resultado de todos los nombres de tablas en tu db, y generar una sentencia "drop table" dinamica. Tambien puedes usar un procedimiento no documentado (por favor no escribir codigo de produccion basado en facilidades no documentadas) llamado sp_MSforeachtable.

    Ejemplo:


    USE tempdb;
    GO
    
    CREATE TABLE t1_2004(c1 int);
    CREATE TABLE t2_2004(c1 int);
    GO
    
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    GO
    
    EXEC sys.sp_MSforeachtable N'if parsename(''?'', 1) like ''%_2004'' begin print ''?''; drop table ?; end';
    GO
    
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    GO
    
    CREATE TABLE t1_2004(c1 int);
    CREATE TABLE t2_2004(c1 int);
    GO
    
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    GO
    
    DECLARE @sql NVARCHAR(MAX);
    DECLARE @tn NVARCHAR(256);
    DECLARE @c CURSOR;
    
    SET @c = CURSOR LOCAL FAST_FORWARD
    FOR
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    
    OPEN @c;
    
    WHILE 1 = 1
    BEGIN
    	FETCH NEXT FROM @c INTO  @tn;
    	
    	IF @@ERROR <> 0 OR @@FETCH_STATUS <> 0
    		BREAK;
    	
    	SET @sql = N'drop table ' + @tn;
    	
    	PRINT @sql;
    	EXEC sp_executesql @sql;	
    END
    GO
    
    SELECT QUOTENAME(SCHEMA_NAME([schema_id])) + N'.' + QUOTENAME([name])
    FROM sys.tables
    WHERE [name] LIKE '%_2004';
    GO


    Fijate que ninguno de los dos metodos chequea si la tabla que se esta eliminando es referenciada por alguna restriccion de clave foranea, en cuyo caso la sentencia "drop table" dara error. En caso de que tus tablas tengan restricciones de clave foranea, entonces habria que cambiar el script y la cosa se pone mas complicada.

    AMB

    jueves, 18 de junio de 2009 18:40