viernes, 25 de marzo de 2011

Reiniciar Campos Identity en SQL Server


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, 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&gt;<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>'