En la primera entrega del Diccionario de Datos, vimos como obtener la estructura de una tabla y como actualizar las descripciones de las tablas y de las columnas. En esta entrega vamos a ver como podemos mejorar el contenido del diccionario, incluyendo no solo estructura y descripciones sino tambien características especiales de cada columna:
Esta vez no nos vamos a concentrar en las descripciones de tabla y columna, si no en las llaves y y otras restricciones. En el siguiente script estamos creando tablas muy parecidas a las del post anterior, pero con llaves primarias y foraneas, defaults, reglas y columnas calculadas (notar líneas resaltadas en el script):
para bajar el script completo haga clic aquí.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
--Creación de base de datos de prueba CREATE DATABASE PruebaDiccionario GO USE PruebaDiccionario GO --creación de la primera tabla de prueba CREATE TABLE dbo.Diccionario1 ( CodigoDic1 Smallint NOT NULL CONSTRAINT PK_Diccionario1 Primary KEY, --PK Nombre varchar(50) NOT NULL, Documento char(8) NOT NULL, Teléfono varchar(10) NULL, Notas varchar(max) NULL, Estado bit NOT NULL CONSTRAINT DF_Diccionario1_Estado DEFAULT (1)) --DEFAULT GO --creación de la segunda tabla de prueba Create Table dbo.Diccionario2 ( NumeroRegistro int NOT NULL CONSTRAINT PK_Diccionario2 Primary KEY, --PK CodigoDic1 Smallint NOT NULL CONSTRAINT FK_Diccionario2_Diccionario1 REFERENCES dbo.Diccionario1 (CodigoDic1), --FK FechaRegistro datetime NOT NULL CONSTRAINT DF_Diccionario2_FechaRegistro DEFAULT (SYSDATETIME()), --DEFAULT Cantidad tinyint NOT NULL, Precio decimal(15,4) CONSTRAINT CK_Diccionario2_Precio CHECK (Precio>0), --REGLA Descuento decimal(5,2) NULL, Monto AS ((Cantidad*Precio)-(Precio*Descuento)) PERSISTED) --CALCULADA GO; |
Ahora vamos a desarrollar la nueva consulta, para obtener una definición más completa. Lo primero que quiero recalcar es que estamos usan Common Table Expression (CTE), que es un mecanismo para generar datos temporales en el mismo query. A continuación la consulta:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
--consulta para obtener la definición de las tablas y sus descripciones WITH --Definición del CTE ColDef AS ( SELECT 'CALCULADA' As Tipo, object_id, column_id, Definition, null as name FROM sys.computed_columns UNION SELECT 'DEFAULT', parent_object_id, parent_column_id, definition, name FROM sys.default_constraints UNION SELECT 'CHECK', parent_object_id, parent_column_id, definition, name FROM sys.check_constraints UNION SELECT'PK', kc.parent_object_id, ic.column_id, null, name FROM sys.key_constraints kc JOIN sys.index_columns ic ON kc.parent_object_id = ic.object_id UNION SELECT 'FK', k.parent_object_id, c.parent_column_id, OBJECT_NAME(c.referenced_object_id)+'.'+COL_NAME (c.referenced_object_id,c.referenced_column_id),k.name FROM sys.foreign_keys k JOIN sys.foreign_key_columns c ON k.object_id = c.constraint_object_id) --Consulta SELECT SCHEMA_NAME(o.schema_id) AS Esquema, O.Name AS Tabla, P1.Value AS [Descripción Tabla], C.Name AS Columna, T.Name AS TipoDato, C.max_length AS Longitud, C.[Precision] AS Presición, C.scale AS Escala, CASE WHEN C.Is_Nullable = 0 Then 'No' ELSE 'Si' END [Nulo?], cd.Tipo As TipoRestricción, cd.definition AS Definición, P2.value AS [Descripción Col] FROM sys.tables O INNER JOIN sys.Columns C ON O.object_id = C.object_id INNER JOIN sys.Types T ON C.system_type_id = T.system_type_id AND C.system_type_id = T.user_type_id LEFT JOIN sys.extended_properties P1 ON C.object_id = P1.major_id AND P1.minor_id = 0 LEFT JOIN sys.extended_properties P2 ON C.object_id = P2.major_id AND C.Column_id = P2.minor_id AND P2.Class = 1 LEFT JOIN ColDef cd ON O.object_id = cd.object_id AND C.column_id = cd.column_id ORDER BY O.Name, C.Column_id GO |
En el CTE obtenemos los diferentes tipos de llaves y restricciones de todas las tablas y los unimos en un solo conjunto de datos. Luego en la consulta unimos la estructura y descripción de las tablas con el contenido del CTE, obteniendo el sigiuente resultado:
NOTA: Se han omitido las columnas de descripción para visualizar mejor el resultado
A la derecha tenemos las columnas de tipo de restricción, La definición de la restricción y el nombre de la restricción:
- PK: No incluye la definición por que solo indica que esa columna forma parte de la llave primaria.
- FK: Indica que ese columna forma parte de la llave foranea y en la definición indica la tabla y el campo al que hacen referencia.
- DEFAULT: Indica que la columna tiene un default y cual es el valor configurado.
- CHECK: Indica que la columna tiene una regla y cual es la regla configurada.
- CALCULADA: Indica que la columna es calculada y cual es la expresión de cálculo.