sqlserver查询数据的所有表名和行数

本文由 猛 于 2017-6-15 6:58 发布在  SQL    

 //查询所有数据库的大小

 SELECT DB_NAME(database_id) AS [Database Name],[Name] AS [Logical Name],[Physical_Name] AS [Physical Name],((size * 8) / 1024) AS [Size(MB)],[differential_base_time] AS [Differential Base Time]
FROM sys.master_files
ORDER BY  4 DESC

//查询所有表明
select name from sysobjects where xtype='u'
select * from sys.tables

//查询数据库中所有的表名及行数
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 a.name,b.rows DESC
 

//查询所有的标明及空间占用量\行数

select
object_name(id) tablename,8*reserved/1024 reserved,
rtrim(8*dpages)+'kb' used,8*(reserved-dpages)/1024 unused,
8*dpages/1024-rows/1024*minlen/1024 free,rows
from sysindexes
where indid=1
order by tablename,reserved desc

-----查询一个表的所有列名，字段的注释
SELECT 
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then isnull(f.value,'') else '' end,
字段序号=a.colorder,
字段名=a.name,
标识=case when COLUMNPROPERTY(a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
  SELECT name FROM sysindexes WHERE indid in(
   SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
   ))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
字段说明=isnull(g.[value],'')
FROM syscolumns a
left join systypes b on a.xtype=b.xusertype
inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e on a.cdefault=e.id
left join sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id
left join sys.extended_properties f on d.id=f.major_id and f.minor_id =0
 where d.name='dim_dept' --如果只查询指定表,加上此条件  
order by a.id,a.colorder