SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
pzlingo
pzlingo
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
pzlingo
pzlingo
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
pzlingo
pzlingo
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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 .....
pzlingo
pzlingo
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
pzlingo
pzlingo
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search