获取所有数据库信息

1、获取数据库的基本信息

select name from sysdatabases order by name

2、获取某个数据库的文件信息

select * from [数据库名].[架构名].sysfiles

3、获取数据库磁盘使用情况

exec sp_spaceused

4、获取数据库中表的空间使用情况

IF OBJECT_ID('tempdb..#TB_TEMP_SPACE') IS NOT NULL DROP TABLE #TB_TEMP_SPACE

GO

CREATE TABLE #TB_TEMP_SPACE(

NAME VARCHAR(500)

,ROWS INT

,RESERVED VARCHAR(50)

,DATA VARCHAR(50)

,INDEX_SIZE VARCHAR(50)

,UNUSED VARCHAR(50)

)

GO

SP_MSFOREACHTABLE 'INSERT INTO #TB_TEMP_SPACE execsp_spaceused '' '''

GO

SELECT *

FROM #TB_TEMP_SPACE

ORDER BY REPLACE(DATA,'KB','')+0 DESC

获取触发器的相关信息

1、查看触发器定义及相关属性信息

（1）exec sp_help '触发器名'

（2）查看表中指定类型的触发器的属性信息

exec sp_helptrigger ['表名'][,['触发器类型']]

--参数2可选，省略参数2时返回该表中所有类型的触发器属性

2、获取触发器的创建脚本

exec sp_helptext '触发器名'

3、查看表中禁用的触发器

select name from sys.triggers where parent_id=object_id('表名') and is_disabled=1

注：is_disabled=0时为启用的触发器。

4、获取触发器的父类名，触发器名，触发器状态和触发器类型信息

select '父类名'=a.name,

'对象类型'=a.type,

'触发器名'=b.name,

'触发器状态'=case when b.is_disabled=1 then'禁用' else '启用'end,

'触发器类型'=case when b.is_instead_of_trigger=1 then 'instead of' else 'after' end

from sys.objects a join sys.triggers b on a.object_id=b.parent_id

注：查询单个表或视图的触发器信息加上a.object_id=object_id(表名)条件。

5、禁用和启用触发器命令

禁用：alter table表名disable trigger触发器名

启用：alter table表名enable trigger触发器名

注：禁用或启用多个触发器，触发器名之间用逗号隔开

禁用或启用表中全部触发器，将触发器名换成ALL。

6、指定第一个或最后一个触发的after触发器。

exec sp_settriggerorder '触发器名', '执行顺序', '触发事件'

查询触发触发器的对应事件

select * from sys.trigger_events where object_id=object_id('触发器名')

7、重命名触发器

exec sp_rename 旧名，新名

SQL语句创建登录名,数据库用户,数据库角色及分配权限

使用到的存储过程解释说明：

sp_addlogin 新增登录账号存储过程

语法：sp_addlogin [@loginame = ] 'login' --登录名

[ , [ @passwd = ] 'password' ] -–登录密码

[ , [ @defdb = ] 'database' ] --默认数据库

[ , [ @deflanguage = ]'language' ] --默认语言

[ , [ @sid = ] sid ] --安全标识号

[ , [ @encryptopt= ]'encryption_option' ] –密码传输方式

sp_grantlogin 创建sql server 登录名

语法：sp_addlogin [ @loginame = ] 'login' --登录名

sp_droplogin 删除登录帐号存储过程

语法：sp_droplogin [@loginame = ] 'login' --登录名

sp_grantdbaccess 将数据库用户添加到当前数据库

语法：sp_grantdbaccess [@loginame = ] 'login' --登录名

[ , [ @name_in_db = ] 'name_in_db' [ OUTPUT ]] --数据库用户名

sp_addrole 创建数据库角色

语法：sp_addrole [ @rolename = ] 'role' –角色名

[ , [ @ownername = ] 'owner' ] --角色所有者

sp_addrolemember 为角色添加成员

语法：sp_addrolemember [ @rolename = ] 'role', --角色名

[ @membername = ] 'security_account' --成员用户

sp_droprolemember 删除角色成员

sp_helprole [ [ @rolename = ] 'role' ]

返回当前数据库中有关角色的信息

1、创建登录名

(1)exec sp_addlogin '登录名','密码','默认数据库'

(2)create login 登录名 with password='密码',default_database=默认数据库

2、为指定登录名为创建指定数据库上的用户

use 指定数据库

(1)execute sp_grantdbaccess '登录名','用户'

(2)create user 用户名 for login 登录名

3、授予用户拥有表的权限

grant 权限 on 对象 to 用户

4、添加数据库角色

execute sp_addrole '角色名'

create role 角色名 authorization 拥有新角色的数据库用户或角色

5、添加角色的成员

execute sp_addrolemember '角色名','用户名'

6、设置角色拥有对象的权限

grant 权限 on 对象名 to 角色名

--=================================================================

创建用户并分配权限

--新增登录名

create login administor with password='123',default_database=Mail

--新增用户

use Mail

create user admins for login administor

--为用户分配权限

grant select on A_Area to admins

--取消分配的权限

revoke select on A_Area to admins

--新增角色

create role ins

--为角色分配权限

grant select on A_MailZT to ins with grant option

--删除角色对表A_MailZT的查询权限

revoke select on a_mailzt to ins CASCADE