Discussion :

[Jinroh77]

Bonjour, je cherche à écrire une requête me remontant certaines données.
Pour garder cela dans les règles de l'art j'aimerai éviter d'utiliser un curseur, et peut-être du SQL dynamique (ça semble plus compliqué...)

Le besoin
J'ai besoin de compter les nombres de lignes présentes dans des tables selon la valeur d'un filtre disponible dans une autre du même schéma au nom toujours identique...

Je précise que je suis dans un contexte décisionnel, que les tables en question sont des tables de faits que je vide tous les jours et que chacun des tables de fait (ou presque) est dans un schéma différent..

Toutes les tables de faits ont une colonne DateId et chacun des schéma à une table RefDateProcess avec une colonne DateId.

Pour chacune des tables de fait je dois faire la jointure avec la table RefDateProcess correspondante sur cette date et compter le nombre de lignes.

Le tout est ensuite d'insérer ces valeurs de comptage dans une table de log.

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
 
CREATE SCHEMA [sc1] AUTHORIZATION [dbo]
CREATE SCHEMA [sc2] AUTHORIZATION [dbo]
GO
 
CREATE TABLE sc1.Ft (DateId INT, Code VARCHAR(MAX))
CREATE TABLE sc2.Ft (DateId INT, Code VARCHAR(MAX))
CREATE TABLE sc1.RefDateProcess (DateId INT)
CREATE TABLE sc2.RefDateProcess (DateId INT)
GO
 
-- Ma liste de tables
select 
	sc.name, 
	tb.name
from 
	sys.tables tb
	JOIN sys.schemas sc
		ON sc.schema_id = tb.schema_id
where
	tb.name like 'Ft%'
 
 
-- Le comptage pour une table
select COUNT(ft.DateId)
from sc1.Ft ft
JOIN sc1.RefDateProcess ref
	ON ref.DateId = ft.DateId

Le tout est alors "d'intégrer" la seconde requête dans la première

Sachant que les tables de faits peuvent ne contenir aucune ligne bien sûr (ce que je chercher à repérer).

Tout cela sera ensuite intégré à un report qui contiendra une date, envoyée en paramètre à une procédure qui nettoies la table de logs sur la date de la RefDateProcess puis recompte le nombre de lignes.

[elsuket]

Bonjour,

Il est un peu tard ici donc la requête suivante est probablement à réécrire de façon plus élégante, mais le principe est là :

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
SET NOCOUNT ON
GO
 
DECLARE @sql nvarchar(max)
 
DECLARE @t TABLE
(
	sch_name sysname
	, table_name sysname
	, occurences int
)
 
SELECT	@sql = CASE WHEN @sql IS NULL THEN '' ELSE @sql END
		+ 'SELECT ''' + S.name + ''',''' + T.name + ''',COUNT(*) FROM ' + S.name + '.' + T.name + ' AS ft INNER JOIN ' + S.name + '.RefDateProcess AS ref ON ft.DateId = ref.DateId'
		+ '' + CHAR(13) + CHAR(10) + 'UNION ALL' + CHAR(13) + CHAR(10)
FROM		sys.schemas AS S
INNER JOIN	sys.TABLES AS T ON S.schema_id = T.schema_id
WHERE		T.name LIKE 'Ft%'
 
 
SELECT @sql = LEFT(@sql, LEN(@sql) - 11)
 
PRINT @sql
 
INSERT	@t
EXEC	sp_executeSQL @sql
 
SELECT	sch_name
	, table_name
	, occurences
FROM	@t

@++

[elsuket]

Ha ben téléscopage

[Jinroh77]

Merci beaucoup pour ces 2 requêtes, exactement ce qu'il me fallait

[SQLpro]

Ceci va vous donner la requête SQL qui exécute tout :

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
WITH
T0 AS
(
SELECT T1.TABLE_SCHEMA +'.'+ T1.TABLE_NAME AS TB1, 
       T2.TABLE_SCHEMA +'.'+ T2.TABLE_NAME AS TB2,
       ROW_NUMBER() OVER(ORDER BY T1.TABLE_SCHEMA, T1.TABLE_NAME) AS N
FROM   INFORMATION_SCHEMA.TABLES AS T1
       INNER JOIN INFORMATION_SCHEMA.TABLES AS T2
             ON T1.TABLE_SCHEMA = T2.TABLE_SCHEMA
                AND T1.TABLE_NAME <> T2.TABLE_NAME
WHERE  T1.TABLE_TYPE = 'BASE TABLE' 
  AND  T1.TABLE_NAME LIKE 'Ft%'
),
T1 AS
(
SELECT CAST('SELECT ''' + TB1 +''', COUNT(ft.DateId) FROM ' 
       + TB1 + ' AS ft INNER JOIN '
       + TB2 + ' AS ref ON ref.DateId = ft.DateId ' 
       AS NVARCHAR(max)) AS C, N
FROM   T0
WHERE  N = 1
UNION ALL
SELECT T1.C + ' UNION ALL ' 
       + 'SELECT ''' + TB1 +''', COUNT(ft.DateId) FROM ' 
       + TB1 + ' AS ft INNER JOIN '
       + TB2 + ' AS ref ON ref.DateId = ft.DateId '
       , T0.N
FROM   T0
       INNER JOIN T1
             ON T1.N +1 = T0.N
)
SELECT TOP 1 C 
FROM   T1
ORDER BY N DESC

A vous de la mettre dans une variable et de faire un EXEC (sql dynamique).

A +