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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SQL Server 2005 Strategies » Awaiting Migration: How to know which tables...

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

Rate Topic

Display Mode

Topic Options

Author

Message

pzlingo

Posted Wednesday, July 11, 2012 2:38 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 4:45 PM
Points: 5, Visits: 20

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)),
[del] [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]
(
[del] 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
(
tabla varchar(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
(
tabla varchar(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
(
tabla varchar(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

Post #1328109

pzlingo

Posted Wednesday, July 11, 2012 3:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 4:45 PM
Points: 5, Visits: 20

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

Post #1328128

pzlingo

Posted Wednesday, July 11, 2012 3:26 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 4:45 PM
Points: 5, Visits: 20

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
(
tabla varchar(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 .....

Post #1328134

pzlingo

Posted Wednesday, July 11, 2012 7:58 AM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 4:45 PM
Points: 5, Visits: 20

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

Post #1328291

pzlingo

Posted Wednesday, July 18, 2012 4:50 PM

Forum Newbie

Group: General Forum Members
Last Login: Wednesday, July 18, 2012 4:45 PM
Points: 5, Visits: 20

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

Post #1331871

« Prev Topic | Next Topic »

Permissions