Vistas del sistema en SQL Server

Vistas del sistema 

Consultando los metadatos en SQL Server

Las Vistas del sistema son vistas integradas que proporcionan información sobre el catálogo del sistema.
Se utililiza la instrucción Select para visualizar los metadatos, es decir, listar los elementos de la base de datos como: tablas, columnas, vistas, procedimientos almacenados, funciones definidas por el usuario, restricciones, archivos de base de datos, grupos de archivos de la base de datos, certificados, etc.

Los metadatos se pueden visualizar desde los elementos que se encuentran en los esquemas sys así como en el esquema llamado INFORMATION_SCHEMA.
En el explorador de objetos del SQL Server Management Studio se pueden ver las vistas del sistema desde el nodo Vistas y dentro de este Vistas del sistema.

En este artículo vamos a mostrar como ver los siguientes objetos:

  • Tablas
  • Vistas
  • Columnas
  • Procedimientos almacenados
  • Tipos de datos definidos por el usuario
  • Índices
  • Restricciones
  • Triggers
  • Bases de datos
  • Archivos de bases de datos
  • Grupos de archivos
  • Secuencias
  • Ensamblados
  • Certificados
  • Roles de servidor
  • Roles de base de datos
  • Inicios de sesión
  • Usuarios de base de datos

El visualizar o listar los objetos de la base de datos se utilizan para crear scripts de manera adecuada para que no reporte error al intentar crear o modificar un objeto, por ejemplo, buscar si existe un índice, si existe sobre escribirlo y si no existe se crea.

El ejemplo muestra como buscar en la vista de sistema sys.indexes si el índice con nombre ProductosDescripcionIDXa no existe, si la expresión es verdadera, crea el índice, si es falsa, es decir, el índice existe, crea el índice pero sobre escribe el existente. (Ver índices)

Use Northwind
go
if not exists (select name from sys.indexes where name = ‘ProductosDescripcionIDXa’)
Begin — el índice no existe
Create index ProductosDescripcionIDXa on Products (ProductName)
with fillfactor = 80
End
Else
Begin
Create index ProductosDescripcionIDXa on Products (ProductName)
with (fillfactor = 80, drop_existing = on)
End
go

Note que en el código anterior se ha utilizado como expresión condicional el listado de los índices desde la vista del sistema sys.indexes.

Ejemplos

Usando la base de datos Northwind
use Northwind
go
Para listar las tablas (Ver creación de tablas)
SELECT T.name As ‘Nombre’, T.create_date As ‘Fecha creación’ ,
T.schema_id As ‘Esquema’, T.type_desc As ‘Tipo’
FROM sys.tables As T
go

Se puede usar el esquema Information_Schema
select * from INFORMATION_SCHEMA.TABLES As I
where I.TABLE_TYPE = ‘BASE TABLE’
go

Para contar la cantidad de tablas
select COUNT(t.name) As ‘Cantidad de tablas’
from sys.tables As T
go

Visualizar las vistas

(Ver vistas) (Ver Vistas indizadas)
select * from sys.views
go

Las vistas desde el esquema INFORMATION_SCHEMA
select * from INFORMATION_SCHEMA.TABLES As I
where I.TABLE_TYPE = ‘VIEW’
go
Para contar la cantidad de vistas
select COUNT(V.name) As ‘Cantidad de vistas’
from sys.views As V
go

Visualizar los Store Procedures (Ver procedimientos almacenados)
select * from sys.procedures
go

Las rutinas, que pueden ser procedimientos almacenados o funciones definidas por el usuario.
Los procedimientos se pueden visualizar con el siguiente select
SELECT SPECIFIC_CATALOG As ‘Base de datos’, SPECIFIC_NAME As ‘Nombre’,
ROUTINE_DEFINITION As ‘Texto’
from INFORMATION_SCHEMA.ROUTINES
where ROUTINE_TYPE = ‘PROCEDURE’
go

Note que al usar la vista Routines se puede ver la definición del procedimiento almacenado.

Para visualizar los parámetros de un procedimiento almacenado
select * from sys.parameters
go
Un procedimiento para insertar un registro en Shippers
Create procedure spShippersInsertar
(@ShipperNombre nvarchar(40),
@ShipperFono nvarchar(24))
As
insert into Shippers values (@ShipperNombre, @ShipperFono)
go

Para visualizar la lista de procedimientos almacenados con sus parámetros
select SP.name As ‘Procedimiento’, PA.name As ‘Parámetro’,
T.name As ‘Tipo de dato’
from sys.procedures As SP
join sys.parameters As PA on SP.object_id = PA.object_id
join sys.types As T on PA.system_type_id = T.system_type_id
where T.name <> ‘sysname’ and T.is_user_defined = 0
order by SP.name
go

Visualizar los tipos de datos definidos por el usuario

(Ver Tipos definidos por el usuario – UDF)
Crearemos algunos
Create type Codigo10 from nchar(10) not null
Create type Texto100 from nvarchar(10) not null
Create type Fecha from Date
go
Ahora si el listado
select * from sys.types As Ti
where ti.is_user_defined = 1
go

Visualizar las columnas de las tablas
Las columnas de todos los objetos de la base de datos
select * from sys.all_columns
go
Las columnas y los objetos a los que pertenecen, estos objetos pueden ser:
Tablas del sistema, tablas internas, tablas de usuario, funciones definidas por el usuario tipo tabla y vistas.
select distinct
O.type_desc As ‘Tipo de objeto’
from sys.all_columns As C
join sys.objects As O on C.object_id = O.object_id
go

Las columnas se muestran en el siguiente listado
select O.name As ‘Objeto’, C.name As ‘Columna’,
O.type_desc As ‘Tipo de objeto’
from sys.all_columns As C
join sys.objects As O on C.object_id = O.object_id
go

Las columnas de la base de datos abierta
select * from INFORMATION_SCHEMA.COLUMNS
go
Las columnas de la tabla Shippers
select * from INFORMATION_SCHEMA.COLUMNS
where TABLE_NAME = ‘Shippers’
go

Visualizar las columnas con la propiedad Identity (Ver Identity)
select * from sys.columns where is_identity = 1
go
Visualizar las columnas con la propiedad Identity y la tabla
select C.name As ‘Columna’, T.name As ‘Tabla’
from sys.columns As C
join sys.tables As T on C.object_id = T.object_id
where is_identity = 1
go

Para visualizar todos los indices (Ver índices)
select * from sys.indexes
go
Indices agrupados
select * from sys.indexes where type = 1
go
Indices no agrupados
select * from sys.indexes where type = 2
go
Indices y sus tablas, además del tipo.
select I.name As ‘Nombre índice’, T.name As ‘Tabla’,
iif(I.type = 1,’Agrupado’,’No agrupado’) As ‘Tipo’
from sys.indexes As I
join sys.tables As T on I.object_id = T.object_id
where I.name is not null
go

RESTRICCIONES EN LAS TABLAS

(Ver uso de restricciones)

Las restriciones permiten un diseño adecuado de la base de datos, se puede asegurar la integridad de los datos y que los valores ingresados en los campos cumplan con ciertas reglas. (Ver restricciones)
Para visualizar la lista completa de las restricciones en la base de datos abierta.
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
go

Se puede filtrar para poder saber si existe una restricción en una determinada tabla. Por ejemplo para listar las restricciones en la tabla Employees se puede usar la siguiente instrucción
select * from INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE
where TABLE_NAME = ‘Employees’
go
Para visualizar las tablas donde se han definido restricciones
select * from INFORMATION_SCHEMA.CONSTRAINT_TABLE_USAGE
go
La diferencia con la instrucción de la vista INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE es que en esta
se muestra el nombre de la columna en la que se ha definido una restricción.

Para visualizar las claves primarias
select * from sys.key_constraints
where type = ‘PK’
go
Las claves primarias incluyendo los nombres de las tablas
select T.name As ‘Tabla’, k.name As ‘Clave primaria’
from sys.tables As T
join sys.key_constraints As k on T.object_id = K.parent_object_id
where K.type = ‘PK’
go

Para visualizar las restricciones de tipo Unique
select * from sys.key_constraints
where type = ‘UQ’
go
Las restricciones Unique incluyendo los nombres de las tablas
select T.name As ‘Tabla’, k.name As ‘Restricción Unique’
from sys.tables As T
join sys.key_constraints As k on T.object_id = K.parent_object_id
where K.type = ‘UQ’
go

Para visualizar las claves foráneas
select * from sys.foreign_keys
go
También desde el esquema INFORMATION_SCHEMA
select * from INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS
go
Las restricciones Foreign key incluyendo los nombres de las tablas
select T.name As ‘Tabla’, F.name As ‘Foreign Key’,
(select name from sys.tables As Ta
where Ta.object_id = F.referenced_object_id)
As ‘Tabla referenciada – PK’
from sys.foreign_keys As F
join sys.tables As T on F.parent_object_id = T.object_id
go

Para visualizar las restricciones de tipo Check
select * from sys.check_constraints
go
Las restricciones Check incluyendo los nombres de las tablas
select T.name As ‘Tabla’, C.name As ‘Restricción Check’,
C.definition As ‘Definición’
from sys.tables As T
join sys.check_constraints As C on T.object_id = C.parent_object_id
go

Para visualizar las restricciones de tipo Default
select * from sys.default_constraints
go
Las restricciones Default incluyendo los nombres de las tablas
select T.name As ‘Tabla’, D.name As ‘Restricción Default’,
D.definition As ‘Definición’
from sys.tables As T
join sys.default_constraints As D on T.object_id = D.parent_object_id
go

Triggers DML (Ver Triggers)

Ejecutar los siguientes ejemplos para visualizarlos en el listado

Create trigger dbo.triggerRegionInsertar on dbo.Region
with encryption
for insert
As
Begin
Print ‘Registro insertado… mensaje desde Trigger’
End
go
Create trigger triggerCategoriasNombreUnico on Categories
for insert, update
As
Begin
if (select Count(*) from Categories As C, inserted As I
where C.CategoryName = I.CategoryName) > 1
Begin
Rollback transaction
Print ‘Inserción o actualización anulada desde el Trigger’
End
End
go
Trigger para guardar historial de cambios.
Create table HistorialShippers
(
HistorialShippersCodigo nchar(10),
HistorialShippersNombre nvarchar(40),
HistorialShippersFono nvarchar(24),
HistorialShippersFecha Date
)
go
Create trigger triggerShippersHistorial on Shippers
for insert, update
As
Begin
Insert into HistorialShippers
select inserted.ShipperID, inserted.CompanyName,
inserted.Phone, GetDate()
from inserted
End
go
Tabla ciudades y Trigger para los cambios
Crear tabla Ciudades
Create table Ciudades
(
CiudadesCodigo nchar(4),
CiudadesNombre nvarchar(100),
constraint CiudadesPK Primary key (CiudadesCodigo)
)
go
Create table HistorialCiudades
(
HistorialCiudadesCodigo nchar(4),
HistorialCiudadesNombre nvarchar(100),
HistorialCiudadesOperacion nvarchar(15),
HistorialFecha DateTime,
HistorialEstado nvarchar(10)
)
go
— Trigger para insertar
Create trigger triggerCiudadesInsertar on Ciudades for insert
As Begin
insert into HistorialCiudades
select inserted.CiudadesCodigo, inserted.CiudadesNombre,
‘inserción’, Getdate(), ‘insertado’ from inserted
End
go
— Trigger para actualización
Create trigger triggerCiudadesActualizacion on Ciudades for update
As Begin
insert into HistorialCiudades
select deleted.CiudadesCodigo, deleted.CiudadesNombre,
‘modificacion’, Getdate(), ‘original’ from deleted
insert into HistorialCiudades
select inserted.CiudadesCodigo, inserted.CiudadesNombre,
‘modificacion’, Getdate(), ‘cambiado’ from inserted
End
go

select * from sys.triggers
go
Las triggers incluyendo los nombres de las tablas
select Ta.name As ‘Tabla’, Tr.name As ‘Trigger’
from sys.tables As Ta
join sys.triggers As Tr on Ta.object_id = Tr.parent_id
go
Los eventos por los que se disparan los triggers
select Ta.name As ‘Tabla’, Tr.name As ‘Trigger’, E.type_desc As ‘Evento’
from sys.tables As Ta
join sys.triggers As Tr on Ta.object_id = Tr.parent_id
join sys.trigger_events As E on Tr.object_id = E.object_id
go

Ver las bases de datos de la instancia (Ver Creación de BD)

select * from sys.databases
go
Archivos de la base de datos abierta
select * from sys.database_files
go
Grupos de Archivos de la base de datos abierta (Ver Grupos de archivos)
select * from sys.filegroups
go
Los archivos y los grupos
select D.name As ‘Nombre de archivo’,
D.physical_name As ‘En el disco’,
G.name As ‘Grupo’
from sys.database_files As D
join sys.filegroups As G on D.data_space_id = G.data_space_id
go

Secuencias (Ver secuencias)
select * from INFORMATION_SCHEMA.SEQUENCES
go
También desde el esquema sys
select * from sys.sequences
go
Los ensamblados
select * from sys.assemblies
go

Los certificados

(Ver Certificados en SQL Sever)

Primero creamos un certificado.
set dateformat dmy
go
Create certificate TrainerCertificado
encryption by password = ‘TSQLCertificadoSP’
with subject = ‘Certificado Trainer’,
Expiry_date = ’15/05/2025′
go
Listado de los certificados
select * from sys.certificates
go

Funciones de servidor (Ver funciones de servidor)
Funciones fijas de servidor
select * from sys.server_principals
where type = ‘R’ and is_fixed_role = 1
go
Funciones de servidor creadas por el usuario
select * from sys.server_principals
where type = ‘R’ and is_fixed_role = 0
go
Inicios de sesión (Ver Logins)
select * from sys.sql_logins
go
Los inicios de sesión y los roles de servidor
select P.name As ‘Rol de servidor’, L.name As ‘Login’
from sys.server_principals As P
join sys.server_role_members As R on P.principal_id = R.role_principal_id
join sys.sql_logins As L on R.member_principal_id = L.principal_id
go
Funciones de base de datos (Ver funciones de base de datos)
Funciones fijas de base de datos
select * from sys.database_principals
where type = ‘R’ and is_fixed_role = 1
go
Funciones de base de datos creadas por el usuario
select * from sys.database_principals
where type = ‘R’ and is_fixed_role = 0
go
Usuarios de base de datos (Ver Usuarios de base de datos)
select * from sys.database_principals
where type = ‘S’
go
Los usuarios de base de datos y las funciones de bases de datos
select D.name As ‘Rol de base de datos’,
U.name As ‘Usuario de base de datos’
from sys.database_principals As D
join sys.database_role_members As M on D.principal_id = M.role_principal_id
join sys.database_principals As U on M.member_principal_id = U.principal_id
go