SQL server 统计数据库

 

select * from sysdatabases order by name

 

 

1. 查询数据库中所有表
   

   select * from sysobjects where xtype='U';
   xtype='U' 表示所有用户表
   select * from sysobjects where xtype='S';
   xtype='S' 表示所有系统表

    

2. 查询指定表所有字段名
   

   select name from syscolumns where id=Object_Id('Bod_Tag)';

   

    

    

3. 用户所属数据库角色
   

       use yourdb  
       go  
       select DbRole = g.name, MemberName = u.name, MemberSID = u.sid  
       from sys.database_principals u, sys.database_principals g, sys.database_role_members m  
       where g.principal_id = m.role_principal_id  
       and u.principal_id = m.member_principal_id  
       order by 1, 2  
       go  

    

4. 登陆帐户所属服务器角色
   

   use master  
   go  
   select SrvRole = g.name, MemberName = u.name, MemberSID = u.sid  
   from sys.server_principals u, sys.server_principals g, sys.server_role_members m  
   where g.principal_id = m.role_principal_id  
   and u.principal_id = m.member_principal_id  
   order by 1, 2  
   go 

    

5. 查询所有用户表
   

   select name from Bod..sysobjects where xtype='u' order by name