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'

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'.

Tabla de codificación para tipos de objeto de SYS.OBJECTS de SQL Server
Tipo de objeto:

AF = Función de agregado (CLR)
C = restricción CHECK
D = DEFAULT (restricción o independiente)
F = Restricción FOREIGN KEY
FN = Función escalar de SQL
FS = Función escalar del ensamblado (CLR)
FT = Función con valores de tabla de ensamblado (CLR)
IF = Función SQL insertada con valores de tabla
IT = tabla interna
P = Procedimiento almacenado de SQL
PC = Procedimiento almacenado del ensamblado (CLR)
PG = Guía de plan
PK = Restricción PRIMARY KEY
R = Regla (estilo antiguo, independiente)
RF = Procedimiento de filtro de replicación
S = Tabla base del sistema
SN = Sinónimo
SO = Objeto de secuencia
U = Tabla (definida por el usuario)
V = Vista
EC = restricción perimetral

Válido para : SQL Server 2012 (11.x) y versiones posteriores.
SQ = Cola de servicio
TA = Desencadenador DML del ensamblado (CLR)
TF = Función con valores de tabla SQL
TR = Desencadenador DML de SQL
TT = Tipo de tabla
UQ = Restricción UNIQUE
X = Procedimiento almacenado extendido

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores, Azure SQL Database , Azure Synapse Analytics (SQL Data Warehouse) , Almacenamiento de datos paralelos .
ET = tabla externa

 

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.