Se nos fue un grande del diseño y de la vida. Un maestro.
Estimados, hoy vamos a compartir un script que puede resultar muy útil al momento de generar un diccionario de datos para SQL Server.

SQL-Server-Select
SQL-Server-Select

El diccionario de datos se utiliza normalmente para documentar el modelo de una base de datos, indicando las tablas, columnas y tipos de datos de estas, lo cual puede ser muy beneficioso para la capacitación de nuevo personal o para externalizar un desarrollo puntual que deba interactuar con algún sistema ya existente.
Lamentablemente, SQL Server no permite generar el diccionario de datos de una base de datos ya existente y puede resultar muy fastidioso ir documentando tabla por tabla todo el modelo, más aún si este es extenso.
Afortunadamente, SQL Server guarda toda la información de nuestro modelo en tablas propias del sistema. Aquí podremos encontrar el código de vistas, procedimientos almacenados, tablas, columnas, tipos de datos y todo tipo de información asociada a su modelo.
Entonces, no es difícil suponer que utilizando las tablas y filtros adecuados, podamos extraer la información necesaria para generar nuestro diccionario de datos. De hecho, en la web existen diferentes script que permiten generar esta información, pero lamentablemente no he logrado dar con un script “definitivo” que entregue la información que necesito en un 100%.

 

Manos a la obra

De esta manera, en ENTRAR.CL nos propusimos generar nuestro propio script, el cual se basa en el uso de las siguientes tablas:
• sys.tables: Contiene un listado con las tablas
• sys.columns: Contiene un listado con las columnas
• sys.systypes: Contiene un listado con los tipos de dato (nvarchar, int, etc)
• sys.extended_properties: Contiene los comentarios que puedan existir en cada columna
• sys.indexes: Contiene un listado de indices
• sys.index_columns: Contiene un listado de indices asociados a columnas de las tablas

Teniendo en claro desde donde extraer la información, el resto del proceso es muy simple. Solo debemos cruzar las tablas ya mencionadas y aplicando un par de filtros para sacar algunos resultados que no deseamos, podríamos tener algo similar a lo siguiente:

select	T.name as tabla, 
		C.name as columna, 
		TYP.name as tipo, 
		case when C.is_nullable=0 then 'Falso' else 'Verdadero' end as EsNulo,
		case when C.is_identity=0 then 'Falso' else 'Verdadero' end as EsAutonumerico,				
		case when IND.is_unique=0 then 'Falso' else 'Verdader' end as EsUnico,
		case when IND.is_primary_key=0 then 'Falso' else 'Verdadero' end as EsPrimaryKey,
		COMTS.value as descripcion		
		,C.*
from sys.tables T
inner JOIN sys.columns C on T.object_id=C.object_id
INNER JOIN sys.systypes TYP on TYP.xtype=C.system_type_id
left JOIN sys.extended_properties COMTS ON COMTS.major_id=C.object_id and COMTS.minor_id=C.column_id
-- Considerando que una columna puede estar asociado a más de un indice,
-- nos aseguramos de traer un solo registro por columna para no duplicar
-- los registros.
left join (
	select	I.object_id, 
			IC.column_id,
			min(I.index_id) as index_id, 
			max(cast(I.is_unique as int)) as is_unique, 
			max(cast(I.is_primary_key as int)) as is_primary_key
	from sys.indexes I 
	inner join sys.index_columns IC on I.object_id=IC.object_id and I.index_id=IC.index_id
	group by I.object_id, IC.column_id
) IND on IND.object_id=C.object_id and IND.column_id=C.column_id
WHERE T.name<>'sysdiagrams' and TYP.name<>'sysname'
order by T.name, C.column_id

 

Resumen

Este script fue probado con SQL Server Express 2012. Esperamos que esta entrada les sea de utilidad. Siéntanse con la libertad de modificarlo y utilizarlo como gusten.
Hasta la próxima! 🙂

No hay comentarios