统计SQLServer数据库基础信息

仅统计登录库的对象信息。

查询SQLServer数据库实例、库、字段的排序规则（字符集编码）。
a. 查询数据库实例的排序规则。

SQL> select name,collation_name,COLLATIONPROPERTY(collation_name, 'CodePage') codpage from sys.databases;

b. 查询库的排序规则。

SQL> select SERVERPROPERTY(N'collation') collation_name,COLLATIONPROPERTY(CONVERT(nvarchar,SERVERPROPERTY(N'collation')), 'CodePage') codpage;

c. 查询字段的排序规则。

SQL> select s.name schema_name,t.name table_name,c.name column_name, TYPE_NAME(c.system_type_id) 				AS column_type,c.collation_name from sys.all_columns c 
      inner join sys.tables t 
         on c.object_id=t.object_id 
      inner join sys.schemas s 
         on t.schema_id=s.schema_id
      where t.is_ms_shipped = 0
         and t.name not in (select tt.name from sys.extended_properties ep inner join sys.tables tt on ep.major_id=tt.object_id)
      order by schema_name,table_name,column_name;

d. 常用代码页(Codepage)对照转换。

--936 简体中文 GBK 
--950 繁体中文 BIG5 
--437 美国/加拿大英语 
--932 日文 
--949 韩文 
--866 俄文 
--65001 unicode UFT-8

统计SQLServer数据库中的对象及个数，其中包括系统内置对象，与用户创建对象无法区分。

SQL> select 'database' type,name db,count(*) cnt from sys.databases group by name
      union all
      select 'schema' type,db_name() db,count(*) cnt from sys.schemas
      union all
      select 'table' type,db_name() db,count(*) cnt from sys.tables
      union all
      select 'view' type,db_name() db,count(*) cnt from sys.views
      union all
      select 'sequence' type,db_name() db,count(*) cnt from sys.sequences
      union all
      select 'procedure' type,db_name() db,count(*) cnt from sys.procedures
      union all
      select 'function' type,db_name() db,count(*) cnt from  sys.objects where type_desc like '%FUNCTION%'
      union all
      select 'trigger' type,db_name() db,count(*) cnt from  sys.triggers 
      union all
      select 'index' type,db_name() db,count(*) cnt from  sys.indexes 
      union all
      select 'partition_table' type,db_name() db,count(*) cnt from (
      select object_id,count(*) parts from sys.partitions group by object_id,index_id having object_id in (select object_id from sys.objects where type='U') and index_id=0 and count(*)>1) partition
      union all
      select 'synonym' type,db_name() db,count(*) cnt from sys.synonyms;

统计表数据量。

SQL> SELECT concat(c.name,'.',a.name) TableName,b.rows TableCount
      FROM sysobjects a  
      INNER JOIN	sysindexes b ON a.id=b.id 
      inner join sys.schemas c on a.uid=c.schema_id
      WHERE b.indid IN(0,1) AND a.Type='U'  and a.name not in ('') 
      ORDER BY TableCount DESC

查看所有用户下的表使用的空间大小。

SQL> SELECT db_name() as DbName,
          t.NAME AS TableName,
          s.Name AS SchemaName,
          p.rows AS RowCounts,
          SUM(a.total_pages) * 8 AS TotalSpaceKB, 
          CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS 总共占用空间MB,
          SUM(a.used_pages) * 8 AS UsedSpaceKB, 
          CAST(ROUND(((SUM(a.used_pages) * 8) / 1024.00), 2) AS NUMERIC(36, 2)) AS UsedSpaceMB, 
          (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB,
          CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2) AS NUMERIC(36, 2)) AS UnusedSpaceMB
      FROM 
          sys.tables t
      INNER JOIN      
          sys.indexes i ON t.OBJECT_ID = i.object_id
      INNER JOIN 
          sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
      INNER JOIN 
          sys.allocation_units a ON p.partition_id = a.container_id
      LEFT OUTER JOIN 
          sys.schemas s ON t.schema_id = s.schema_id
      WHERE 
          t.NAME NOT LIKE 'dt%' 
          AND t.is_ms_shipped = 0
          AND i.OBJECT_ID > 0
      GROUP BY 
          t.Name, s.Name, p.Rows
      ORDER BY 
          总共占用空间MB desc

查看数据库表对象使用的列名称（关键字排查）。

SQL> select name from sys.all_columns group by name order by name;

查看数据库表对象使用的数据类型。

SQL> select t.name from sys.columns c inner join sys.types t on c.system_type_id=t.system_type_id group by t.name;

列出所有的非系统数据所有表的主键信息和外键信息。
a. 查询主键。

SQL> select 
      o.id 主键ID,
      concat(s.name,'.',o.name)  主键名,
      oo.id 表ID,
      concat(ss.name,'.',oo.name) 表名称,
      cl.object_id 列ID,
      cl.name  列名称
      from sysobjects o 
      inner join sys.schemas s on o.uid=s.schema_id 
      inner join sysobjects oo on o.parent_obj=oo.id
      inner join sys.schemas ss on oo.uid=ss.schema_id 
      inner join sys.index_columns i on o.parent_obj=i.object_id
      inner join sys.columns cl on cl.object_id=i.object_id and cl.column_id=i.column_id
      where o.xtype='PK';

b. 查询外键与关联的主键信息。

SQL> SELECT 
      外键ID	   = b.constid,
      外键名称   = concat(s.name,'.',object_name(b.constid)),
      外键表ID   = b.fkeyid ,
      外键表名称 = concat(s.name,'.',object_name(b.fkeyid)),
      外键列ID   = b.fkey ,
      外键列名   = (SELECT name FROM syscolumns WHERE colid = b.fkey AND id = b.fkeyid) ,
      主键表ID   = b.rkeyid ,
      主键表名   =concat(s.name,'.',object_name(b.rkeyid)),
      主键列ID   = b.rkey ,
      主键列名   = (SELECT name FROM syscolumns WHERE colid = b.rkey AND id = b.rkeyid) ,
      级联更新   = ObjectProperty(a.id,'CnstIsUpdateCascade') ,
      级联删除   = ObjectProperty(a.id,'CnstIsDeleteCascade') 
      FROM sysobjects a 
      JOIN sysforeignkeys b ON a.id = b.constid 
      JOIN sysobjects c ON a.parent_obj = c.id 
      JOIN sys.schemas s on a.uid=s.schema_id
      WHERE a.xtype IN ('F') AND c.xtype = 'U';

统计SQLServer数据库基础信息 ​

统计SQLServer数据库基础信息