Script para generar el diccionario de datos

/*****************/
Actualización 21 de octubre de 2014
*Adjunto el archivo SQL para facilitar su utilizacion

Diccionario.sql


/*****************/


Hola, una de las cosas por las que uno como freelancer tiene que pasar es por entregar una documentación adecuada de sus proyectos, y una parte de esta documentación es el diccionario de datos, y la mejor forma de hacerlo (En SQL Server 2005 o superior) , es:

1. Al crear la tablas, campos, y vistas utilizar el la propiedades la descripción de dicho objeto:

Una ventaja de esta opción, es que si se hace un backup, o si se genera un script del objeto o de la base de datos, la descripción de este siempre estará incluida, otra ventaja, es que es fácilmente actualizable el diccionario pues solo es necesario actualizar esta propiedad cuando sea conveniente. 

2. Una vez diseñada la base de datos se debe abrir el siguiente script 

--Cut and paste the results to note pad and name with a *.html format when saving.


--//SQL Database documentation script
--//Author: Nitin Patel, Email: nitinpatel31@gmail.com
--//Modificado por :Robinson Moscoso, Email: robinmp@gmail.com
--//Date:26-Jun-2013
--//Description: T-SQL script to generate the database document for SQL server 2005
Declare @i Int, @maxi Int
Declare @j Int, @maxj Int
Declare @sr int
Declare @Output varchar(4000)
Declare @SqlVersion varchar(5)
Declare @last varchar(155), @current varchar(255), @typ varchar(255), @description varchar(4000)
Declare @NombreTabla nvarchar(100)


--crear la tabla donde se van a guardar los tamaños
declare @tamanio as table (objname varchar(255),rows varchar (100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100))


create Table #Tables (id int identity(1, 1), Object_id int, Name varchar(155), Type varchar(20), [description] varchar(4000))
create Table #Columns (id int identity(1,1), Name varchar(155), Type Varchar(155), Nullable varchar(2), [description] varchar(4000),[valor] varchar(4000))
create Table #Fk(id int identity(1,1), Name varchar(155), col Varchar(155), refObj varchar(155), refCol varchar(155))
create Table #Constraint(id int identity(1,1), Name varchar(155), col Varchar(155), definition varchar(1000))
create Table #Indexes(id int identity(1,1), Name varchar(155), Type Varchar(25), cols varchar(1000))
create Table #Procedure(id int identity(1,1), Shema varchar(50), [Procedure] Varchar(100), CreadoEl varchar(100), UltimaModificacion varchar(100))


If (substring(@@VERSION, 1, 25 ) = 'Microsoft SQL Server 2005')
set @SqlVersion = '2005'
else if (substring(@@VERSION, 1, 26 ) = 'Microsoft SQL Server 2000')
set @SqlVersion = '2000'
else
set @SqlVersion = '2005'
Print ''
Print ''
Print ''
Print ''
Print ''


set nocount on
if @SqlVersion = '2000'
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2000
select object_id(table_name), '[' + table_schema + '].[' + table_name + ']',
case when table_type = 'BASE TABLE' then 'Table' else 'View' end,
cast(p.value as varchar(4000))
from information_schema.tables t
left outer join sysproperties p on p.id = object_id(t.table_name) and smallid = 0 and p.name = 'MS_Description'
order by table_type, table_schema, table_name
end
else
if @SqlVersion = '2005'
begin
insert into #Tables (Object_id, Name, Type, [description])
--FOR 2005
Select o.object_id, '[' + s.name + '].[' + o.name + ']',
case when type = 'V' then 'View' when type = 'U' then 'Table' end,
cast(p.value as varchar(4000))
from sys.objects o
left outer join sys.schemas s on s.schema_id = o.schema_id
left outer join sys.extended_properties p on p.major_id = o.object_id and minor_id = 0 and p.name = 'MS_Description'
where type in ('U', 'V')
order by type, s.name, o.name;
end
Set @maxi = @@rowcount
set @i = 1
print '

Index

'
print ' '


While(@i <= @maxi)
begin
select @Output = ' '
from #Tables where id = @i

print @Output
set @i = @i + 1
end
print '

Sr	Object	Type
' + Cast((@i) as varchar) + '	' + name + '	' + Type + '

'


set @i = 1
While(@i <= @maxi)
begin
--table header
select @NombreTabla = name from #Tables where id = @i
delete from @tamanio
insert into @tamanio
exec sp_spaceused @NombreTabla


select @Output = ' ' + Type + ':' + name + ' ', @description = [description]
from #Tables where id = @i
print '


'
print @Output
print '

Index

'
print ' '
select @Output = ' '
from @tamanio
print @Output
print '

Nro Filas	Reservado	Tamaño de los Datos	Tamaño de los Indices	No Usado
'+ rows +'	'+ reserved +'	' + data + '	' + index_size + '	' + unused+ '

'

print '

Description

' + isnull(@description,'') + '

'


--table columns
truncate table #Columns
if @SqlVersion = '2000'
begin
insert into #Columns (Name, Type, Nullable, [description], valor)
--FOR 2000
Select c.name,
type_name(xtype) + (
case when (type_name(xtype) = 'varchar' or type_name(xtype) = 'nvarchar' or type_name(xtype) ='char' or type_name(xtype) ='nchar')
then '(' + cast(length as varchar) + ')'
when type_name(xtype) = 'decimal'
then '(' + cast(prec as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when isnullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(8000)),
''
from syscolumns c
inner join #Tables t on t.object_id = c.id
left outer join sysproperties p on p.id = c.id and p.smallid = c.colid and p.name = 'MS_Description'
where t.id = @i
order by c.colorder
end
else
if @SqlVersion = '2005'
begin
insert into #Columns (Name, Type, Nullable, [description],valor)
--FOR 2005
Select c.name,
type_name(user_type_id) + (
case when (type_name(user_type_id) = 'varchar' or type_name(user_type_id) = 'nvarchar' or type_name(user_type_id) ='char' or type_name(user_type_id) ='nchar')
then '(' + cast(max_length as varchar) + ')'
when type_name(user_type_id) = 'decimal'
then '(' + cast([precision] as varchar) + ',' + cast(scale as varchar) + ')'
else ''
end
),
case when is_nullable = 1 then 'Y' else 'N' end,
cast(p.value as varchar(4000)),
cast(p1.value as varchar(4000))
from sys.columns c
inner join #Tables t on t.object_id = c.object_id
left outer join sys.extended_properties p on p.major_id = c.object_id and p.minor_id = c.column_id and p.name = 'MS_Description'
left outer join sys.extended_properties p1 on p1.major_id = c.object_id and p1.minor_id = c.column_id and p1.name = 'Valor'
where t.id = @i
order by c.column_id
end
Set @maxj = @@rowcount
set @j = 1


print '

Table Columns

'
print ' '

While(@j <= @maxj)
begin
select @Output = ' '
from #Columns where id = @j

print @Output
Set @j = @j + 1;
end


print '

Sr.	Name	Datatype	Nullable	Description	Value
' + Cast((@j) as varchar) + '	' + isnull(name,'') + '	' + upper(isnull(Type,'')) + '	' + isnull(Nullable,'N') + '	' + isnull([description],'') + '	' + isnull(valor,'') + '

'


--reference key
truncate table #FK
if @SqlVersion = '2000'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2000
select object_name(constid), s.name, object_name(rkeyid) , s1.name
from sysforeignkeys f
inner join sysobjects o on o.id = f.constid
inner join syscolumns s on s.id = f.fkeyid and s.colorder = f.fkey
inner join syscolumns s1 on s1.id = f.rkeyid and s1.colorder = f.rkey
inner join #Tables t on t.object_id = f.fkeyid
where t.id = @i
order by 1
end
else if @SqlVersion = '2005'
begin
insert into #FK (Name, col, refObj, refCol)
-- FOR 2005
select f.name, COL_NAME (fc.parent_object_id, fc.parent_column_id) , object_name(fc.referenced_object_id) , COL_NAME (fc.referenced_object_id, fc.referenced_column_id)
from sys.foreign_keys f
inner join sys.foreign_key_columns fc on f.object_id = fc.constraint_object_id
inner join #Tables t on t.object_id = f.parent_object_id
where t.id = @i
order by f.name
end

Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin


print '

Refrence Keys

'
print ' '


While(@j <= @maxj)
begin


select @Output = ' '
from #FK where id = @j


print @Output
Set @j = @j + 1;
end


print '

Sr.	Name	Column	Reference To
' + Cast((@j) as varchar) + '	' + isnull(name,'') + '	' + isnull(col,'') + '	[' + isnull(refObj,'N') + '].[' + isnull(refCol,'N') + ']

'
end


--Default Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and
convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '10101000'
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), c.definition
from sys.default_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount
set @j = 1
if (@maxj >0)
begin


print '

Default Constraints

'
print ' '


While(@j <= @maxj)
begin


select @Output = ' '
from #Constraint where id = @j


print @Output
Set @j = @j + 1;
end


print '

Sr.	Name	Column	Value
' + Cast((@j) as varchar) + '	' + isnull(name,'') + '	' + isnull(col,'') + '	' + isnull(definition,'') + '

'
end


--Check Constraints
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name, col, definition)
select object_name(c.constid), col_name(c.id, c.colid), s.text
from sysconstraints c
inner join #Tables t on t.object_id = c.id
left outer join syscomments s on s.id = c.constid
where t.id = @i
and ( convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00101000'
or convert(varchar,+ (c.status & 1)/1)
+ convert(varchar,(c.status & 2)/2)
+ convert(varchar,(c.status & 4)/4)
+ convert(varchar,(c.status & 8)/8)
+ convert(varchar,(c.status & 16)/16)
+ convert(varchar,(c.status & 32)/32)
+ convert(varchar,(c.status & 64)/64)
+ convert(varchar,(c.status & 128)/128) = '00100100')


end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name, col, definition)
select c.name, col_name(parent_object_id, parent_column_id), definition
from sys.check_constraints c
inner join #Tables t on t.object_id = c.parent_object_id
where t.id = @i
order by c.name
end
Set @maxj = @@rowcount

set @j = 1
if (@maxj >0)
begin


print '

Check Constraints

'
print ' '


While(@j <= @maxj)
begin


select @Output = ' '
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end


print '

Sr.	Name	Column	Definition
' + Cast((@j) as varchar) + '	' + isnull(name,'') + '	' + isnull(col,'') + '	' + isnull(definition,'') + '

'
end




--Triggers
truncate table #Constraint
if @SqlVersion = '2000'
begin
insert into #Constraint (Name)
select tr.name
FROM sysobjects tr
inner join #Tables t on t.object_id = tr.parent_obj
where t.id = @i and tr.type = 'TR'
order by tr.name
end
else if @SqlVersion = '2005'
begin
insert into #Constraint (Name)
SELECT tr.name
FROM sys.triggers tr
inner join #Tables t on t.object_id = tr.parent_id
where t.id = @i
order by tr.name
end
Set @maxj = @@rowcount

set @j = 1
if (@maxj >0)
begin


print '

Triggers

'
print ' '


While(@j <= @maxj)
begin
select @Output = ' '
from #Constraint where id = @j
print @Output
Set @j = @j + 1;
end


print '

Sr.	Name	Description
' + Cast((@j) as varchar) + '	' + isnull(name,'') + '

'
end


--Indexes
truncate table #Indexes
if @SqlVersion = '2000'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.indid = 0 then 'Heap' when i.indid = 1 then 'Clustered' else 'Nonclustered' end , c.name
from sysindexes i
inner join sysindexkeys k on k.indid = i.indid and k.id = i.id
inner join syscolumns c on c.id = k.id and c.colorder = k.colid
inner join #Tables t on t.object_id = i.id
where t.id = @i and i.name not like '_WA%'
order by i.name, i.keycnt
end
else if @SqlVersion = '2005'
begin
insert into #Indexes (Name, type, cols)
select i.name, case when i.type = 0 then 'Heap' when i.type = 1 then 'Clustered' else 'Nonclustered' end, col_name(i.object_id, c.column_id)
from sys.indexes i
inner join sys.index_columns c on i.index_id = c.index_id and c.object_id = i.object_id
inner join #Tables t on t.object_id = i.object_id
where t.id = @i
order by i.name, c.column_id
end


Set @maxj = @@rowcount

set @j = 1
set @sr = 1
if (@maxj >0)
begin


print '

Indexes

'
print ' '
set @Output = ''
set @last = ''
set @current = ''
While(@j <= @maxj)
begin
select @current = isnull(name,'') from #Indexes where id = @j

if @last <> @current and @last <> ''
begin
print ' '
set @Output = ''
set @sr = @sr + 1
end


select @Output = @Output + cols + '
' , @typ = type
from #Indexes where id = @j

set @last = @current
Set @j = @j + 1;
end
if @Output <> ''
begin
print ' '
end


print '

Sr.	Name	Type	Columns
' + Cast((@sr) as varchar) + '	' + @last + '	' + @typ + '	' + @Output + '
' + Cast((@sr) as varchar) + '	' + @last + '	' + @typ + '	' + @Output + '

'
end
--------------------------------------------------------------------------------------------
Set @i = @i + 1;
--Print @Output
Print 'Autor: ROBINSON MOSCOSO - SIPSE'
end
-------------------------------------------------------------------------------------------------------------------------------------------------------------
-- procedures
truncate table #Procedure
if @SqlVersion = '2005'
begin
---create Table #Procedure(id int identity(1,1), Shema varchar(25), [Procedure] Varchar(60), CreadoEl varchar(50), UltimaModificacion varchar(50))
insert into #Procedure (Shema, [Procedure], CreadoEl, UltimaModificacion)
SELECT SPECIFIC_SCHEMA AS Shema,ROUTINE_NAME As [Procedure],CREATED AS CreadoEl,LAST_ALTERED AS UltimaModificacion
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_TYPE = 'PROCEDURE' -- AND LEFT(ROUTINE_NAME,4) ='usp_'
ORDER BY ROUTINE_NAME
end


Set @maxj = @@rowcount
set @j = 1



print ''
print '
'
print ''
print '

Procedure Name

'
print ' '


While(@j <= @maxj)
begin
select @Output = ' '
from #Procedure where id = @j

print @Output
Set @j = @j + 1;
end


print '

Sr.	Schema	Procedure Name	Creado El	UltimaModificacion
' + Cast((@j) as varchar) + '	' + isnull(Shema,'') + '	' + isnull([Procedure],'') + '	' + isnull(CreadoEl,'') + '	' + isnull(UltimaModificacion,'') + '

'
Print 'Autor: ROBINSON MOSCOSO - SIPSE'
Print ''
Print ''


drop table #Tables
drop table #Columns
drop table #FK
drop table #Constraint
drop table #Indexes
drop table #Procedure

set nocount off

-- Fin del script

3. Seleccionar que genere el resultado en un archivo 

3. Al ejecutar el script se abre una ventana para guardar el archivo generado, se especifica el nombre, teniendo en cuenta de agregar el tipo htm al final del nombre (por ejemplo: diccionario.htm) y en tipo de archivo se debe seleccionar "Todos los archivos(*.*)" y se hace clic en guardar.

4. Finalmente abrimos el archivo en cualquier navegador y se podrá observar el diccionario de nuestra base de datos.

Este script, lo encontré hace un tiempo por ahí navegando pero no recuerdo el sitio para hacer referencia a este, sin embargo, quien lo hizo aparece en el Script, por lo tanto agradezco a Nitin Patel por tan buen trabajo.

Saludos

Róbinson