Download Conversión de Números a Letras con Transact-SQL

Procedimiento para Contar Las
Bases de Datos, Tablas y
Registros en un Servidor SQL
con Transact SQL
Leonel Morales Díaz
Ingeniería Simple
[email protected]
Copyright 2008 by Leonel Morales Díaz – Ingeniería Simple.
Derechos reservados
Disponible en: http://www.ingenieriasimple.com/TSQL
Problema
• Se necesita mantener un monitoreo de
– Número de bases de datos
– Tablas por base de datos
– Registros por tabla
– En un servidor SQL
• Restricciones de permisos
– El usuario puede no tener permiso de
• Login en ciertas bases de datos
• Lectura en ciertas tablas
Requerimiento
• Crear un procedimiento que liste
– Bases de datos
• (número de tablas entre paréntesis)
– Tablas por base de datos
• (número de registros entre paréntesis)
Con dos cursores
• Código completo
Create Table #TablasCount (NombreDB SysName, NombreTabla SysName Null, CantRegistros int)
Create Table #Tablas (Name SysName)
Declare @CurDB SysName
Declare @DBAccesible Bit
Declare @CurName SysName
Declare CountTables Cursor
For Select Name From Sys.Databases Where Owner_SId <> 1
Open CountTables
Fetch Next From CountTables Into @CurDB
While @@Fetch_Status = 0 Begin
Set @DBAccesible = 1
Begin Try
Execute('Use ' + @CurDB)
End Try
Begin Catch
Set @DBAccesible = 0
End Catch
If @DBAccesible = 1 Begin
Execute('Use ' + @CurDB + '; Insert Into #Tablas Select Name From Sys.Tables')
Declare CountRecords Cursor
For Select Name From #Tablas
Inicialización y operaciones finales
• Inicialización
Tablas temporales disponibles en la sesión sin importar
en qué base de datos se esté trabajando.
Create Table #TablasCount (NombreDB SysName, NombreTabla SysName Null, CantRegistros int)
Create Table #Tablas (Name SysName)
Declare @CurDB SysName
Declare @DBAccesible Bit
Declare @CurName SysName
Esta se necesita porque el cursor siempre corre contra
ella y no contra la base de datos en contexto.
Declare CountTables Cursor
For Select Name From Sys.Databases Where Owner_SId <> 1
• Operaciones finales
Cursor para las bases de datos en el servidor activo.
Solo se toman las de usuario, la Master, Model, etc., no
se toman en cuenta.
DeAllocate CountTables
Drop Table #Tablas
Select * From #TablasCount
Drop Table #TablasCount
Se borran los cursores y las tablas temporales. Para la
#TablasCount, que es la que contiene los datos, se
hace un select antes de borrarla.
Ciclo principal
• Código del ciclo
While @@Fetch_Status = 0 Begin
Set @DBAccesible = 1
Begin Try
Execute('Use ' + @CurDB)
End Try
Begin Catch
Set @DBAccesible = 0
End Catch
La variable @DBAccesible solo sirve para indicar si se
tienen permisos para acceder a la base de datos.
Como se ve la verificación es por prueba y error. La
estructura Try – Catch sirve para esto.
If @DBAccesible = 1 Begin
/****************************************************/
/*** El código que se ejecuta en el ciclo va aquí ***/
/****************************************************/
Si no es posible acceder a la base de datos
se inserta un registro con el nombre de la DB
y nullos para indicarlo.
End Else
Insert Into #TablasCount (NombreDB,NombreTabla,CantRegistros) Values (@CurDB,Null,Null)
Fetch Next From CountTables Into @CurDB
End
Dentro del ciclo
• Si hay acceso a la base de datos…
Execute('Use ' + @CurDB + '; Insert Into #Tablas Select Name From Sys.Tables')
Declare CountRecords Cursor
For Select Name From #Tablas
Open CountRecords
Cursor sobre la lista de
tablas. Se crea y destruye
en cada iteración.
Fetch Next From CountRecords Into @CurName
Se recupera la lista de tablas, esto
solo se puede hacer en el mismo
contexto de la base de datos.
Se intenta el acceso a cada tabla y
si es posible se recupera el número
de registros en ella.
While @@Fetch_Status = 0 Begin
Set @DBAccesible = 1
Begin Try
Execute('Use ' + @CurDB + '; Insert Into #TablasCount (NombreDB,NombreTabla,CantRegistros) ' +
'Select ''' + @CurDB + ''',''' + @CurName + ''' As Nombre,' +
'Count(*) As CantRegistros From [' + @CurName + ']')
End Try
Si no hay permiso de consulta a la tabla
Begin Catch
se inserta un registro con nulo en el
Set @DBAccesible = 0
End Catch
campo de cantidad de registros.
If @DBAccesible = 0
Insert Into #TablasCount (NombreDB,NombreTabla,CantRegistros) Values (@CurDB,@CurName,Null)
Fetch Next From CountRecords Into @CurName
End
Close CountRecords
DeAllocate CountRecords
Delete From #Tablas
Se destruye el cursor y se borran los datos de
la lista de tablas para dejarlos listos para la
siguiente iteración.
Resultados
• Luego de correr el código
NombreDB
-------------------ReportServer
ReportServer
ReportServer
ReportServer
ReportServer
ReportServer
ReportServer
ReportServer
ReportServer
ReportServer
ReportServer
ReportServer
ReportServerTempDB
ReportServerTempDB
ReportServerTempDB
ReportServerTempDB
ReportServerTempDB
ReportServerTempDB
DummyArticulos
DummyArticulos
DummyArticulos
DummyArticulos
DummyArticulos
DummyArticulos
(86 row(s) affected)
NombreTabla
--------------------ConfigurationInfo
Catalog
UpgradeInfo
ModelDrill
ModelPerspective
CachePolicy
Users
DataSource
Policies
ModelItemPolicy
SecData
History
SnapshotData
ChunkData
PersistedStream
SessionLock
SessionData
ExecutionCache
Nomenclatura
TemporalEncabezado
Datos
TemporalDetalle
ERRoRES
ENCABEZADO
CantRegistros
-------------17
1
0
0
0
0
3
0
2
0
2
0
0
0
0
0
0
0
22
27
43
10
0
0
Posibles usos
• Generar una página de Internet que
presente constantemente el resumen de
las actualizaciones a las bases de datos
de un servidor
• Revisar de una sola mirada el contenido y
permisos de acceso a las bases de datos
de un servidor
• Recopilación histórica de crecimiento de
bases de datos en un servidor