查询数据库、对象、磁盘等信息

-- 外键约束信息

;WITH
FK AS(
 SELECT
  foreign_schema_name = SCH.name,
  foreign_name = FK.name,
  FK.is_disabled,
  delete_action = FK.delete_referential_action_desc,
  update_action = FK.update_referential_action_desc,
  constraint_column_id = FKC.constraint_column_id,
  FKC.parent_object_id,
  FKC.parent_column_id,
  FKC.referenced_object_id,
  FKC.referenced_column_id
  FROM sys.foreign_keysFK
  INNER JOIN sys.foreign_key_columnsFKC
   ON FK.object_id = FKC.constraint_object_id
  INNER JOIN sys.schemasSCH
   ON FK.schema_id = SCH.schema_id
),
TB AS(
 SELECT
  TB.object_id,
  schema_name = SCH.name,
  table_name = TB.name,
  column_id = C.column_id,
  column_name = C.name
 FROM sys.tablesTB WITH(NOLOCK)
  INNER JOIN sys.columnsC WITH(NOLOCK)
   ON TB.object_id = C.object_id
  INNER JOIN sys.schemas SCH WITH(NOLOCK)
   ON TB.schema_id = SCH.schema_id
 WHERE TB.is_ms_shipped = 0  -- 此条件表示仅查询不是由内部 SQL Server 组件创建对象
)
SELECT
 FK.foreign_schema_name,
 FK.foreign_name,
 FK.is_disabled,
 FK.delete_action,
 FK.update_action,
 FK.constraint_column_id,
 parent_table_schema_name = TBP.schema_name,
 parent_table_name = TBP.table_name,
 parent_table_column_name = TBP.column_name,
 referenced_table_schema_name = TBR.schema_name,
 referenced_table_name = TBR.table_name,
 referenced_table_column_name = TBR.column_name
FROM FK
 INNER JOIN TB TBP
  ON FK.parent_object_id = TBP.object_id
   AND FK.parent_column_id = TBP.column_id
 INNER JOIN TB TBR
  ON FK.referenced_object_id = TBR.object_id
   AND FK.referenced_column_id = TBR.column_id
ORDER BY foreign_schema_name, parent_table_schema_name, parent_table_name, constraint_column_id

----对象依赖关系

;WITH
DEP AS(
 SELECT DISTINCT
  object_id,
  referenced_object_id = referenced_major_id
 FROM sys.sql_dependenciesD
 WHERE class IN(0, 1)
),
DEP_TREE AS(
 SELECT
  object_id,
  level = 0,
  path = CONVERT(varchar(8000),
    RIGHT(10000 + ROW_NUMBER() OVER(ORDER BY object_id), 4))
 FROM DEP A
 WHERE NOT EXISTS(
   SELECT * FROM DEP
   WHERE referenced_object_id = A.object_id)
 UNION ALL
 SELECT
  object_id = A.referenced_object_id,
  level = B.level + 1,
  path = CONVERT(varchar(8000),
    B.path +
    RIGHT(10000 + ROW_NUMBER() OVER(ORDER BY A.referenced_object_id), 4))
 FROM DEP A, DEP_TREE B
 WHERE A.object_id = B.object_id
  AND A.object_id <> A.referenced_object_id
),
DEP_INFO AS(
 SELECT
  schema_name = SCH.name,
  object_name = O.name,
  object_type = O.type_desc,
  DEP.level,
  DEP.path  
 FROM DEP_TREE DEP
  INNER JOIN sys.objectsO
   ON DEP.object_id = O.object_id
  INNER JOIN sys.schemas SCH
   ON O.schema_id = SCH.schema_id
)
SELECT
 object_name = REPLICATE(N' ', level * 2)
   + N'|- '
   + QUOTENAME(schema_name) + N'.' + QUOTENAME(object_name),
 object_type,
 level
FROM DEP_INFO
ORDER BY path

----表空间信息

;WITH
TB AS(
 SELECT
  TB.object_id,
  schema_name = SCH.name,
  table_name = TB.name
 FROM sys.tablesTB
  INNER JOIN sys.schemasSCH
   ON TB.schema_id = SCH.schema_id
 WHERE is_ms_shipped = 0    -- 此条件表示仅查询不是由内部 SQL Server 组件创建对象
),
PS AS(
 -- 此部分计算表空间的信息
 SELECT
  object_id,
  reserved_pages = SUM(reserved_page_count),
  used_pages = SUM(used_page_count),
  pages = SUM(
   CASE
    WHEN index_id > 1 THEN lob_used_page_count + row_overflow_used_page_count
    ELSE in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
   END),
  row_count = SUM (
   CASE
    WHEN index_id < 2 THEN row_count
    ELSE 0
   END)
 FROM sys.dm_db_partition_stats PS
 GROUP BY object_id
),
ITPS AS(
 -- 此部分计算包含 XML INDEX 和 FULLTEXT INDEXE 的空间信息(如果有的话)
 SELECT
  object_id = ITB.parent_id,
  reserved_pages = SUM(reserved_page_count),
  used_pages = SUM(used_page_count)
 FROMsys.dm_db_partition_statsP
  INNER JOIN sys.internal_tables ITB
   ON P.object_id = ITB.object_id
 WHERE ITB.internal_type IN(202, 204)
 GROUP BY ITB.parent_id
),
SIZE AS(
 -- 此部分合并所有的空间信息
 SELECT
  PS.object_id,
  reserved_pages = PS.reserved_pages + ISNULL(ITPS.reserved_pages, 0),
  used_pages = PS.used_pages + ISNULL(ITPS.used_pages, 0),
  PS.pages,
  PS.row_count
 FROM PS
  LEFT JOIN ITPS
   ON PS.object_id = ITPS.object_id
)
-- 显示最终的空间统计结果
-- 在前面的统计中，空间统计以页为单位, 8K/页,最终的统计将页数*8，得到KB为单位的空间大小
SELECT
 TB.schema_name,
 TB.table_name,
 SIZE.row_count,
 reserved = SIZE.reserved_pages * 8,
 data = SIZE.pages * 8,
 index_size = CASE
     WHEN SIZE.used_pages > SIZE.pages
      THEN SIZE.used_pages - SIZE.pages
     ELSE 0
    END * 8,
 unused = CASE
    WHEN SIZE.reserved_pages > SIZE.used_pages
     THEN SIZE.reserved_pages - SIZE.used_pages
    ELSE 0
   END * 8
FROM TB
 INNER JOIN SIZE
  ON TB.object_id = SIZE.object_id
ORDER BY schema_name, table_name

 

-------所有表、索引视图和Services Broker消息队列的空间信息

;WITH
OBJB AS(
 SELECT
  schema_id,
  object_id,
  object_name = name,
  object_type = type_desc
 FROM sys.objects
 WHERE type IN('U ','S ','V ','IT')
 UNION ALL
 -- 对于 Services Broker 消息队列，应使用 sys.internal_tables 中的 object_id 与 sys.dm_db_partition_stats 关联
 SELECT
  ITB.schema_id,
  ITB.object_id,
  object_name = O.name,
  type_desc = O.type_desc 
 FROM sys.objects O
  INNER JOIN sys.internal_tables ITB
   ON O.object_id = ITB.parent_id
 WHERE O.type = 'SQ'
  AND ITB.internal_type = 201
),
OBJ AS(
 SELECT
  OBJB.object_id,
  OBJB.object_type,
  schema_name = SCH.name,
  OBJB.object_name
 FROM OBJB
  INNER JOIN sys.schemas SCH
   ON OBJB.schema_id = SCH.schema_id
),
PS AS(
 -- 此部分计算表空间的信息
 SELECT
  object_id,
  reserved_pages = SUM(reserved_page_count),
  used_pages = SUM(used_page_count),
  pages = SUM(
   CASE
    WHEN index_id > 1 THEN lob_used_page_count + row_overflow_used_page_count
    ELSE in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count
   END),
  row_count = SUM (
   CASE
    WHEN index_id < 2 THEN row_count
    ELSE 0
   END)
 FROM sys.dm_db_partition_stats PS
 GROUP BY object_id
),
ITPS AS(
 -- 此部分计算包含 XML INDEX 和 FULLTEXT INDEXE 的空间信息(如果有的话)
 SELECT
  object_id = ITB.parent_id,
  reserved_pages = SUM(reserved_page_count),
  used_pages = SUM(used_page_count)
 FROM sys.dm_db_partition_stats P
  INNER JOIN sys.internal_tables ITB
   ON P.object_id = ITB.object_id
 WHERE ITB.internal_type IN(202, 204)
 GROUP BY ITB.parent_id
),
SIZE AS(
 -- 此部分合并所有的空间信息
 SELECT
  PS.object_id,
  reserved_pages = PS.reserved_pages + ISNULL(ITPS.reserved_pages, 0),
  used_pages = PS.used_pages + ISNULL(ITPS.used_pages, 0),
  PS.pages,
  PS.row_count
 FROM PS
  LEFT JOIN ITPS
   ON PS.object_id = ITPS.object_id
)
-- 显示最终的空间统计结果
-- 在前面的统计中，空间统计以页为单位, 8K/页,最终的统计将页数*8，得到KB为单位的空间大小
SELECT
 OBJ.object_type,
 OBJ.schema_name,
 OBJ.object_name,
 SIZE.row_count,
 reserved = SIZE.reserved_pages * 8,
 data = SIZE.pages * 8,
 index_size = CASE
     WHEN SIZE.used_pages > SIZE.pages
      THEN SIZE.used_pages - SIZE.pages
     ELSE 0
    END * 8,
 unused = CASE
    WHEN SIZE.reserved_pages > SIZE.used_pages
     THEN SIZE.reserved_pages - SIZE.used_pages
    ELSE 0
   END * 8
FROM OBJ
 INNER JOIN SIZE
  ON OBJ.object_id = SIZE.object_id
ORDER BY object_type, schema_name, object_name
---数据库空间信息

----单个数据库空间信息

;WITH
DB AS(
 -- 此部分查询数据文件和日志文件空间信息
 SELECT
  datafile_size = SUM(
   CASE
    WHEN type = 1 THEN 0
    ELSE size
   END),
  logfile_size = SUM(
   CASE
    WHEN type = 1 THEN size
    ELSE 0
   END),
  file_size = SUM(size)
 FROM sys.database_files
),
DATA AS(
 -- 此部分查询数据库中对象的空间使用信息
 SELECT
  reserved_pages = SUM(AU.total_pages),
  used_pages = SUM(AU.used_pages),
  pages = SUM(
   CASE
    -- xml index nodes 和 fulltext catalog map 只应包含在索引部分
    WHEN ITB.internal_type IN (202,204) THEN 0
    WHEN AU.type <> 1 THEN AU.used_pages
    WHEN P.index_id < 2 THEN AU.data_pages
    Else 0
   END)
 FROM sys.partitionsP
  INNER JOIN sys.allocation_units AU
   ON P.partition_id = AU.container_id
  LEFT JOIN sys.internal_tables ITB
   ON P.object_id = ITB.object_id
),
SIZE AS(
 SELECT
  database_space = CONVERT(decimal(15, 2),
    DB.file_size * 8 / 1024.),
  unallocated_space = CONVERT(decimal(15, 2),
   CASE
    WHEN DB.datafile_size >= DATA.reserved_pages
     THEN (DB.datafile_size - DATA.reserved_pages) * 8 / 1024.
    ELSE 0
   END),
  reserved_space = CONVERT(decimal(15, 2),
    DATA.reserved_pages * 8 / 1024),
  datafile_size = CONVERT(decimal(15, 2),
    DB.datafile_size * 8 / 1024.),
  logfile_size = CONVERT(decimal(15, 2),
    DB.logfile_size * 8 / 1024.),
  data_size = CONVERT(decimal(15, 2),
    DATA.pages * 8 / 1024),
  index_size = CONVERT(decimal(15, 2),
    (DATA.used_pages - DATA.pages) * 8 / 1024),
  unused_size = CONVERT(decimal(15, 2),
    (DATA.reserved_pages - DATA.used_pages) * 8 / 1024)
 FROM DB, DATA
)
SELECT
 database_name = DB_NAME(),
 *
FROM SIZE

-----当前实例所有数据库空间信息

IF OBJECT_ID(N'tempdb..#database_size') IS NOT NULL
 DROP TABLE #database_size
CREATE TABLE #database_size(
 database_name sysname,
 database_space decimal(15, 2),
 unallocated_space decimal(15, 2),
 reserved_space decimal(15, 2),
 datafile_size decimal(15, 2),
 logfile_size decimal(15, 2),
 data_size decimal(15, 2),
 index_size decimal(15, 2),
 unused_size decimal(15, 2)
)

DECLARE
 @sql nvarchar(4000)
SELECT
 @sql = N'
USE [?]
RAISERROR(N''calculate size on database "?"'', 10, 1) WITH NOWAIT
;WITH
DB AS(
 -- 此部分查询数据文件和日志文件空间信息
 SELECT
  datafile_size = SUM(
   CASE
    WHEN type = 1 THEN 0
    ELSE size
   END),
  logfile_size = SUM(
   CASE
    WHEN type = 1 THEN size
    ELSE 0
   END),
  file_size = SUM(size)
 FROM sys.database_files
),
DATA AS(
 -- 此部分查询数据库中对象的空间使用信息
 SELECT
  reserved_pages = SUM(AU.total_pages),
  used_pages = SUM(AU.used_pages),
  pages = SUM(
   CASE
    -- xml index nodes 和 fulltext catalog map 只应包含在索引部分
    WHEN ITB.internal_type IN (202,204) THEN 0
    WHEN AU.type <> 1 THEN AU.used_pages
    WHEN P.index_id < 2 THEN AU.data_pages
    Else 0
   END)
 FROM sys.partitions P
  INNER JOIN sys.allocation_units AU
   ON P.partition_id = AU.container_id
  LEFT JOIN sys.internal_tables ITB
   ON P.object_id = ITB.object_id
),
SIZE AS(
 SELECT
  database_space = CONVERT(decimal(15, 2),
    DB.file_size * 8 / 1024.),
  unallocated_space = CONVERT(decimal(15, 2),
   CASE
    WHEN DB.datafile_size >= DATA.reserved_pages
     THEN (DB.datafile_size - DATA.reserved_pages) * 8 / 1024.
    ELSE 0
   END),
  reserved_space = CONVERT(decimal(15, 2),
    DATA.reserved_pages * 8 / 1024),
  datafile_size = CONVERT(decimal(15, 2),
    DB.datafile_size * 8 / 1024.),
  logfile_size = CONVERT(decimal(15, 2),
    DB.logfile_size * 8 / 1024.),
  data_size = CONVERT(decimal(15, 2),
    DATA.pages * 8 / 1024),
  index_size = CONVERT(decimal(15, 2),
    (DATA.used_pages - DATA.pages) * 8 / 1024),
  unused_size = CONVERT(decimal(15, 2),
    (DATA.reserved_pages - DATA.used_pages) * 8 / 1024)
 FROM DB, DATA
)
INSERT #database_size
SELECT
 database_name = DB_NAME(),
 *
FROM SIZE
'
EXEC sp_MSforeachdb
 @command1 = @sql

SELECT * FROM #database_size
ORDER BY database_name

 

-----磁盘空间信息

-- 默认查询硬盘驱动器
EXEC master.dbo.xp_fixeddrives

-- 查询移动设备(例如U盘)
EXEC master.dbo.xp_fixeddrives 2

-- 查询大量存储设备(CD-ROM，DVD等)
EXEC master.dbo.xp_fixeddrives 3

-- 查询硬盘驱动器
EXEC master.dbo.xp_fixeddrives 4

-- 打开 xp_cmdshell 选项
EXEC sp_configure 'show advanced options', 1
RECONFIGURE

EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE
GO

-- 使用 msinfo32 查询磁盘空间信息
DECLARE
 @temp_file nvarchar(250),
 @s nvarchar(4000)

-- 通过 msinfo32 将磁盘信息存储到文件中
SELECT
 @temp_file = N'c:\'
  + CONVERT(nvarchar(36), NEWID())
  + N'.nfo',
 @s = N'start /wait msinfo32.exe /nfo '
  + QUOTENAME(@temp_file, N'"')
  + N' /categories +ComponentsStorage-ComponentsStorageDisks-ComponentsStorageSCSI-ComponentsStorageIDE'
EXEC master.dbo.xp_cmdshell @s, NO_OUTPUT

-- 从文件中获取磁盘信息
DECLARE
 @xml xml

SELECT
 @s = N'
SELECT
 @xml = CONVERT(xml, T.c)
FROM OPENROWSET(BULK N' + QUOTENAME(@temp_file, N'''')
 + N', SINGLE_BLOB) T(c)'
EXEC sp_executesql @s, N'@xml xml OUTPUT', @xml OUTPUT

-- 删除磁盘信息文件
SELECT
 @s = N'DEL '
  + QUOTENAME(@temp_file, N'"')
EXEC master.dbo.xp_cmdshell @s, NO_OUTPUT

-- 显示磁盘信息
SELECT
 Item = T.c.value(N'(项目)[1]', 'nvarchar(500)'),
 Value = T.c.value(N'(数值)[1]', 'nvarchar(500)')
FROM @xml.nodes(N'/MsInfo
 /Category[@name="系统摘要"]
 /Category[@name="组件"]
 /Category[@name="存储"]
 /Category[@name="驱动器"]
 /Data') T(c)