lunes, 26 de septiembre de 2011

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