—
=============================================
—
Author:
—
Create date:
—
Description:
—
Thanks to ,, RBarry Young
—
=============================================
CREATE
PROCEDURE
[
dbo
]
.
[
spTableInformation
]
AS
BEGIN
—
SET NOCOUNT ON added to prevent extra result sets from
—
interfering with SELECT statements.
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
)
AS
Expr1
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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16436858/viewspace-609719/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/16436858/viewspace-609719/