Discussion :

[davidkungfu]

Bonjour,

J'aurais besoin d'aide pour écrire la requete pour m'afficher le nombre d'enregistrements dans chaque table utilisateur avec colonne 1 = nom de la table et colonne 2 = nbre d'enregistrement.

Merci d'avance

David

[zinzineti]

Cette requête je l'utilise pour auditer les tables, objectif : purger

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
USE msdb 
GO 
IF OBJECT_ID('dbo.T_AUDIT_TABLES', 'U') IS NOT NULL 
  DROP TABLE dbo.T_AUDIT_TABLES 
GO 
CREATE TABLE msdb.dbo.T_AUDIT_TABLES 
  ( 
  [NomTable] nvarchar(20), 
  [NbLignes] char(11) NULL, 
  [EspacePrevu] varchar(18) NULL, 
  [TailleData] varchar(18) NULL, 
  [TailleIndex] varchar(18) NULL, 
  [EspaceInutilise] varchar(18)  
  ) ON [PRIMARY] 
GO 
-- Se positionner sur la base à auditer  
USE [TaBase]  
GO 
DECLARE @TabEnCoursTraitement VARCHAR(50),@SQL VARCHAR (100), @DEL VARCHAR (100) 
-- Stocker le nom des tables de la base dans une table temporaire 
CREATE TABLE #LIST_TABLES (nom_table varchar (50)) 
INSERT INTO #LIST_TABLES  
SELECT TABLE_SCHEMA+'.'+TABLE_NAME AS [NomTable] 
FROM information_schema.tables 
WHERE table_type='BASE TABLE' 
ORDER BY [NomTable] 
-- Tant qu'il y a des lignes dans la table temporaire 
WHILE EXISTS ( SELECT nom_table  
  FROM #LIST_TABLES 
  )  
BEGIN 
--Se positionner sur la 1ère ligne de la table temporaire 
SET @TabEnCoursTraitement = (SELECT TOP 1 nom_table FROM #LIST_TABLES) 
-- Insérer les infos de la table en cours de traitement dans la table qui stocke les infos à auditer  
SET @SQL = 'INSERT INTO msdb.dbo.T_AUDIT_TABLES EXEC sp_spaceused '''+ @TabEnCoursTraitement + ''';' 
EXEC (@SQL);  
-- Supprimer la table traitée de la table temporaire 
SET @DEL = 'DELETE FROM #LIST_TABLES WHERE nom_table = '''+ @TabEnCoursTraitement + ''';' 
EXEC (@DEL) 
END 
--Supprimer la table temporaire 
DROP TABLE #LIST_TABLES 
 
--> Quelles sont les 10 tables ayant le plus de lignes
 
SELECT TOP 10 NomTable 
, cast([NbLignes] as int) AS [Nombre de Lignes] 
FROM msdb.dbo.T_AUDIT_TABLES 
ORDER BY [Nombre de Lignes] DESC 
 
--> Quelles sont les 10 tables les plus grosses
SELECT TOP 10 NomTable 
, cast(replace([TailleData],'KB','') as int) AS [TailleData (en Ko)] 
, cast(replace([TailleIndex],'KB','') as int) AS [TailleIndex (en Ko)] 
,cast(replace([EspacePrevu],'KB','') as int) AS [EspaceReservé (en Ko)] 
,cast(replace([EspaceInutilise],'KB','') as int) AS [EspaceInutilisé (en Ko)] 
FROM msdb.dbo.T_AUDIT_TABLES 
ORDER BY [TailleData (en Ko)] DESC

Pour plus de détails

[SQLpro]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
DECLARE @SQL VARCHAR(max);
SET @SQL = '';
SELECT @SQL = @SQL + 'SELECT COUNT(*) AS N, ''' + TABLE_SCHEMA + '.' 
          +  TABLE_NAME +''' AS NOM_TABLE FROM ' 
       + TABLE_SCHEMA + '.' + TABLE_NAME +' UNION ALL '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'       
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 10)
EXECUTE (@SQL)

A +

[zinzineti]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

DECLARE @SQL VARCHAR(max);

A +

Il me semble que SQL 2000 n'accepte pas varchar (max).

En remplaçant varchar (max) par varchar(8000) on obtient bien le résultat excompté.

[zinzineti]

J'ai ajouté un tri sur le script de sqlpro

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
DECLARE @SQL VARCHAR(8000);
SET @SQL = '';
SELECT @SQL = @SQL + 'SELECT COUNT(*) AS N, ''' + TABLE_SCHEMA + '.' 
          +  TABLE_NAME +''' AS NOM_TABLE FROM ' 
       + TABLE_SCHEMA + '.' + TABLE_NAME +' UNION ALL '
FROM   INFORMATION_SCHEMA.TABLES
WHERE  TABLE_TYPE = 'BASE TABLE'   
SET @SQL = SUBSTRING(@SQL, 1, LEN(@SQL) - 10) + ' ORDER BY N DESC'
EXECUTE (@SQL)

[davidkungfu]

Merci à tous!

David