A continuacion muestro el script sql, aplica para cualquier base de datos....
declare @databases varchar(150)
set @databases = 'AQUI PONEN EL NOMBRE DE LA BASE DE DATOS'
use NOMBRE DE LA BASE DE DATOS
set nocount on
declare @srvname sysname
/* Variables para llenar el combo*/
declare @nombre_tabla varchar(80)
/* Variables para la base de datos */
declare @db_name sysname
declare @dbid smallint
declare @db_filename nvarchar(520)
declare @db_crdate datetime
/* Variables archivos */
declare @f_name nchar(256)
declare @f_filename nchar(520)
declare @f_size int
declare @f_groupname sysname
/* Variables tablas de la bd */
declare @id int
declare @table_name sysname
declare @Descripcion_tabla varchar(max)
declare @xtype char(2)
declare @crdate datetime
declare @refdate datetime
/* Variables columnas de laas tablas */
declare @column_name sysname
declare @column_desc varchar(max)
declare @column_type sysname
declare @cdefault 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 'Diccionario de Datos'
print '</title>'
print '<script>'
print 'function navegar(){'
print 'var indice = document.form1.Listado.selectedIndex'
print 'location.href="#"+document.form1.Listado.options[indice].text'
print '}'
print '</script>'
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 '<form name="form1">'
print '<div>'
print '<div>'
print '<h1><span class="style7">Base de Datos: </span>' + @db_name + '</h1>'
print '<hr noshade/>'
print '<strong><span class="style7">Archivo: </span>' + @db_filename + '</strong>'
print '<br />'
print '<strong><span class="style7">Fecha de Creación: </span>' + convert(varchar,@db_crdate) + '</strong>'
print '<br />'
print '<strong><span class="style7">Servidor: </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 /> - Archivo: ' + @f_filename + '<br /> - Tamaño (Bytes): ' + convert(varchar,@f_size) + '<br /> - Grupo de Archivos: ' + @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 llena_combo cursor for
select x.name
from
sysobjects x
where xtype = 'u' order by x.name
open llena_combo
fetch next from llena_combo
into @nombre_tabla
print '<A name="inicio"></A>'
print '<hr noshade/>'
print'Elija Nombre Tabla: <SELECT name="Listado" onChange="navegar()">'
while @@FETCH_STATUS=0
begin
PRINT '<option value='+@nombre_tabla+'>'+@nombre_tabla+'</option>'
fetch next from llena_combo
into @nombre_tabla
end
print '</select>'
print '<hr noshade/>'
close llena_combo
deallocate llena_combo
declare table_cursor cursor for
select id,x.name, CONVERT(varchar(max),ep.value), xtype, crdate, refdate
from
sysobjects x inner join sys.extended_properties ep
on x.id=ep.major_id and ep.name='Descripción'
where xtype = 'u' order by x.name
open table_cursor
fetch next from table_cursor
into @id, @table_name,@Descripcion_tabla, @xtype, @crdate, @refdate
while @@fetch_status = 0
begin
print ' <h2><a NAME='+@table_name+'> Tabla: ' + @table_name + '</A></h2>'
print ' <h4 class="style6">Fecha Creación: ' + convert(varchar,@crdate) + '</h4>'
print '<h4 class="style6">Descripción: '+@descripcion_tabla+'</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">Nombre Columna</strong></th>'
print ' <th><span class="style5">Descripción</strong></th>'
print ' <th><span class="style5">Tipo de dato</strong></th>'
print ' <th><span class="style5">Valor por defecto</strong></th>'
print ' <th><span class="style5">Nulo</strong></th>'
print ' </tr>'
declare columns_cursor cursor for
select
c.name,
CONVERT(varchar (max),ep.value),
b.name,
cdefault,
isnullable,
pk = isnull((Select TOP 1 CONSTRAINT_NAME from INFORMATION_SCHEMA.KEY_COLUMN_USAGE X where X.COLUMN_NAME=c.name and CONSTRAINT_NAME like 'PK%' and X.TABLE_NAME=@table_name),' ')
from sys.objects o INNER JOIN sys.extended_properties ep
ON o.object_id = ep.major_id
INNER JOIN sys.schemas s
ON o.schema_id = s.schema_id
LEFT JOIN syscolumns c
ON ep.minor_id = c.colid
AND ep.major_id = c.id
,systypes b
where b.xtype = c.xtype and c.id=@id
order by c.colid
open columns_cursor
fetch next from columns_cursor
into @column_name,@column_desc,@column_type,@cdefault, @isnullable, @pk
while @@fetch_status = 0
begin
print ' <tr>'
print ' <td>' + CONVERT(VARCHAR,@pk) + '</td>'
print ' <td>' + ISNULL(@column_name,'VARCHAR') + '</td>'
print ' <td>' + @column_desc+ '</td>'
print ' <td>' + ISNULL(@column_type,'VARCHAR') + '</td>'
print ' <td>' + CONVERT(VARCHAR,@cdefault) + '</td>'
print ' <td>' + CONVERT(VARCHAR,@isnullable) + '</td>'
print ' </tr>'
fetch next from columns_cursor
into @column_name,@column_desc,@column_type,@cdefault, @isnullable, @pk
end
print ' </table>'
print '<br><a href=#inicio>=|=Volver=|=</a>'
close columns_cursor
deallocate columns_cursor
fetch next from table_cursor
into @id, @table_name,@Descripcion_tabla, @xtype, @crdate, @refdate
end
close table_cursor
deallocate table_cursor
print '</div>'
print '</div>'
print '</form>'
print '</body>'
print '</html>'
Este comentario ha sido eliminado por el autor.
ResponderEliminarPor si alguien desea generar el diccionario de datos sin el formato CSS http://www.entrar.cl/diccionario-datos-sql-server/
ResponderEliminar