sqlserver查询用户表，sqlplus查询用户表

SQLServer如何用T-SQL命令查询一个数据库中有哪些表

1、查询SQL中的所有表： Select TABLE_NAME FROM 数据库名称.INFORMATION_SCHEMA.TABLES Where TABLE_TYPE='BASE TABLE' 执行之后，就可以看到数据库中所有属于自己建的表的名称 2、查询SQL中所有表及列： Select dbo.sysobjects.name as Table_name, dbo.syscolumns.name AS Column_name FROM dbo.syscolumns INNER JOIN dbo.sysobjects ON dbo.syscolumns.id = dbo.sysobjects.id Where (dbo.sysobjects.xtype = 'u') AND (NOT (dbo.sysobjects.name LIKE 'dtproperties')) 3、在Sql查询分析器，还有一个简单的查询方法： EXEC sp_MSforeachtable @command1="sp_spaceused '?'" 执行完之后，就可以看到数据库中所有用户表的信息 4、查询总存储过程数:select count(*) 总存储过程数 from sysobjects where xtype='p' 附：xtype类型D = 默认值或 DEFAULT 约束

F = FOREIGN KEY 约束L = 日志FN = 标量函数

IF = 内嵌表函数

P = 存储过程

PK = PRIMARY KEY 约束（类型是 K）

RF = 复制筛选存储过程S = 系统表TF = 表函数

TR = 触发器U = 用户表UQ = UNIQUE 约束（类型是 K）V = 视图X = 扩展存储过程 另：在sqlserver中取得某个数据库中所有表名的sql语句 select sysobjects.name from sysobjects.xtype ='U';SELECT name

WHERE (xtype = 'U') 在数据库的sysobjects表里有这个数据库全部表的信息, xtype值为'U'的就是表名 注意：一般通过上述方法获得全部用户表示都会有一个dtproperties表，SQLSERVER 默认它也是用户表，想要从用户表中排出，需要加上限定条件 status0，即：select * from sysobjects where xtype='U' and status0

相关问答

Q1: sql server 怎么查看用户

SQL Server中查询用户的对象权限和角色的方法

-- 查询用户的object权限

exec sp_helprotect NULL, 'UserName'

-- 查询用户拥有的role

exec sp_helpuser 'UserName'

-- 查询哪些用户拥有指定的系统role

exec sp_helpsrvrolemember 'sysadmin'

-- 可查询嵌套role

WITH tree_roles as

(

SELECT role_principal_id, member_principal_id

FROM sys.database_role_members

WHERE member_principal_id = USER_ID('UserName')

UNION ALL

SELECT c.role_principal_id,c.member_principal_id

FROM sys.database_role_members as c

inner join tree_roles

on tree_roles.member_principal_id = c.role_principal_id

)

SELECT distinct USER_NAME(role_principal_id) RoleName

FROM tree_roles

-- 其他权限相关基本表

select * from sysusers

select * from syspermissions

-- Who has access to my SQL Server instance?

SELECT

name as UserName, type_desc as UserType, is_disabled as IsDisabled

FROM sys.server_principals

where type_desc in('WINDOWS_LOGIN', 'SQL_LOGIN')

order by UserType, name, IsDisabled

-- Who has access to my Databases?

SELECT

dp.name as UserName, dp.type_desc as UserType, sp.name as LoginName, sp.type_desc as LoginType

FROM sys.database_principals dp

JOIN sys.server_principals sp ON dp.principal_id = sp.principal_id

order by UserType

select * from sys.database_principals

-- Server Roles

select

p.name as UserName, p.type_desc as UserType, pp.name as ServerRoleName, pp.type_desc as ServerRoleType

from sys.server_role_members roles

join sys.server_principals p on roles.member_principal_id = p.principal_id

join sys.server_principals pp on roles.role_principal_id = pp.principal_id

where pp.name in('sysadmin')

order by ServerRoleName, UserName

-- Database Roles

SELECT

p.name as UserName, p.type_desc as UserType, pp.name as DBRoleName, pp.type_desc as DBRoleType, pp.is_fixed_role as IfFixedRole

FROM sys.database_role_members roles

JOIN sys.database_principals p ON roles.member_principal_id = p.principal_id

JOIN sys.database_principals pp ON roles.role_principal_id = pp.principal_id

where pp.name in('db_owner', 'db_datawriter')

-- What can these users do?

SELECT

grantor.name as GrantorName, dp.state_desc as StateDesc, dp.class_desc as ClassDesc, dp.permission_name as PermissionName ,

OBJECT_NAME(major_id) as ObjectName, GranteeName = grantee.name

FROM sys.database_permissions dp

JOIN sys.database_principals grantee on dp.grantee_principal_id = grantee.principal_id

JOIN sys.database_principals grantor on dp.grantor_principal_id = grantor.principal_id

where permission_name like '%UPDATE%'

Q2: SQLServer如何用T—SQL命令查询一个数据库中有哪些表

所有用户表都存放在数据库中的系统对象表sysobjects中。

笔者以个人专用数据库为例：

select *

from sysobjects      --系统对象表

where xtype = 'U'    --U表示所有用户表

执行后影响的行数为180(rows)，如下图：

补充说明：另两个与之相关的系统对象表为（附下图）

1）syscolumns ：数据表列名清单表；

2）systypes：各列数据类型表。

sqlserver查询用户表的介绍就聊到这里吧，感谢你花时间阅读本站内容，更多关于sqlplus查询用户表、sqlserver查询用户表的信息别忘了在本站进行查找喔。