在数据库管理中，了解表的详细信息是非常重要的。通常，可能会使用ADO.NET类库来获取SQL Server表的信息。然而，SQL Server提供了一些标准的技术，这些技术在运行时可以更有效地获取这些信息。本文将介绍一种不使用游标和动态SQL的方法来获取表信息，并且可以将其封装为存储过程以便重复使用。
几天前，一个朋友问为什么总是使用ADO.NET类库来获取SQL表信息。决定尝试使用SQL Server提供的标准技术，并发现这种方法非常酷。通过研究sp_spaceused存储过程和SMO（SQL ServerManagement Objects）如何获取相同信息，决定不使用游标和动态SQL来实现这一功能。
使用了一个名为spTableInformation的存储过程，它显示了当前数据库中表、索引视图或服务代理队列的行数、保留的磁盘空间和使用的磁盘空间，或者显示整个数据库保留和使用的磁盘空间。换句话说，这个存储过程计算了数据和索引使用的磁盘空间，以及当前数据库中表使用的磁盘空间。
CREATE PROCEDURE [dbo].[spTableInformation] AS
BEGIN
SET NOCOUNT ON;
SELECT
SCHEMA_NAME(SYSTBL.SCHEMA_ID) AS [SCHEMA],
SYSTBL.NAME,
COALESCE(
(SELECT NAME FROM sys.database_principals AS SDBP WHERE (PRINCIPAL_ID = SYSTBL.PRINCIPAL_ID)),
SCHEMA_NAME(SYSTBL.SCHEMA_ID)
) AS OWNER,
SYSTBL.MAX_COLUMN_ID_USED AS COLUMNS,
CAST(
CASE SINDX_1.INDEX_ID
WHEN 1 THEN 1
ELSE 0
END AS BIT
) AS HASCLUSIDX,
COALESCE(
(SELECT SUM(rows) FROM sys.partitions AS SPART WHERE (OBJECT_ID = SYSTBL.OBJECT_ID) AND (INDEX_ID < 2)),
0
) AS [ROWCOUNT],
COALESCE(
(SELECT CAST(SPTV.low / 1024.0 AS FLOAT) * SUM(SAU_1.USED_PAGES - CASE WHEN SAU_1.TYPE <> 1 THEN SAU_1.USED_PAGES WHEN SYSP.INDEX_ID < 2 THEN SAU_1.DATA_PAGES ELSE 0 END) FROM sys.indexes AS SINDX_2 INNER JOIN sys.partitions AS SYSP ON SYSP.OBJECT_ID = SINDX_2.OBJECT_ID AND SYSP.INDEX_ID = SINDX_2.INDEX_ID INNER JOIN sys.allocation_units AS SAU_1 ON SAU_1.CONTAINER_ID = SYSP.PARTITION_ID WHERE (SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID)),
0.0
) AS INDEXKB,
COALESCE(
(SELECT CAST(SPTV.low / 1024.0 AS FLOAT) * SUM(CASE WHEN SAU_2.TYPE <> 1 THEN SAU_2.USED_PAGES WHEN SYSP.INDEX_ID < 2 THEN SAU_2.DATA_PAGES ELSE 0 END) FROM sys.indexes AS SINDX_2 INNER JOIN sys.partitions AS SYSP ON SYSP.OBJECT_ID = SINDX_2.OBJECT_ID AND SYSP.INDEX_ID = SINDX_2.INDEX_ID INNER JOIN sys.allocation_units AS SAU_2 ON SAU_2.CONTAINER_ID = SYSP.PARTITION_ID WHERE (SINDX_2.OBJECT_ID = SYSTBL.OBJECT_ID)),
0.0
) AS DATAKB,
SYSTBL.CREATE_DATE,
SYSTBL.MODIFY_DATE
FROM
sys.tables AS SYSTBL
INNER JOIN
sys.indexes AS SINDX_1 ON SINDX_1.OBJECT_ID = SYSTBL.OBJECT_ID AND SINDX_1.INDEX_ID < 2
INNER JOIN
master.dbo.spt_values AS SPTV ON SPTV.NUMBER = 1 AND SPTV.type = 'E'
END
GO