[2012] SP_MSforeachdb taille de variable

Ah, j'ai trouvé la réponse.

Ca marche avec un curseur :ccool: :
Code:
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
USE master
GO
 
CREATE  TABLE #TABLE_TABLE (TABLE_TYPE VARCHAR(255), DATABASE_NAME VARCHAR(255), TABLE_NAME VARCHAR(255), CONTROL_NAME BIT)
 
DECLARE @DB_NAME NVARCHAR(100)
DECLARE @QUERY NVARCHAR(MAX)
 
DECLARE CUR_BASES CURSOR
FOR SELECT name FROM sys.databases
WHERE database_id > 4
 
OPEN CUR_BASES
 
FETCH NEXT FROM CUR_BASES INTO @DB_NAME
 
WHILE @@FETCH_STATUS = 0
BEGIN
 
IF @DB_NAME LIKE 'DataMart%'
BEGIN
SET @QUERY =
 
'
INSERT INTO #TABLE_TABLE 
SELECT
	CASE
		WHEN TABLE_NAME LIKE ''zzz%'' THEN ''A supprimer''
		WHEN TABLE_NAME LIKE ''REPORT%'' THEN ''Table qui sert à alimenter les rapports SSRS''
		WHEN TABLE_NAME LIKE ''RESTIT%'' THEN ''Table de données utilisées par les vues dans Publication_RESTITS''
		WHEN TABLE_NAME LIKE ''HISTO%'' THEN ''Table historisation de données des indicateurs'' ELSE ''Non classé !''
	END AS TABLE_TYPE,
	''' + @DB_NAME + ''' AS DATABASE_NAME,
	TABLE_NAME,
	CASE
		WHEN TABLE_NAME LIKE ''zzz%'' THEN 1
		WHEN TABLE_NAME LIKE ''REPORT%'' THEN 0
		WHEN TABLE_NAME LIKE ''RESTIT%'' THEN 0
		WHEN TABLE_NAME LIKE ''HISTO%'' THEN 0 ELSE 1
	END AS CONTROL_NAME
FROM ' + @DB_NAME + '.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = ''BASE TABLE''
'
 
EXEC sys.sp_executesql @QUERY
FETCH NEXT FROM CUR_BASES INTO @DB_NAME
END
END
 
CLOSE CUR_BASES
DEALLOCATE CUR_BASES
 
SELECT * FROM #TABLE_TABLE
ORDER BY TABLE_TYPE, TABLE_NAME
 
DROP TABLE #TABLE_TABLE
J'ai plusieurs IF @DB_NAME LIKE....

Usine à gaz ce truc!