Discussion :

[SINDIZ]

Bonjour,

Savez-vous si on peut connaitre le nombre d'occurrence d'une table ou d'une vue en utilisant les tables system car théoriquement les statistiques stockent cette information mais où ???

Merci de votre aide

Thierry

[serge0934]

ci joint un script qui peut t'aider
il fonctionne depuis la version 2000

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
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
declare @id	int			
declare @type	character(2) 		
declare	@pages	int			
declare @dbname sysname
declare @dbsize dec(15,0)
declare @bytesperpage	dec(15,0)
declare @pagesperMB		dec(15,0)
 
create table #spt_space
(
	objid		int null,
	rows		int null,
	reserved	dec(15) null,
	data		dec(15) null,
	indexp		dec(15) null,
	unused		dec(15) null
)
 
set nocount on
 
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select	id
from	sysobjects
where	xtype = 'U'
 
open c_tables
 
fetch next from c_tables
into @id
 
while @@fetch_status = 0
begin
 
	/* Code from sp_spaceused */
	insert into #spt_space (objid, reserved)
		select objid = @id, sum(reserved)
			from sysindexes
				where indid in (0, 1, 255)
					and id = @id
 
	select @pages = sum(dpages)
			from sysindexes
				where indid < 2
					and id = @id
	select @pages = @pages + isnull(sum(used), 0)
		from sysindexes
			where indid = 255
				and id = @id
	update #spt_space
		set data = @pages
	where objid = @id
 
 
	/* index: sum(used) where indid in (0, 1, 255) - data */
	update #spt_space
		set indexp = (select sum(used)
				from sysindexes
				where indid in (0, 1, 255)
				and id = @id)
			    - data
		where objid = @id
 
	/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
	update #spt_space
		set unused = reserved
				- (select sum(used)
					from sysindexes
						where indid in (0, 1, 255)
						and id = @id)
		where objid = @id
 
	update #spt_space
		set rows = i.rows
			from sysindexes i
				where i.indid < 2
				and i.id = @id
				and objid = @id
 
	fetch next from c_tables
	into @id
end
 
select 	TableName = (select left(name,60) from sysobjects where id = objid),
	Lignes = convert(char(11), rows),
	RéservéKB = ltrim(str(reserved * d.low / 1024.,15,0) + ' ' + 'KB'),
	DonnéesKB = ltrim(str(data * d.low / 1024.,15,0) + ' ' + 'KB'),
	TailleIndexKB = ltrim(str(indexp * d.low / 1024.,15,0) + ' ' + 'KB'),
	LibreKB = ltrim(str(unused * d.low / 1024.,15,0) + ' ' + 'KB')
 
from 	#spt_space, master.dbo.spt_values d
where 	d.number = 1
and 	d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables

[cavo789]

Voici un code SQL 2005.

Code :

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

1
2
3
4
SELECT o.Name As TableName, i.rows As RecordCount
FROM sysobjects o, sysindexes i
WHERE (i.id = OBJECT_ID(o.name)) AND (i.indid < 2) AND (o.Name = 'NomDeMaTable')
ORDER BY o.Name