use NOMBRE-BASE-DE-DATOS
/* PERMITE REINICIAR EL VALOR DE UN CAMPO IDENTITY A CERO*/
/**/
/**/
DECLARE @NombreTabla VARCHAR(30)
DECLARE @TotalTablas INT
SET @TotalTablas=0
DECLARE Tablas CURSOR FOR
SELECT Name FROM SYS.objects T WHERE T.type='U' AND T.name NOT IN
('sysdiagrams',
'TABLA X')
OPEN Tablas
/*IF @@CURSOR_ROWS>0*/
/*PRINT @@CURSOR_ROWS*/
FETCH NEXT FROM Tablas
INTO @NombreTabla
WHILE @@FETCH_STATUS=0
BEGIN
PRINT 'REINICIANDO '+ @NombreTabla+'...'
SET @TotalTablas=@TotalTablas+1
DBCC CHECKIDENT (@NombreTabla, RESEED,0)
FETCH NEXT FROM Tablas
INTO @NombreTabla
END
PRINT CONVERT(VARCHAR,@TotalTablas) +' TABLAS FUERON REINICIADAS'
/*END*/
CLOSE Tablas
DEALLOCATE Tablas
viernes, 25 de marzo de 2011
Reiniciar Campos Identity en SQL Server
viernes, 11 de marzo de 2011
Generar Diccionario de datos para Base de Datos SQL Server
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>'
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>'
Suscribirse a:
Entradas (Atom)