Generar Diccionario de Datos Sql Server 2005 o 2008

Hace más de dos años publiqué un artículo de como podíamos generar un diccionario de datos desde una base de datos SQL 2000. Un lector realizó unas mejoras significativas al diccionario para corregir algunos valores como son los tipos de datos y las variables primary keys que no eran identificadas correctamente en el script anterior. Además, el script anterior solo generaba el diccionario para una sola base de datos, mientras que este script lo hace para todas las bases de datos que se encuentren en un servidor.

Por esta vía deseo agradecer a Adán Salvador Aguilera Campos desde México por el aporte que nos ha hecho a todos con las mejoras al script de generación de un diccionario de datos.


/*
para systypes and xusertype <= 256)
Creado por Adán Salvador Aguilera Campos
Aguascalientes, Mexico
Basado en gran parte en el trabajo hecho por el Ing. Ely Michael Núñez
26/08/2011
*/
set nocount on
/*
------------------------------------------------------------------------------
 Variables y tablas temporales
*/
declare @dbname sysname 
declare @name   sysname
declare @tabla    sysname
declare @uid    smallint
declare @usuario sysname
declare @fecha nvarchar(11)
declare @cmd      nvarchar(600)
declare @low nvarchar(11)
declare @cont int
declare @total int
set @cont=0
set @total=0

create table #Base
(
      ident    smallint,
      base         sysname
)
----------------------------------------------------------------
/*Del script de diccionario de datos*/

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

/* 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


-----------------------PRUEBA para syscolumns--------

create table #pk (
      TABLE_QUALIFIER varchar(100),
      TABLE_OWNER varchar(100),
      TABLE_NAME varchar(100),
      COLUMN_NAME varchar(100),
      KEY_SEQ smallint,
      PK_NAME varchar(100)
)

create table #columnas(TABLE_QUALIFIER   sysname     ,
      TABLE_OWNER sysname     ,
      TABLE_NAME  sysname     ,
      COLUMN_NAME sysname     ,
      DATA_TYPE   smallint    ,
      TYPO  varchar(100)      ,
      PREC  int   ,
      LENGTH      int   ,
      SCALE smallint    ,
      RADIX smallint    ,
      NULLABLE    smallint    ,
      REMARKS     varchar(254)      ,
      COLUMN_DEF  nvarchar(3000)    ,
      SQL_DATA_TYPE     smallint    ,
      SQL_DATETIME_SUB  smallint    ,
      CHAR_OCTET_LENGTH int   ,
      ORDINAL_POSITION int   ,
      IS_NULLABLE varchar(254)      ,
      SS_DATA_TYPE      tinyint    
)
/*select column_name,typo, column_def,length,is_nullable,column_name
into @column_name, @column_type, @cdefault, @prec, @isnullable, @pk
*/----------------------------------------------------------

/* Declares for columns variables*/
declare @llave sysname
declare @column_name sysname
declare @column_type varchar(13)
declare @cdefault nvarchar(3000)
declare @prec int
declare @isnullable varchar(254)
declare @nulo as varchar(3)
declare @pk sysname


------------------------------------------------------------------------------
/* Dado que se tienen que extraer datos de dos tablas diferentes se tiene
** que requerir al uso de tablas temporales para crear las descripciones
*
*/
/*
**  Se crea #Base a partir de sysdatabases
    para identificar las bases de datos registradas en el servidor y se descartan
    las que son de SQL Server
*/
insert into #Base (base, ident )
      select name, dbid
            from master.dbo.sysdatabases
            where (@dbname is null or name = @dbname)
                  AND name<> 'master' AND name<> 'tempdb'
                  AND name<> 'model' AND name<> 'msdb'
                  AND name<> 'pubs' AND name<> 'Northwind'


/*
** Se busca cuales bases están accesibles para el usuario
** y las que no las elimina de #Base
*/

declare ms_crs_c1 cursor for
      select db_name (ident) from #Base
open ms_crs_c1
fetch ms_crs_c1 into @name
while @@fetch_status >= 0
begin
      if (has_dbaccess(@name) <> 1)
      begin
            delete #Base where current of ms_crs_c1
            raiserror(15622,-1,-1, @name)
      end
      fetch ms_crs_c1 into @name
end
deallocate ms_crs_c1
/*
**  Se utiliza @curdbid para recorrer los identificadores de BD
**  en #Base para construir las otras dos tablas temporales, se construyen sus caracteristicas
**  @ErrorSave1 se utiliza para manejar errores
*/
print '<html>'
print '<head><title>'
print '           Diccionario de Datos'
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 '     <table>                
            <tr>       
                  <td colspan="7">
                        <h1><strong><span class="style7">Servidor: </span>' + @@servername + '</strong></h1>
                  </td>
            </tr>
      </table>'


declare @curdbid smallint   
select @curdbid = min(ident) from #Base
while @curdbid IS NOT NULL
begin
      set @name = db_name(@curdbid)
      select @db_name2 = name, @dbid = dbid, @db_filename = filename, @db_crdate = crdate
      from master..sysdatabases where dbid = db_id(@name)
      select @low = convert(varchar(11),low) from master.dbo.spt_values
                  where type = N'E' and number = 1

      select @cmd = '(select str(convert(dec(15),sum(size))* ' + @low + '/ 1048576,10,2)+ N'' MB'' from '
            + quotename(@name, N'[') + N'.dbo.sysfiles)'
      /*set @cmd= '@db_size = (select str(convert(dec(15),sum(size))* ' + @low + '/ 1048576,10,2)+ N'' MB'' from '
                                               + quotename(@name, N'[') + N'.dbo.sysfiles) '
      */   
      print '     <div>'
      print '           <hr noshade="noshade"/>'    
      print '           <table >'        
      print '                 <tr>
                        <td colspan="7"><strong><span class="style7">Nombre de Base de Datos: </span></strong>' + @name
      print '                      </td>
                  </tr>'
      print '                 <tr>
                        <td colspan="7"><strong><span class="style7">Ruta del archivo: </span></strong>' + @db_filename
      print '                      </td>
                  </tr>'
      print '                 <tr>
                        <td colspan="7"><strong><span class="style7">Fecha de creación: </span></strong>' + convert(varchar,@db_crdate)
      print '                      </td>
                  </tr>'     
      print '                 <tr>
                        <td colspan="7"><strong><span class="style7">Tamaño: </span></strong>' 
      exec (@cmd)
      print '                      </td>
                  </tr>
                  '    


      select @cmd= 'Use "' + @name + '"'+char(13)+' 
      declare files_cursor cursor for         
      select name, filename, isnull((select groupname from sysfilegroups b where b.groupid = a.groupid),''N/A'')
            from sysfiles a'
      exec (@cmd)

     

      open files_cursor
      fetch next from files_cursor
            into @f_name, @f_filename, @f_groupname
      print '                 <tr>
                        <td colspan="7">
                             <ul>'

      while @@fetch_status = 0
      begin
     
            print '                                  <li>
                                         <b>' + @f_name + '</b>
                                         <br/>
                                         - Archivo: ' + @f_filename + '<br/>                 
                                         - Tipo: ' + @f_groupname + '
                                   </li>'
     
            fetch next from files_cursor
            into @f_name, @f_filename,  @f_groupname
     
      end
      print '                            </ul>
                        </td>
                  </tr>
            </table>
            <hr />'
      print '     </div>'
     
     
      close files_cursor
      deallocate files_cursor
     
      select @cmd= 'Use "' + @name + '"'+char(13)+'       
            declare table_cursor cursor for
            select id, name, xtype, crdate, refdate from sysobjects where xtype = ''u''
      '
      exec(@cmd)
      open table_cursor
     
      fetch next from table_cursor
            into @id, @table_name, @xtype, @crdate, @refdate
     
      while @@fetch_status = 0
      begin
            set @cont=@cont +1
           

            select @cmd= 'Use "' + @name + '"'+char(13)+'       
                  declare total_cr cursor for
                  select count(*) from sysobjects where xtype = ''u''
            '
            exec(@cmd)
           

            open total_cr
           
            fetch next from total_cr
                  into @total
           
            while @@fetch_status = 0
            begin
                 
                  fetch next from total_cr
                        into @total
            end
            close total_cr
            deallocate total_cr
            print '     <div style="margin-left:5%;">
            <table >
                  <tr>'
                  If (@cont>1)
                  begin
                        print '<br/>'
                  end
                  print
                  '
                        <td>
                        </td>
                        <td colspan="1" style="border:1px solid black">
                             <b>Tabla</b> '+ convert(varchar,@cont) + ' de ' + convert(varchar,@total) +'
                        </td>
                        <td colspan="1" style="border:1px solid black">
                             <b>B.D. </b>'+ @name + '
                        </td>                 
                        <td colspan="4" style="border:1px solid black">     
                             <span class="style6">Fecha de creación: ' + convert(varchar,@crdate) + '</span>
                        </td>
                  </tr>
                  <tr>
                        <td>
                        </td>
                        <td colspan="6" style="font-weight:bold; font-size: 16px; border:1px solid black">
                              Nombre de tabla: ' + @table_name + '
                        </td>                 
                  </tr>
            </table>'
            print '           <table cellpadding="2" cellspacing="0">'-- bordercolor="#CCCCCC">'

            print '                 <tr>'-- bordercolor="#0066CC">'
/*          print '     <tr bordercolor="#0066CC" bgcolor="#cc9300">'
*/
            print '           <td style="border:none">
                        </td>                 
                        <td bgcolor="#0066CC"><span class="style5">Nombre de campo</span></td>'
            print '                      <td bgcolor="#0066CC"><span class="style5">Llave primaria </span></td>'
            print '                      <td bgcolor="#0066CC"><span class="style5">Tipo de columna</span></td>'
            print '                      <td bgcolor="#0066CC"><span class="style5">Valor por Default</span></td>'
            print '                      <td bgcolor="#0066CC"><span class="style5">Longitud</span></td>'
            print '                      <td bgcolor="#0066CC"><span class="style5">Valores nulos</span></td>'
            print '                </tr>'
           
            select @cmd= ' delete #columnas Use "' + @name + '"'+char(13)+'
                  insert into #columnas EXEC sp_columns "'+ @table_name +'"'
            EXEC (@cmd)
     
            Select @cmd = 'delete #pk Use "' + @name + '"'+char(13)+'
                  exec sp_pkeys "'+ @table_name +'"'
            insert into #pk exec(@cmd)

     
            select @cmd= 'update #columnas set REMARKS= '''' '
            exec (@cmd)
           
            select @cmd= 'Use "' + @name + '"'+char(13)+'
                  declare llaves cursor for
                        select column_name
                        from #pk'        
            exec (@cmd)
            open llaves
            fetch next from llaves
                  into @llave
            while @@fetch_status = 0
            begin                                         
                  select @cmd= 'update #columnas set remarks= ''X'' where column_name= ''' + @llave  + ''''
                  exec (@cmd)
                  fetch next from llaves
                        into @llave
            end        
            close llaves
            deallocate llaves
           

           
            select @cmd= 'Use "' + @name + '"'+char(13)+'
                  declare columns_cursor cursor for
                        select column_name,typo, isnull(column_def,'' ''),length,is_nullable,remarks
                        from #columnas'        
            exec (@cmd)      
            open columns_cursor
            fetch next from columns_cursor
                  into @column_name, @column_type, @cdefault, @prec, @isnullable, @pk
           

            /*select @cmd= 'Use "' + @name + '"'+char(13)+'
                  declare columns_cursor cursor for
                        select name, type = (select top 1 name from systypes b where b.xtype = a.xtype and b.xusertype = a.xusertype),
                             cdefault, isnull(prec,0) as prec, isnullable,
                             pk = isnull((select top 1 ''PK'' from sysobjects where xtype = ''u'' and parent_obj = id),''No tiene'')
                        from syscolumns a where id = ' + convert(varchar,@id)
                 
           
            exec (@cmd)
            open columns_cursor
     
            fetch next from columns_cursor
                  into @column_name, @column_type, @cdefault, @prec, @isnullable, @pk
      */
            while @@fetch_status = 0
            begin      
                  print '                 <tr>
                        <td>
                        </td>'
                  print '                      <td style="border:1px solid black;">'+ @column_name +  '</td>'
                  print '                      <td style="text-align:center; border:1px solid black;">' + @pk + '</td>'
                  print '                      <td style="border:1px solid black">' + isnull(@column_type,'varchar') + '</td>'
                  print '                      <td style="text-align:center; border:1px solid black;">' + convert(varchar,@cdefault) + '</td>'
                  print '                      <td style="text-align:center; border:1px solid black;">' + convert(varchar,@prec) + '</td>'
                  print '                      <td style="text-align:center; border:1px solid black;">' + @isnullable + '</td>'
                  print '                 </tr>'
                  /*if (@isnullable=0)
                  begin
                        set @nulo='No'
                  end
                  if (@isnullable=1)
                  begin
                        set @nulo='Si'
                  end
*/
                 
                 
                  fetch next from columns_cursor
                        into @column_name, @column_type, @cdefault, @prec, @isnullable, @pk
           
            end
     
            print '           <tr style="border:none">
                        </tr>
                        </table>'
            print '     </div>'
     
            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

----------------------------------------------------------
      /*
      ** Buscamos el siguiente identificador de BD
      */set @cont=0
      select @curdbid = min(ident) from #Base where ident > @curdbid
end
print '     </body>'
print '</html>'

/* obtener el resultado
-----------------------------------------------------------------
select base, nombre, usuario, num_filas, fecha
from #resul
order by base, nombre
-----------------------------------------------------------------
*/

drop table #base
drop table #columnas
drop table #pk

Comentarios

Anónimo ha dicho que…
Suuuuper
MUCHÍSIMAS GRACIAS.
Nos salvó noches y tiempos de angustia.

Un pequeñísíma mejoría sería que las tablas de cada base de datos saliera ordenadas por su nombre:

Sólo hay que incluir en la sentencia SELECT que está a partir de la línea 264 la cláusula de ordenamiento 'ORDER BY NAME'
---------------------------
select @cmd= 'Use "' + @name + '"'+char(13)+'
declare table_cursor cursor for
select id, name, xtype, crdate, refdate from sysobjects where xtype = ''u''
ORDER BY NAME'
-------------------------------
Saludos
alejandro.torres ha dicho que…
te debo muchas horas de programacion. gracias!!
Michael Núñez ha dicho que…
Es un placer.
Anónimo ha dicho que…
Muchas gracias muy util, sin embargo necesitaba incluir la descripcion de cada campo y procesar solo una base de datos asi que modifique algunas cosas ademas de darle mejor apariencia visual, lo comparto con el que lo requiera: www.victor-rivas.com/descargas/gen_dicc.sql (favor desacargar el archivo y publicar su conetnido ya que estara disponible solo unos dias, gracias)
Anónimo ha dicho que…
Sería factible saber el foreign key de la columna??? estoy tratando de contrar esa descripción pero no hayo como.. un abrazo Muy util el código!
Anónimo ha dicho que…
Me respondo mi pregunta, se debe incorporar lo siguiente:
create table #FkRelacion
(
nombreTablaFk varchar(100),
nombreColFk varchar(100),
nombreTabla varchar(100),
nombreColTabla varchar(100),
Fk varchar(200)
)

Una vez en el curso por tablas
insert into #FkRelacion
select object_name(k.parent_object_id),b.name,convert(varchar,object_name(k.referenced_object_id)) ,convert(varchar,c.name),convert(varchar,object_name(k.referenced_object_id))+'.'+convert(varchar,c.name)
from sys.foreign_keys k
inner join sys.foreign_key_columns as fk on fk.constraint_object_id =k.object_id
inner join sys.columns as c on fk.referenced_object_id= c.object_id and fk.referenced_column_id = c.column_id
inner join sys.columns as b on fk.parent_object_id= b.object_id and fk.parent_column_id = b.column_id
WHERE k.parent_object_id = object_id(@table_name)


y se debe reemplazar el cursor de las columnas
select column_name,typo, isnull(column_def,'' ''),length,is_nullable,remarks,isnull(Fk,'' '')
from #columnas left JOIN #FkRelacion fk ON fk.nombreTablaFk = TABLE_NAME
AND fk.nombreColFk = COLUMN_NAME'
luego Fk contiene la relación entre los foreign key.
Unknown ha dicho que…
MUCHAS GRACIAS. LO BUENO SE AGRADECE... me ahorre todo.
Anónimo ha dicho que…
Hola buen día!

Yo al ejecutar el Script me sale un error, lo cito a continuación:

Msg 16917, Level 16, State 1, Line 262 Cursor is not open. Msg 16958, Level 16, State 3, Line 269 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16958, Level 16, State 3, Line 271 Could not complete cursor operation because the set options have changed since the cursor was declared. Msg 16917, Level 16, State 1, Line 408 Cursor is not open.

y éste otro:

Msg 468, Level 16, State 9, Line 6 Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Modern_Spanish_CI_AS" in the equal to operation. Msg 16916, Level 16, State 1, Line 376 A cursor with the name 'columns_cursor' does not exist. Msg 16916, Level 16, State 1, Line 378 A cursor with the name 'columns_cursor' does not exist.

Alguien que me pueda auxiliar?

Saludos cordiales.
Vladimir Venegas Velásquez ha dicho que…
Por si alguien desea el diccionario de datos sin el formato CSS http://www.entrar.cl/diccionario-datos-sql-server/
ampe21 ha dicho que…
Amigo Anónimo, en el link que usted nos hizo llegar (http://www.victor-rivas.com/descargas/gen_dicc.sql) adaptó las correcciones realizadas en los siguientes comentarios a su actualización?
Anónimo ha dicho que…
Necesité algo asi en estos días y buscando me tope con varios scrits pero este es realmente bueno, gracias hermano.

Entradas populares de este blog

Como ejecutar una aplicación desde SQL.

Desahogo

Crear un Cursor SQL Server