use master;

exec sp_helpdb  

use msdb

select name,

       user_access_desc,           --用户访问模式

       state_desc,                 --数据库状态

       recovery_model_desc,        --恢复模式

       page_verify_option_desc,    --页检测选项

       log_reuse_wait_desc         --日志重用等待

from sys.databases  

name    user_access_desc    state_desc    recovery_model_desc    page_verify_option_desc    log_reuse_wait_desc

master    MULTI_USER    ONLINE    SIMPLE    CHECKSUM    NOTHING

tempdb    MULTI_USER    ONLINE    SIMPLE    CHECKSUM    NOTHING

model    MULTI_USER    ONLINE    FULL    CHECKSUM    NOTHING

msdb    MULTI_USER    ONLINE    SIMPLE    CHECKSUM    NOTHING

ReportServer    MULTI_USER    ONLINE    FULL    CHECKSUM    NOTHING

ReportServerTempDB    MULTI_USER    ONLINE    SIMPLE    CHECKSUM    NOTHING

SHYANGLAO    MULTI_USER    ONLINE    FULL    CHECKSUM    NOTHING

use SHYANGLAO

go  

--按页面计算空间，有性能影响，基本准确

exec sp_spaceused

go  

----可以@updateusage = 'true',会运行dbcc updateusage

exec sp_spaceused  @updateusage = 'true'

go 

----对某个数据库,显示目录视图中的页数和行数错误并更正

DBCC UPDATEUSAGE('SHYANGLAO')  

use master 

--查看某个数据库中的所有文件及大小

exec sp_helpfile

--查看所有文件所在数据库、路径、状态、大小

select db_name(database_id) dbname,

       type_desc,      --数据还是日志

       name,           --文件的逻辑名称

       physical_name,  --文件的物理路径

       state_desc,     --文件状态

       size * 8.0/1024 as '文件大小（MB）'

from sys.master_files  

--按区extent计算空间，没有性能影响，基本准确,把TotalExtents*64/1024,单位为MB

--同时也适用于计算tempdb的文件大小，但不包括日志文件

dbcc showfilestats  

use master 

--查看日志文件所在数据库、路径、状态、大小

select db_name(database_id) dbname,

       type_desc,      --数据还是日志

       name,           --文件的逻辑名称

       physical_name,  --文件的物理路径

       state_desc,     --文件状态

       size * 8.0/1024 as '文件大小（MB）'

from sys.master_files

where type_desc = 'LOG'  

--所有数据库的日志的大小,空间使用率

dbcc sqlperf(logspace)  

use master 

--数据和日志文件的I/O统计信息,包含文件大小

    select database_id,

           file_id,

           file_handle,           --windows文件句柄

           sample_ms,             --自从计算机启动以来的毫秒数

           num_of_reads,

           num_of_bytes_read,

           io_stall_read_ms,      --等待读取的时间

           num_of_writes,

           num_of_bytes_written,

           io_stall_write_ms,

           io_stall,              --用户等待文件完成I/O操作所用的总时间

           size_on_disk_bytes     --文件在磁盘上所占用的实际字节数          

    from sys.dm_io_virtual_file_stats(db_id('master'),1 )  ----数据库id 数据文件id

    union all  

    select database_id,

           file_id,

           file_handle,           --windows文件句柄

           sample_ms,             --自从计算机启动以来的毫秒数

           num_of_reads,

           num_of_bytes_read,

           io_stall_read_ms,      --等待读取的时间

           num_of_writes,

           num_of_bytes_written,

           io_stall_write_ms,  

           io_stall,              --用户等待文件完成I/O操作所用的总时间

           size_on_disk_bytes     --文件在磁盘上所占用的实际字节数

    from sys.dm_io_virtual_file_stats( db_id('master'),2 )  -- --数据库id日志文件id  

use SHYANGLAO 

--不一定准确:某个表的行数,保留大小，数据大小，索引大小，未使用大小

exec sp_spaceused @objname ='yl_person_num'  

--准确:但有性能影响

exec sp_spaceused @objname ='yl_person_num',   @updateusage ='true'  

--按页统计，没有性能影响，有时不准确

/*======================================================

一次计算多个对象的空间使用情况

sys.dm_db_partition_stats返回当前数据库中每个分区(表和索引)的页和行计数信息

========================================================*/

select o.name,

       sum(p.reserved_page_count) as reserved_page_count, --保留页，包含表和索引

       sum(p.used_page_count) as used_page_count,         --已使用页，包含表和索引

       sum(case when p.index_id <2

                     then p.in_row_data_page_count + p.lob_used_page_count + p.row_overflow_used_page_count

                else p.lob_used_page_count +    p.row_overflow_used_page_count

           end) as data_pages,  --数据页,包含表中数据、索引中的lob数据、索引中的行溢出数据

       sum(case when p.index_id < 2

                     then p.row_count

                else 0

           end) as row_counts   --数据行数，包含表中的数据行数，不包含索引中的数据条目数  

from sys.dm_db_partition_stats p

inner join sys.objects o

        on p.object_id = o.object_id

where p.object_id= object_id('yl_person_num')

group by o.name     

use SHYANGLAO 

--按页或区统计，有性能影响，准确

--显示当前数据库中所有的表或视图的数据和索引的空间信息

--包含：逻辑碎片、区碎片(碎片率)、平均页密度

dbcc showcontig(yl_person_num)  

--SQL Server推荐使用的动态性能函数，准确

select *

from sys.dm_db_index_physical_stats(

        db_id('SHYANGLAO'),                      --数据库id

        object_id('SHYANGLAO.yl_person_num'),    --对象id

        null,                               --索引id

        null,                               --分区号

        'limited'

    )

         --default,null,'limited','sampled','detailed',默认为'limited'

         --'limited'模式运行最快，扫描的页数最少,对于堆会扫描所有页,对于索引只扫描叶级以上的父级页

         --'sampled'模式会返回堆、索引中所有页的1%样本的统计信息，如果少于1000页，那么用'detailed'代替'sampled'

         --'detailed'模式会扫描所有页,返回所有统计信息  

DBCC SHOWCONTIG 正在扫描 'yl_person_num' 表...

表: 'yl_person_num' (533576939)；索引 ID: 1，数据库 ID: 7

已执行 TABLE 级别的扫描。

- 扫描页数................................: 1

- 扫描区数..............................: 1

- 区切换次数..............................: 0

- 每个区的平均页数........................: 1.0

- 扫描密度 [最佳计数:实际计数].......: 100.00% [1:1]

- 逻辑扫描碎片 ..................: 0.00%

- 区扫描碎片 ..................: 0.00%

- 每页的平均可用字节数.....................: 5828.0

- 平均页密度(满).....................: 28.00%

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。

(7 行受影响)

select t.object_id,t.name, p.value from sys.tables t

join sys.extended_properties p

on t.object_id = p.major_id

and p.name = 'MS_Description'

and p.minor_id=0

;

select t1.*,t2.name as column_name

from

(select t.object_id,p.major_id,p.minor_id,t.name as tables_name, p.value,t.type,t.type_desc from sys.tables t

join sys.extended_properties p

on t.object_id = p.major_id

and p.name = 'MS_Description') t1

left join syscolumns t2

on t1.object_id=t2.id

and t1.minor_id=t2.colid

;