Compter des enregistrements pour chaque table

**piflechien73** · 18/11/2011, 11h59

Bonjour,

je cherche à créer une Procédure stockée qui compte les enregistrements de toutes les tables d'une base et qui somme toutes les valeurs de chaque colonne de chaque table.

J'ai attrapé sur le net une procédure qui affiche toutes les tables et colonnes qui marche bien:

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
 
USE [BPI]
GO
/****** Object:  StoredProcedure [dbo].[spOther_ListTablesAndColumns]    Script Date: 11/18/2011 11:50:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spOther_ListTablesAndColumns]
 
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
SELECT C.Table_Catalog DB,C.Table_Schema, C.Table_Name, Column_Name, Data_Type
 
FROM Information_Schema.Columns C JOIN Information_Schema.Tables T
 
ON C.table_name = T.table_name
 
WHERE Table_Type = 'BASE TABLE'
 
 
END

J'ai également développé à côté une procédure qui compte les enregistrements d'une table :

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
 
USE [BPI]
GO
/****** Object:  StoredProcedure [dbo].[spOther_CountRecordsTables]    Script Date: 11/18/2011 11:51:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[spOther_CountRecordsTables] 
 
AS
DECLARE @CountTable nvarchar(200)
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
 
    -- Insert statements for procedure here
	select @CountTable=COUNT(*) FROM [tblDistribution_base]
	Print'There are '+@CountTable+' records'
	END

Du coup j'ai essayé de faire une synergie des 2 procédures:

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
 
USE [BPI]
GO
/****** Object:  StoredProcedure [dbo].[spOther_ListCountSumTablesAndColumns]    Script Date: 11/18/2011 11:36:47 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
alter PROCEDURE [dbo].[spOther_ListCountSumTablesAndColumns]
 
AS
DECLARE @CountTable nvarchar(200)
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
SELECT C.Table_Catalog DB,C.Table_Schema, C.Table_Name, Column_Name, Data_Type, @CountTable=COUNT(*)
 
FROM Information_Schema.Columns C JOIN Information_Schema.Tables T
 
ON C.table_name = T.table_name
 
WHERE Table_Type = 'BASE TABLE'
 
 
END

Mais cela ne fonctionne pas.

J'ai ce message

Msg 141, Level 15, State 1, Procedure spOther_ListCountSumTablesAndColumns, Line 14
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

qui me laisse penser que je ne peux pas afficher un calcul à côté des colonnes qui affichent le nom des tables et des colonnes.

Je démarre sous SQL Server et du coup je ne sais pas comment faire. Quelqu'un peut m'aider pour pouvoir compter les enregistrements aussi sommer les valeurs de chaque colonne?

Merci par avance

**iberserk** · 18/11/2011, 12h06

Vous pouvez passer par les dmv systèmes et vous servir des physical stats sur les indexes...

**Jinroh77** · 18/11/2011, 12h12

Quelque chose comme ça :

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
;
WITH Alltables AS (
select distinct ta.name, pa.rows
from sys.tables ta
JOIN sys.partitions pa
	ON ta.object_id = pa.object_id
--where 
--	ta.name like '%TMP%'
--	AND ta.name not like '%TEMP%'
)
SELECT 
	name
	,rows AS NbrLignes
from Alltables 
order by name

**mikedavem** · 18/11/2011, 12h47

ou comme ca (si jamais vous avez une table partitionnée) :

Code :

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

1
2
3
4
5
6
7
8
9
select 
 T.name,
 SUM(P.[rows]) AS [rows]
from sys.tables AS T
inner join sys.partitions AS P
 ON T.object_id = P.object_id 
WHERE P.index_id = 0
 OR P.index_id = 1
GROUP BY T.name

++

**zinzineti** · 18/11/2011, 13h06

voici une procédure old school

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
 
CREATE PROCEDURE P_TableInfos AS 
BEGIN
SET NOCOUNT ON
--> Créer dans la base msdb une table pour stocker les infos des tables et index à auditer
CREATE TABLE #T_AUDIT_TABLES 
  ( 
  [NomTable] nvarchar(100), 
  [NbLignes] nchar(100) NULL, 
  [EspacePrevu] varchar(18) NULL, 
  [TailleData] varchar(18) NULL, 
  [TailleIndex] varchar(18) NULL, 
  [EspaceInutilise] varchar(18)  
  ) ON [PRIMARY] 
-- Se positionner sur la base à auditer  
DECLARE @TabEnCoursTraitement VARCHAR(100),@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 #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 
-->AUDIT DES TABLES DE LA BASE DE DONNEES
/****************
-->1. Quelles sont les tables les plus grosses 
SELECT 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 #T_AUDIT_TABLES 
ORDER BY [TailleData (en Ko)] DESC 
****************/
-->2.Les tables et le nombre de lignes
SELECT NomTable 
, cast([NbLignes] as int) AS [Nombre de Lignes] 
FROM #T_AUDIT_TABLES 
ORDER BY [Nombre de Lignes] DESC 
 
DROP TABLE #T_AUDIT_TABLES
END

**Sergejack** · 18/11/2011, 13h46

Il y a ce genre de solution aussi :

Code :

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

EXEC sp_MSforeachtable 'SELECT ''?'', COUNT(*) FROM ?'

**piflechien73** · 19/11/2011, 18h29

Hello à tous,

Merci beaucoup pour vos réponses. Je me rends compte que SQL Server offre bien plus de possibilités qu'Access car il existe donc des fonctions systèmes qui interrogent directement toutes les tables.

J'ai testé toutes les solutions et elles marchent toutes, cependant je n'arrive pas à obtenir le résultat final dans un fichier à plat:

Table - Nb d'enregistrements de la table - Colonne
-----------------------------------------------------------------------

Cela signifie que n fois la table nommée si elle a n colonnes, mais cela me va bien.

Je peux bien sûr croiser le résultat la procédure stockée ci dessous qui liste les tables et colonnes

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
USE [BPI]
GO
/****** Object:  StoredProcedure [dbo].[spOther_ListTablesAndColumns]    Script Date: 11/19/2011 18:23:00 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
 
ALTER PROCEDURE [dbo].[spOther_ListTablesAndColumns]
 
 
AS
 
BEGIN
 
           -- SET NOCOUNT ON added to prevent extra result sets from
 
           -- interfering with SELECT statements.
 
           SET NOCOUNT ON;
 
SELECT C.Table_Catalog DB,C.Table_Schema, C.Table_Name, Column_Name, Data_Type
 
 
FROM Information_Schema.Columns C JOIN Information_Schema.Tables T
 
 
ON C.table_name = T.table_name
 
 WHERE Table_Type = 'BASE TABLE'
 
 
END

avec le résultat d'une de vos solutions, mais peut être existe t'il une fonction système qui permet de tout lister en une procédure?

J'ai cherché mais en vain. Merci si là encore quelquún peut m'aider.

**zinzineti** · 19/11/2011, 19h40

Envoyé par piflechien73

Hello à tous,

J'ai testé toutes les solutions et elles marchent toutes, cependant je n'arrive pas à obtenir le résultat final dans un fichier à plat:

Table - Nb d'enregistrements de la table - Colonne
-----------------------------------------------------------------------

Manuellement dans SSMS :

Tapes

EXEC taProcedure

clique-droit > Résultats dans > Résultats dans un fichier

...

**piflechien73** · 19/11/2011, 20h14

Envoyé par zinzineti

Manuellement dans SSMS :

Tapes

EXEC taProcedure

clique-droit > Résultats dans > Résultats dans un fichier

...

Rebonjour,

Désolé, je n'ai pas été assez clair. Je voulais dire que les solutions jusque là ne donnent pas "Colonne"

Table - Nb d'enregistrements de la table - Colonne
(Ci dessous une copie écran du résultat que j'essaie d'obtenir)

J'ai le nom des tables, le nombre d'enregistrements mais les PS jusque là plus haut ne donnent pas dans le nom des colonnes et les sommes associées.

Quelqu'un peut m'aider?

Compter des enregistrements pour chaque table

Développement SQL Server

Discussions similaires

Partager

Partager