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><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:
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
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.
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.
Gracias, voy a tratar de hacer esos cambios que me haz espresado y tan pronto como lo tenga les dejo saber.
Super bueno me salvaste la vida... Gracias!!!
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.
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.
Hola Soy Alberto Buen Aporte , pero como caso en sql 2005 la Descripcion de cada Columna de una tabla
hola amigo muy util
pero me fije que los campos numeric los coloca como varchart el cual estaria incorrecto
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
Gracias, voy a proceder a hacer algunos cambios a ese script para publicarlo más adelante, que incluye los tipos de datos.
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
Hola soy Luis, es una excelente script, el cual nos sirve de mucha ayuda, para la implementacion del diccionario de datos.
gracias.
Publicar un comentario en la entrada