Cómo generar sentencias SQL de administración para eliminar tablas y vistas

Los metadatos que guardan las bases de datos sobre la estructura de sus objetos son muy útiles para realizar tareas que requieran hacer algo sobre todos los objetos de un esquema, de una base de datos, de un tipo determinado, con un patrón en el nombre del objeto, etc.

En SQL Server, con las vistas que la base de datos nos da sobre el catálogo podemos consultar, entre otras muchas cosas, los nombres de objetos de las bases de datos como tablas o vistas.

Si lo que queremos hacer es eliminar todas las tablas y vistas de un determinado esquema de una base de datos 'DBName', por ejemplo, conectados a DBName o incluyendo el nombre de la base de datos en la consulta, podemos consultar en las vistas de sistema de SYS.OBJECTS y SYS.SCHEMA de objetos y esquemas, respectivamente, para construir nuestras sentencias de DROP Table en un segundo.

Consulta de ejemplo para generar DROPS de las tablas y vistas de dos esquemas

select 'DROP ' 
       + CASE type WHEN 'U' THEN 'Table' WHEN 'V' THEN 'View' END 
       + ' [' + sc.name + '].[' + ob.name + '];' 
from DBName.sys.objects OB
join DBName.sys.schemas SC on OB.schema_id=SC.schema_id
where sc.name in ('dbo','USER')
and ob.type in ('U','V')
order by ob.type, ob.name

Esta sentencia nos devuelve montadas las consultas para todas las tablas y vistas que contengan los esquemas incluídos en el IN, en este caso 'dbo' y 'User', de la base de datos con mobre 'DBName'.

Después sólo es cuestión de copiar las queries generadas, revisarlas, sobretodo teniendo en cuenta que son DROPs, e incluirlas en nuestro script, o ejecutarlas directamente desde el SSMS, por ejemplo.

Tabla de codificación para tipos de objeto de SYS.OBJECTS

Como ayuda, esta es la codificación de los tipos de objetos que podemos encontrarnos en la vista sys.objects, en el campo 'type'. En nuestro caso hemos filtrado por 'Tablas de usuario' y 'Vistas' con 'U' y 'V'.

Una alternativa, generar SQL con INFORMATION_SCHEMA

Utilizando las vistas de INFORMATION_SCHEMA se podría conseguir lo mismo y con una consulta más sencilla, pero mejor ir acostumbrándose a las vistas de sys, que son las más fiables, y las que parece que van a quedarse a la larga.

Ejemplo para crear DROP de tablas dinámico con INFORMATION_SCHEMA

SELECT
  'DROP ' + right(table_type,5) + ' [' + TABLE_SCHEMA + '].[' + TABLE_NAME + '];'
FROM
 DBName.INFORMATION_SCHEMA.TABLES
where table_schema not in ('dbo','USER')
order by table_schema, table_type, table_name

Las vistas del catálogo te pueden ahorrar mucho tiempo

Esta es una aplicación típica que pongo como ejemplo, pero utilizar las vistas del catálogo para generar consultas dinámicamente nos puede ahorrar un montón de tiempo y asegurarnos de que no nos dejamos nada en muchas tareas de administración de la base de datos que requieran consultar o realizar acciones sobre grupos de objetos.

Por último, en el post de Consultas Útiles para SQL Server hay otras consultas sobre el catálogo, que combinadas con esta manera de generar queries de administración pueden ahorrar mucho trabajo y facilitar la administración o el desarrollo con SQL Server.