（4.1）对象信息--系统目录视图

 --表结构相关的系统目录视图
 select * from sys.schemas    --每一行对应一个数据库中的架构
 select * from sys.tables    --每一行对应数据库的一个表
 select * from sys.columns  --对象的列信息，包括表（用户表、系统表）、函数（表值程序集函数、内联表值函数、内部表、表值sql函数）及视图
 select * from sys.types    --列对应的数据类型

--查看该库所有表及列
use master
go

select t4.name 架构名,
t1.name 表名,
t2.name 列名,
t2.max_length 列定义长度,
t2.precision 精确度,
t2.scale 级别,
t2.is_nullable 是否允许为空,
t2.is_identity 是否为自增列,
t2.is_computed 是否计算,
t3.name type,
t3.max_length 类型最大长度
from sys.tables t1 join sys.columns t2 
on t1.object_id = t2.object_id
join sys.types t3 
on t3.system_type_id = t2.system_type_id AND t2.user_type_id = t3.user_type_id
join sys.schemas t4 on t4.schema_id = t1.schema_id



效果图如下：

有些列可以酌情去掉。

也可以用sp_help table_name来验证

 

select t2.type_desc,t2.name,t2.create_Date,t2.modify_date,t1.definition 
from sys.sql_modules t1 join sys.objects t2 on t1.object_id = t2.object_id

--查看所有表大小
create table #Data(name varchar(100),row varchar(100),reserved varchar(100),data varchar(100),index_size varchar(100),unused varchar(100)) 
 
declare @name varchar(100) 
declare cur cursor  for 
    select name from sysobjects where xtype='u' order by name 
open cur 
fetch next from cur into @name 
while @@fetch_status=0 
begin 
    insert into #data 
    exec sp_spaceused   @name 
    print @name 
 
    fetch next from cur into @name 
end 
close cur 
deallocate cur 
 
create table #DataNew(name varchar(100),row int,reserved int,data int,index_size int,unused int) 
 
insert into #dataNew 
select name,convert(int,row) as row,convert(int,replace(reserved,'KB','')) as reserved,convert(int,replace(data,'KB','')) as data, 
convert(int,replace(index_size,'KB','')) as index_size,convert(int,replace(unused,'KB','')) as unused from #data  
 
select * from #dataNew order by data desc 

--查看所有用户自定义表表行数
SELECT   a.name, b.rows
FROM      sysobjects AS a INNER JOIN
                 sysindexes AS b ON a.id = b.id
WHERE   (a.type = 'u') AND (b.indid IN (0, 1))
ORDER BY b.rows DESC