Comment savoir dans quels « storage », répertoires et fichier ou sont stockées mes données ?
Sachant que cela peut être une table, un index, un filestream et que tables et index peuvent être partitionnés…
Voici la requête magique :
SELECT s.name AS SCHEMA_NAME, o.name AS OBJECT_NAME, o.type_desc AS OBJECT_TYPE,
ds.name AS STORAGE, dbf.physical_name AS FILENAME, ds.type_desc AS STORAGE_TYPE,
i.name AS index_name, i.index_id, i.type_desc AS index_type,
p.partition_id, p.partition_number as pnum, p.rows as partition_rows,
a.allocation_unit_id as alloc_unit_id, a.type_desc as page_type_desc, a.total_pages as pages,
internals.total_pages, internals.used_pages, internals.data_pages,
first_page, root_page, first_iam_page
FROM sys.objects AS o
INNER JOIN sys.schemas AS s
ON o.schema_id = s.schema_id
INNER JOIN sys.indexes AS i
ON o.object_id = i.object_id
INNER JOIN sys.partitions p
ON p.object_id = i.object_id
AND p.index_id = i.index_id
INNER JOIN sys.allocation_units a
ON a.container_id = p.partition_id
INNER JOIN sys.data_spaces ds
ON ds.data_space_id = a.data_space_id
LEFT OUTER JOIN sys.database_files dbf
ON dbf.data_space_id = ds.data_space_id
LEFT OUTER JOIN sys.system_internals_allocation_units internals
on internals.container_id = a.container_id
--------
Frédéric Brouard, SQLpro - ARCHITECTE DE DONNÉES, http://sqlpro.developpez.com/
Expert bases de données relationnelles et langage SQL. MVP Microsoft SQL Server
www.sqlspot.com : modélisation, conseil, audit, optimisation, tuning, formation
* * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *
Allez je me suis amusé à « traduire » les pages en choses humainement lisibles
[code]
SELECT S.name + ‘.’ + O.name AS object_name
, O.type_desc AS object_type
, DS.name AS dataspace_name
, DBF.physical_name AS file_name
, DS.type_desc AS storage_type
, I.name AS index_name
, I.index_id
, I.type_desc AS index_type
, P.partition_id
, P.partition_number AS partition_#
, P.rows AS partition_rows
, A.allocation_unit_id AS alloc_unit_id
, A.type_desc AS page_type_desc
, A.total_pages AS pages
, IAU.total_pages
, IAU.used_pages
, IAU.data_pages
, IAU.first_page
, IAU.root_page
, IAU.first_iam_page
, ‘(‘ + CONVERT
(
varchar(6)
, CONVERT
(
int
, SUBSTRING(IAU.first_page, 6, 1)
+ SUBSTRING(IAU.first_page, 5, 1))) + ‘:’
+ CONVERT
(
varchar(20)
, CONVERT
(
int
, SUBSTRING(IAU.first_page, 4, 1)
+ SUBSTRING(IAU.first_page, 3, 1)
+ SUBSTRING(IAU.first_page, 2, 1)
+ SUBSTRING(IAU.first_page, 1, 1)
)
)
+ ‘)’ AS first_page
, ‘(‘ + CONVERT
(
varchar(6)
, CONVERT
(
int
, SUBSTRING(IAU.root_page, 6, 1)
+ SUBSTRING(IAU.root_page, 5, 1))) + ‘:’
+ CONVERT
(
varchar(20),
CONVERT
(
int
, SUBSTRING(IAU.root_page, 4, 1)
+ SUBSTRING(IAU.root_page, 3, 1)
+ SUBSTRING(IAU.root_page, 2, 1)
+ SUBSTRING(IAU.root_page, 1, 1)
)
)
+ ‘)’ AS [Root Page],
‘(‘ + CONVERT
(
varchar(6)
, CONVERT
(
int
, SUBSTRING(IAU.first_iam_page, 6, 1)
+ SUBSTRING(IAU.first_iam_page, 5, 1)
)
)
+ ‘:’
+ CONVERT
(
varchar(20)
, CONVERT
(
int
, SUBSTRING(IAU.first_iam_page, 4, 1)
+ SUBSTRING(IAU.first_iam_page, 3, 1)
+ SUBSTRING(IAU.first_iam_page, 2, 1)
+ SUBSTRING(IAU.first_iam_page, 1, 1)
)
) +
‘)’ AS [First IAM Page]
FROM sys.objects AS O
INNER JOIN sys.schemas AS S
ON O.schema_id = S.schema_id
INNER JOIN sys.indexes AS I
ON O.object_id = I.object_id
INNER JOIN sys.partitions AS P
ON P.object_id = I.object_id
AND P.index_id = I.index_id
INNER JOIN sys.allocation_units AS A
ON A.container_id = P.partition_id
INNER JOIN sys.data_spaces AS DS
ON DS.data_space_id = A.data_space_id
LEFT JOIN sys.database_files AS DBF
ON DBF.data_space_id = DS.data_space_id
INNER JOIN sys.system_internals_allocation_units AS IAU
ON IAU.container_id = A.container_id
WHERE O.type IN (‘U’, ‘V’)[/code]