Awaiting Migration: How to know which tables are used and which not

Question

Awaiting Migration: How to know which tables are used and which not

pzlingo

Valued Member

Points: 71
More actions
July 11, 2012 at 2:38 am

#392719

Hi
This little tutorial only demostrates to detect the tables which are used for manimpulating data, not the parameter tables, which maybe rarely are updated.
But in any case, this scripts may be useful to analize the current situation of the database, and to get an overall overview of what happens within it.
Hope it helps to others:
Maybe the code is something poor, but it works very well.
==================================
First, the table to store the information:
****** Objeto: Table [dbo].[pz_t_mov] Fecha de la secuencia de comandos: 07/10/2012 06:59:33 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[pz_t_mov](
[tabla] [varchar](150) COLLATE Modern_Spanish_CI_AS NOT NULL,
[ins] [int] NOT NULL CONSTRAINT [DF_pz_tablas_movimientos_ins] DEFAULT ((0)),
[int] NOT NULL CONSTRAINT [DF_pz_tablas_movimientos_del] DEFAULT ((0)),
[upd] [int] NOT NULL CONSTRAINT [DF_pz_tablas_movimientos_upd] DEFAULT ((0)),
[fecha_creacion] [datetime] NOT NULL,
[fecha_ins] [datetime] NULL,
[fecha_del] [datetime] NULL,
[fecha_upd] [datetime] NULL,
CONSTRAINT [PK_pz_t_mov] PRIMARY KEY CLUSTERED
(
[tabla] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
Now some indexes, depens on the size of the database and the amount of tables
CREATE UNIQUE NONCLUSTERED INDEX [IndiceTabla] ON [dbo].[pz_t_mov]
(
[tabla] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, ONLINE = OFF) ON [PRIMARY]
CREATE NONCLUSTERED INDEX [IndiceIns] ON [dbo].[pz_t_mov]
(
[ins] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR =
CREATE NONCLUSTERED INDEX [IndiceUpd] ON [dbo].[pz_t_mov]
(
[upd] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_K
CREATE NONCLUSTERED INDEX [IndiceDel] ON [dbo].[pz_t_mov]
(
ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, FILLFACTOR = 80, ONLINE = OFF) ON [PRIMARY]
Ok, this is the table side. Now we want to have a tool, which insertes in every dabase-table a trigger which will be only fired once (just to capt that there is some moving).
For INSERT
use myTable
/***************************************************************/
/* */
/* Codigo para crear un trigger en cada tabla de una */
/* base de datos. */
/* */
/* 08/07/2012 Patrick Zumstein pzlingo[at]gmail.com*/
/***************************************************************/
/* Crear tabla que contiene los nombres de todas las tablas */
create table #allTables
(
tablavarchar(255)
)
/* Llenar la tabla temporal con los nombre de todas las tablas */
insert into #allTables (tabla)
(
SELECT INFOS.[TABLE_NAME]
FROM INFORMATION_SCHEMA.Tables AS INFOS
WHERE TABLE_TYPE = 'BASE TABLE'
)
declare @miTabla varchar(255)
DECLARE @supercmd NVARCHAR(2500)
DECLARE curSUPER CURSOR FOR
select tabla from #allTables
open curSUPER
/* Iterar por todas las tablas */
FETCH NEXT FROM curSUPER INTO @miTabla
WHILE @@FETCH_STATUS = 0
BEGIN
/* Crear el comando que se debe ejecutar en cada tabla */
/* NOTA: Este comando contiene otro comando para ejecutarse posteriormente (Autodesactivación) */
SET @supercmd ='CREATE TRIGGER [dbo].[' + @miTabla + '_T_I] ON [dbo].[' + @miTabla + '] FOR INSERT AS
DECLARE @C AS VARCHAR(500)
SET @C = ''' + 'ALTER TABLE ' + @miTabla + '' + ' DISABLE TRIGGER ' + @miTabla + '_T_I;''' +
'
IF EXISTS (select tabla from pz_t_mov where tabla = ''' + @miTabla + ''' )
BEGIN IF (select ins from pz_t_mov where tabla = ''' + @miTabla + ''' ) = 0
BEGIN update pz_t_mov set ins = 1, fecha_ins = getdate() where tabla = ''' + @miTabla + '''
EXEC (@c) END END ELSE BEGIN
insert into pz_t_mov (tabla, ins, fecha_creacion, fecha_ins) values (''' + @miTabla + ''', 1, getdate(), getdate())
EXEC (@c) END'
/* Ejecutar el comando */
EXEC (@supercmd)
SET @supercmd = ''
FETCH NEXT FROM curSUPER INTO @miTabla
END
CLOSE curSUPER
DEALLOCATE curSUPER
/* Borrar tabla temporal */
drop table #allTables
For Update (you will note that is is almost similar)
use sofipan
/***************************************************************/
/* */
/* Codigo para crear un trigger en cada tabla de una */
/* base de datos. */
/* */
/* 08/07/2012 Patrick Zumstein */
/***************************************************************/
/* Crear tabla que contiene los nombres de todas las tablas */
create table #allTables
(
tablavarchar(255)
)
/* Llenar la tabla temporal con los nombre de todas las tablas */
insert into #allTables (tabla)
(
SELECT INFOS.[TABLE_NAME]
FROM INFORMATION_SCHEMA.Tables AS INFOS
WHERE TABLE_TYPE = 'BASE TABLE'
)
declare @miTabla varchar(255)
DECLARE @supercmd NVARCHAR(2500)
DECLARE curSUPER CURSOR FOR
select tabla from #allTables
open curSUPER
/* Iterar por todas las tablas */
FETCH NEXT FROM curSUPER INTO @miTabla
WHILE @@FETCH_STATUS = 0
BEGIN
/* Crear el comando que se debe ejecutar en cada tabla */
/* NOTA: Este comando contiene otro comando para ejecutarse posteriormente (Autodesactivación) */
SET @supercmd ='CREATE TRIGGER [dbo].[' + @miTabla + '_T_U] ON [dbo].[' + @miTabla + '] FOR UPDATE AS
DECLARE @C AS VARCHAR(500)
SET @C = ''' + 'ALTER TABLE ' + @miTabla + '' + ' DISABLE TRIGGER ' + @miTabla + '_T_U;''' +
'
IF EXISTS (select tabla from pz_t_mov where tabla = ''' + @miTabla + ''' )
BEGIN IF (select upd from pz_t_mov where tabla = ''' + @miTabla + ''' ) = 0
BEGIN update pz_t_mov set upd = 1, fecha_upd = getdate() where tabla = ''' + @miTabla + '''
EXEC (@c) END END ELSE BEGIN
insert into pz_t_mov (tabla, upd, fecha_creacion, fecha_upd) values (''' + @miTabla + ''', 1, getdate(), getdate())
EXEC (@c) END'
/* Ejecutar el comando */
EXEC (@supercmd)
SET @supercmd = ''
FETCH NEXT FROM curSUPER INTO @miTabla
END
CLOSE curSUPER
DEALLOCATE curSUPER
/* Borrar tabla temporal */
drop table #allTables
For Delete
use pos_jardines
/***************************************************************/
/* */
/* Codigo para crear un trigger en cada tabla de una */
/* base de datos. */
/* */
/* 08/07/2012 Patrick Zumstein */
/***************************************************************/
/* Crear tabla que contiene los nombres de todas las tablas */
create table #allTables
(
tablavarchar(255)
)
/* Llenar la tabla temporal con los nombre de todas las tablas */
insert into #allTables (tabla)
(
SELECT INFOS.[TABLE_NAME]
FROM INFORMATION_SCHEMA.Tables AS INFOS
WHERE TABLE_TYPE = 'BASE TABLE'
)
declare @miTabla varchar(255)
DECLARE @supercmd NVARCHAR(2500)
DECLARE curSUPER CURSOR FOR
select tabla from #allTables
open curSUPER
/* Iterar por todas las tablas */
FETCH NEXT FROM curSUPER INTO @miTabla
WHILE @@FETCH_STATUS = 0
BEGIN
/* Crear el comando que se debe ejecutar en cada tabla */
/* NOTA: Este comando contiene otro comando para ejecutarse posteriormente (Autodesactivación) */
SET @supercmd ='CREATE TRIGGER [dbo].[' + @miTabla + '_T_D] ON [dbo].[' + @miTabla + '] FOR DELETE AS
DECLARE @C AS VARCHAR(500)
SET @C = ''' + 'ALTER TABLE ' + @miTabla + '' + ' DISABLE TRIGGER ' + @miTabla + '_T_D;''' +
'
IF EXISTS (select tabla from pz_t_mov where tabla = ''' + @miTabla + ''' )
BEGIN IF (select del from pz_t_mov where tabla = ''' + @miTabla + ''' ) = 0
BEGIN update pz_t_mov set del = 1, fecha_del = getdate() where tabla = ''' + @miTabla + '''
EXEC (@c) END END ELSE BEGIN
insert into pz_t_mov (tabla, del, fecha_creacion, fecha_del) values (''' + @miTabla + ''', 1, getdate(), getdate())
EXEC (@c) END'
/* Ejecutar el comando */
EXEC (@supercmd)
SET @supercmd = ''
FETCH NEXT FROM curSUPER INTO @miTabla
END
CLOSE curSUPER
DEALLOCATE curSUPER
/* Borrar tabla temporal */
drop table #allTables
Ok, this is all. You need to run the last three scripts during night, when no person is using SQL-Server, because of the blocks. It will create a table where you can see what exactly happens in your database, which tables are used.
I decided to desactivate the trigger after first execution, to evitate overhead, but it could easyly be extended to know which are the tables that a re used most.
Not sure if there are other methods which are better, I just like to share this with all.
Happy Testing
Patrick

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

pzlingo Valued Member Points: 71 More actions · Answer 1

Just add another information

Select statementss cannot be triggered normally, but there are some turotials on internet to create views to do the same job. For my was useless, because y know the paramenter-tables of our database.

Happy Felling

pzlingo Valued Member Points: 71 More actions · Answer 2

Last commnent: If all study works has finished, you can easily drop all the triggers at onnce,.

Here the example for the DEL Trigger

use AnyDatabase

/***************************************************************/

/* */

/* Codigo para borrar un trigger en cada tabla de una */

/* base de datos. */

/* */

/* 08/07/2012 Patrick Zumstein */

/***************************************************************/

/* Crear tabla que contiene los nombres de todas las tablas */

create table #allTables

(

tablavarchar(255)

)

/* Llenar la tabla temporal con los nombre de todas las tablas */

insert into #allTables (tabla)

(

SELECT INFOS.[TABLE_NAME]

FROM INFORMATION_SCHEMA.Tables AS INFOS

WHERE TABLE_TYPE = 'BASE TABLE'

)

declare @miTabla varchar(255)

DECLARE @supercmd NVARCHAR(2500)

DECLARE curSUPER CURSOR FOR

select tabla from #allTables

open curSUPER

/* Iterar por todas las tablas */

FETCH NEXT FROM curSUPER INTO @miTabla

WHILE @@FETCH_STATUS = 0

BEGIN

/* Crear el comando que se debe ejecutar en cada tabla */

/* NOTA: Este comando contiene otro comando para ejecutarse posteriormente (Autodesactivación) */

SET @supercmd ='

DECLARE @C AS varchar(500)

SET @C = ''' + 'DROP TRIGGER ' + @miTabla + '_T_D;''

EXEC (@c)'

/* Ejecutar el comando */

EXEC (@supercmd)

SET @supercmd = ''

FETCH NEXT FROM curSUPER INTO @miTabla

END

CLOSE curSUPER

DEALLOCATE curSUPER

/* Borrar tabla temporal */

drop table #allTables

Ok, hope it helps, and happy SQL .....

pzlingo Valued Member Points: 71 More actions · Answer 3

Need to eplain a little better (Y am spanish speeking)

This tool will create a table, and the 3 Scripts will create a trigger in every table of the user-database.

The trigger will load the created table with the dada it there are inserts, updates or deletes.

Once the trigger was fired, it desactivates itself.

Waiting some time, you will know exactly what is happened in the database, it will help a lot in case of migragion, but as I mencioned, with some little changes you can have an order of the "most used" tables..

Glad to help

Patrick

pzlingo Valued Member Points: 71 More actions · Answer 4

Last addon:

It is quite simple to detect the parameter tables, if U use the scrips I posted. You will need to create another table where the tables are stored with are not included in the tables the are used for insert, update, select, omiting thonse with 0 Records.

For the moment, I won't post the code here, I think the information is sufficient useful.

----> Never use select count(*) to get te amount of records in a table!!!

Greets from Paraguay

Patrick