martes, 19 de mayo de 2009

Generar Diccionario de Datos Sql Server 2000

A veces tenemos necesidades urgente con un tiempo muy corto (o como dicen en mi país, con el tiempo arriba) donde necesitamos entregar un proyecto o una determinada posición y no tenemos la documentación de las aplicaciones o los diccionarios de datos de la base de datos que necesitamos.

Hace dos días se me presentó esa dificultad, tenía mis aplicaciones documentadas, toda la documentación de las políticas que maneja el departamento, los diagramas de flujos de la aplicaciones, los diagramas de base de datos, pero me faltaba algo y no sabía que era.

Cuando me quedaba solo 1 día para entregar la posición recordé que no había documentado las base de datos, y estas eran enormes, y me estaban esperando con urgencia en mi nuevo trabajo. Tuve que tomar la decisión de generar un HTML a partir de la misma base de datos con algo parecido a un diccionario de datos y les voy a mostrar el código utilizado para el mismo, espero les sirva.

declare @databases varchar(150)

set @databases = 'db_practica'

use db_practica
/*
Cursor: generate XML data information from SQL Server 2000.
Created By: Michael Núñez
Created On: 2009-05-15
Modified ON: 2009-05-19
Version: v1.3
*/

set nocount on

declare
@srvname sysname

/* Declares for databases variables */
declare @db_name sysname
declare @dbid smallint
declare @db_filename nvarchar(520)
declare @db_crdate datetime

/* Declares for databases files variables */
declare @f_name nchar(256)
declare @f_filename nchar(520)
declare @f_size int
declare @f_groupname sysname

/* Declares for tables variables */
declare @id int
declare @table_name sysname

declare @xtype char(2)
declare @crdate datetime
declare @refdate datetime


/* Declares for columns variables */
declare @column_name sysname
declare @column_type sysname
declare @cdefault int
declare @prec smallint
declare @scale int
declare @isnullable int
declare @pk char
(2)

select @db_name = name, @dbid = dbid, @db_filename = filename, @db_crdate = crdate
from master..sysdatabases where dbid = db_id(@databases)

select top 1 @srvname = srvname from master..sysservers

print '<html>'
print '<head><title>'
print 'Data Dictionary'
print '</title>'
print '<style type="text/css">'
print '<!--'
print '.style5 {color: #CCCCCC; font-weight: bold; }'
print '.style6 {color: #0000CC}'
print '.style7 {color: #000066}'
print '-->'
print '</style>'
print '</head>'
print '<body>'
print '<div>'


print '<div>'
print '<h1><span class="style7">Databases Name: </span>'
+ @db_name + '</h1>'
print '<hr noshade/>'
print '<strong><span class="style7">Filename: </span>' + @db_filename + '</strong>'
print '<br />'

print '<strong><span class="style7">Created Date: </span>' + convert(varchar,@db_crdate) + '</strong>'

print '<br />'
print '<strong><span class="style7">Server Name: </span>' + (@srvname) + '</strong>'
print '<hr />'

declare files_cursor cursor for

select name, filename, size, isnull((select groupname from sysfilegroups b where b.groupid = a.groupid),'N/A')
from sysfiles a

open files_cursor

fetch next from files_cursor
into @f_name, @f_filename, @f_size, @f_groupname

print '<ul>'

while @@fetch_status = 0
begin

print '<li><b>' + @f_name + '</b&gt;<br /> - File: ' + @f_filename + '<br /> - Size: ' + convert(varchar,@f_size) + '<br /> - Filegroup: ' + @f_groupname + '</li>'

fetch next from files_cursor
into @f_name, @f_filename, @f_size, @f_groupname

end
print
'</ul>'

close files_cursor
deallocate files_cursor

declare table_cursor cursor for
select
id, name,
xtype, crdate, refdate from sysobjects where xtype = 'u'

open table_cursor

fetch next from table_cursor
into @id, @table_name, @xtype, @crdate, @refdate

while @@fetch_status = 0
begin

print ' <h2> Table Name: ' + @table_name + '</h2>'
print ' <h4 class="style6">Created Date: ' + convert(varchar,@crdate) + '</h4>'
print ' <table border="1" cellpadding="2" cellspacing="0" bordercolor="#CCCCCC">'
print ' <tr bordercolor="#0066CC" bgcolor="#0066CC">'
print ' <th><span class="style5"> </strong></th>'
print ' <th><span class="style5">Column Name</strong></th>'
print ' <th><span class="style5">Column Type</strong></th>'
print ' <th><span class="style5">Default Value</strong></th>'
print ' <th><span class="style5">Pre</strong></th>'
print ' <th><span class="style5">Scale</strong></th>'
print ' <th><span class="style5">Nullable</strong></th>'
print ' </tr>'


declare columns_cursor cursor for
select name, type = (select top 1 name from systypes b where b.type = a.type and b.xtype = a.xtype),
cdefault, isnull(prec,0) as prec, isnull(scale,0) as scale, isnullable,
pk = isnull((select top 1 'PK' from sysobjects where xtype = 'u' and parent_obj = id),' ')
from syscolumns a where id = @id

open columns_cursor

fetch next from columns_cursor
into @column_name, @column_type, @cdefault, @prec, @scale, @isnullable, @pk

while @@fetch_status = 0
begin

print ' <tr>'
print ' <td>' + @pk + '</td>'
print ' <td>' + @column_name + '</td>'
print ' <td>' + isnull(@column_type,'varchar') + '</td>'
print ' <td>' + convert(varchar,@cdefault) + '</td>'
print ' <td>' + convert(varchar,@prec) + '</td>'
print ' <td>' + convert(varchar,@scale) + '</td>'
print ' <td>' + convert(varchar,@isnullable) + '</td>'
print ' </tr>'

fetch next from columns_cursor
into @column_name, @column_type, @cdefault, @prec, @scale, @isnullable, @pk

end

print ' </table>'

close columns_cursor
deallocate columns_cursor

fetch next from table_cursor
into @id, @table_name, @xtype, @crdate, @refdate

end

close table_cursor
deallocate table_cursor

print '</div>'

print '</div>'
print '</body>'
print '</html>'

Para poder copiar este código en Blogger tuve que utilizar la herramienta http://www.simplebits.com/cgi-bin/simplecode.pl?mode=process ya que no podía pegarla directamente y convertir "<" en "<" es una tarea dificil en un post tan largo.

Realmente espero que les sirva y si pueden hacerla para varias base de datos favor escribirme a elymichael@gmail.com, en caso de que yo no termine primero, :) .

Nota: El lector Adán Salvador Aguilera Campos de México, hizo unas mejoras significativas a este script, pueden visualizarlo en el siguiente enlace.

14 comentarios:

Anónimo dijo...

Que es esto una funcion un procedimiento, como lo hago para correrlo, esta muy interesante, yo hice algo parecido en su tiempo, para access, espero hacerlo para SQL y MYSQL, muchas gracias por el aporte.

Jorge

Michael Núñez dijo...

Este query está diseñado para SQL Server 2000, y solo lo copias en el Query Analyser y ejecutarlo en modo texto. Espero te sirva, si deseas complementar lo que hiciste en Access.

Anónimo dijo...

Super bueno el aporte, practicamente genera lo que se necesita, una modificacion importante seria tomar los comentarios de cada columna y ponerlos como descripcion en tabla, y poner de cada tabla las llaves FK y PK para que este completo. Pero esto incialmente muy bueno, si hago esa modificacion con gusto la subo.

Saludos.

Michael Núñez dijo...

Gracias, voy a tratar de hacer esos cambios que me haz espresado y tan pronto como lo tenga les dejo saber.

Anónimo dijo...
Este comentario ha sido eliminado por un administrador del blog.
John Carlos dijo...

Super bueno me salvaste la vida... Gracias!!!

Michael Núñez dijo...

jajaja, eso mismo pensé luego que lo terminé, me faltan unas cositas, pero pude entregar algo de documentación cuando iba a cambiar de empleo. Sabes que esa es la parte tediosa del negocio y lo más pesado de hacer.

Anónimo dijo...

oye man tengo un error me podrias decir xq podria ser es en:
No se encuentra en sysdatabases la entrada de la base de datos 'db_practica'. Asegúrese de que ha escrito el nombre correctamente.

Anónimo dijo...

Hola Soy Alberto Buen Aporte , pero como caso en sql 2005 la Descripcion de cada Columna de una tabla

Don Deco dijo...

hola amigo muy util
pero me fije que los campos numeric los coloca como varchart el cual estaria incorrecto

Don Deco dijo...

Hola mi nombre es Angelo super util lo que colocaste pero encotre un detalle que no se si se debe al codigo

Que los campos numeric los coloca como varchart

Michael Núñez dijo...

Gracias, voy a proceder a hacer algunos cambios a ese script para publicarlo más adelante, que incluye los tipos de datos.

Luis Eduardo Estrada dijo...

agregue los comentarios

declare columns_cursor cursor for
select a.name, type = (select top 1 name from systypes b where b.type = a.type and b.xtype = a.xtype),
cdefault, isnull(prec,0) as prec, isnull(scale,0) as scale, isnullable,
pk = isnull((select top 1 'PK' from sysobjects where xtype = 'u' and parent_obj = id),' '),
cast(ex.value as varchar(250)) as Description
from syscolumns a LEFT OUTER JOIN sys.extended_properties ex ON
ex.major_id = a.id
AND ex.minor_id = a.colid
AND ex.name = 'MS_Description'
where id = @id

lanc dijo...

Hola soy Luis, es una excelente script, el cual nos sirve de mucha ayuda, para la implementacion del diccionario de datos.

gracias.