系统视图，系统表，系统存储过程的使用

获取数据库中用户表信息

1、获取特定库中所有用户表信息

select * from sys.tables 

select * from sys.objects  where type='U'  --用户表

第二条语句中当type='S'时是系统表

2、获取表的字段信息

select * from sys.columns where object_id=object_id('表名') select * from syscolumns where id=OBJECT_ID('表名' )

3、获取当前库中表的字段及类型信息（1）

select '字段名'=a.name, 
             '类型名'=b.name, 
             '字段长度'=a.max_length, 
             '参数顺序'=a.column_id   
from sys.columns a left join sys.types b  
on a.user_type_id=b.user_type_id where object_id=object_id('表名')

syscolumns与sys.columns表用法类似。

获取索引或主键信息
1、获取对象及对应的索引的信息

select '对象名'=A.name, 
       
'对象类型'=a.type,    
    
'索引名'=B.name, 

'索引类型'=case b.type 

when 1 then '聚集索引' 

when 2 then '非聚集索引'                   

when 3 then 'xml索引'                   

else '空间索引' end,  

'主键否'=case when b.is_primary_key=1 then '主键' else '' end 
 
FROM sys.objects A JOIN sys.indexes B ON A.object_id=B.object_id  WHERE A.type='U' AND B.name IS NOT NULL order by a.name

2、获取表的主键及对应的字段
（1）

select '表名'=d.name ,'主键名'=a.name,'字段名'=c.name   
from sys.indexes a join sys.index_columns b  
on a.object_id=b.object_id and a.index_id=b.index_id  join sys.columns c on a.object_id=c.object_id and  c.column_id=b.column_id  
join sys.objects d on d.object_id=c.object_id  where a.is_primary_key=1

（2）

SELECT '表名'=OBJECT_NAME(b.parent_obj), 
       '主键名'=c.name,        '字段名'=a.name
FROM syscolumns a,sysobjects b,sysindexes c,sysindexkeys d  WHERE  b.xtype = 'PK' AND b.parent_obj = a.id AND c.id = a.id  AND b.name = c.name AND d.id = a.id  
AND d.indid = c.indid AND a.colid = d.colid

（3）

select '所属架构'=s.name , 
       '表名'=t.name,        '主键名'=k.name ,        '列名'=c.name, 
       '键列序数'=ic.key_ordinal from sys.key_constraints as k   join sys.tables as t     
on t.object_id = k.parent_object_id   join sys.schemas as s     on s.schema_id = t.schema_id   join sys.index_columns as ic     on ic.object_id = t.object_id    and ic.index_id = k.unique_index_id   join sys.columns as c     on c.object_id = t.object_id    
and c.column_id = ic.column_id where k.type = 'pk';

（4）使用系统存储过程获取指定表的主键信息

EXEC sp_pkeys '表名' --表名只能是当前数据库下的单独表名不能带上架构名

3、查询哪些表创建了主键

select '表名'=a.name from  
(select name,object_id from sys.objects where type='u') a  left join  sys.indexes  b  
on a.object_id=b.object_id and b.is_primary_key=1 where b.name is not null

注：查询哪些表没有创建主键，将where条件改成 is null 即可。

查找视图信息
1、查看视图属性信息

exec sp_help '视图名'

2、查看创建视图脚本

exec sp_helptext '视图名'

3、查看当前数据库所有视图基本信息

select * from sys.views
select * from sys.objects where type='V' select * from INFORMATION_SCHEMA.VIEWS

4、查看视图对应的字段及字段属性

select '视图名'=a.name,        '列名'=b.name,
'字段类型'=TYPE_NAME(b.system_type_id),        '字段长度'=b.max_length
from sys.views a join sys.columns b  on a.object_id=b.object_id order by a.name

5、获取视图中的对象信息
exec sp_depends '视图名'

查看存储过程信息

1、基本信息

select * from sys.procedures
select * from sys.objects where type='P'

2、查看存储过程创建文本

sp_helptext 存储过程名称
select text from syscomments where id=object_id (存储过程名称)

3、查看存储过程的参数信息

(1)select '参数名称' = name, '类型' = type_name(xusertype), '长度' = length, '参数顺序' = colid from syscolumns where id=object_id(存储过程名称)
(2)select '参数名称' = name, '类型' = type_name(system_type_id), '长度' = max_length, '参数顺序' =parameter_id from sys.parameters where object_id=object_id(存储过程名称)

返回当前环境中可查询的指定表或视图的列信息。
exec sp_columns 表名
select * from sys.columns where object_id=OBJECT_id(表名) select * from sys.syscolumns where id=OBJECT_ID(表名)
select * from information_schema.columns where TABLE_NAME=表名

查询存储过程或函数的参数的详细信息

select * from sys.parameters where object_id=object_id(函数或存储过程名称)

获取所有数据库信息

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 exec sp_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
--添加角色ins成员admins
exec sp_addrolemember 'ins','admins'
--删除角色ins成员admins
exec sp_droprolemember 'ins','admins'
--删除角色
drop role ins   --必须先删除角色中所有成员
--删除用户
drop user admins
--删除登录账户
drop login administor
--==================================================================

查看数据库关于权限的信息
--查询当前数据库角色信息 exec sp_helprole 角色名
--提供有关每个数据库中的登录及相关用户的信息

exec sp_helplogins 登录名
--报告有关当前数据库中数据库级主体的信息。

exec sp_helpuser 当前数据库用户或角色名
--返回有关当前数据库中某个角色的成员的信息

exec sp_helprolemember 角色名
--返回SQL Server 固定服务器角色的列表

exec sp_helpsrvrole 固定服务器角色名
sql数据库批量分配权限

declare @sql varchar(max)=''
select @sql=@sql+'grant insert on '+ name + ' to admins '+CHAR(10) from sysobjects where name like 'a_%' exec (@sql)

如何创建windows用户登录

备份和还原数据库

1、创建备份设备
sp_addumpdevice

[ @devtype = ] 'device_type' --备份设备类型 ,

[ @logicalname = ] 'logical_name' --备份设备逻辑名称 ,

[ @physicalname = ] 'physical_name' –物理名称

EXEC sp_addumpdevice 'disk', 'mydiskdump', 'd:\dump1.bak';
注：添加逻辑名为mydiskdump物理名为dump1.bak 的disk类型的备份设备

2、删除备份设备
sp_dropdevice [ @logicalname = ] 'device' --备份设备逻辑名称
[ , [ @delfile = ] 'delfile' ] --指定物理备份设备文件是否应删除
exec sp_dropdevice 'mydiskdump','delfile';
注：参数'delfile'不选时只将备份设备的逻辑名从数据库引擎中删除，并删除对应master..sysdevices表中的项。有参数时会同时删除对应的物理备份设备的文件。

3、查询数据库引擎中备份设备的信息
select * from master..sysdevices select * from sys.backup_devices

4、备份数据库
backup database mail to disk=备份文件

backup database 数据库名 to 备份设备

5、数据恢复

6、数据库快照恢复
----------------------------------创建数据库DemoDB------------------------

create database DemoDB on primary
(name='DemoDB_data',filename='d:\Demodb_log.mdf',size=5MB,maxsize=10MB)

log on (name='DemoDB_log',filename='d:\Demodb_log.ldf',size=2MB,maxsize=10MB)

go
-------------------------------------在DemoDB创建数据表T1和T2 ------------------------

use DemoDB
create table T1(id int,name char(8),address char(13))

go
create table T2(id int,name char(8),address char(13))

go
---------------------------------------在DemoDB数据库的T1和T2插入数据------------------------

use DemoDB
Insert into T1 values(1,'jacky','suzhou')

Insert into T1 values(2,'Hellen','shanghai')

Insert into T2 values(1,'Tom','beijing')

Insert into T2 values(2,'Alice','hangzhou')

--------------为DemoDB数据库创建数据库快照DemoDB_dbsnapshot_200510201600-------------------

create database DemoDB_dbsnapshot_200510201600

on
(name='DemoDB_data',filename='d:\DemoDB_dbsnapshot_201203091700.mdf')

as snapshot of DemoDB

go
----------------------------------------在数据库快照和数据库中查询T1和T2表------------

use DemoDB_dbsnapshot_200510201600

select * from dbo.T1

select * from dbo.T2

go
use DemoDB

--在数据库中查看表T1和T2

select * from dbo.T1

select * from dbo.T2

go
---------------------------------------------在数据库中修改T1和T2-------------------

use DemoDB

update T1 set name='Tony' where id=1 --在DemoDB中更新数据

go
delete from T1 where id=2 --在DemoDB中删除数据

go
drop Table T2 --删除T2表

go
------------------------------在数据库快照和数据库中查询T1和T2表-------------------

use DemoDB_dbsnapshot_200510201600

select * from T1 select * from T2 go

use DemoDB

select * from T1 select * from T2
go
------------------使用数据库快照还原在DemoDB数据库的T1表误删除和更新的数据-------------------

update DemoDB.dbo.T1
set name=(select name from DemoDB_dbsnapshot_200510201600.dbo.T1 where id=1) where id=1

go
insert into DemoDB.dbo.T1
select * from DemoDB_dbsnapshot_200510201600.dbo.T1 where id=2

go
----------------------------使用数据库快照还原在DemoDB数据库误删除的T2表------------------

use DemoDB
--复制进剪贴板中的创建T2的语句 go
select *into DemoDB.dbo.T2 from DemoDB_dbsnapshot_200510201600.dbo.T2

go
------------------------------------在数据库快照和数据库中查询T1和T2表------------------

use DemoDB

select * from T1 select * from T2 go
use DemoDB_dbsnapshot_200510201600

select * from T1 select * from T2 go
------------------------------------------ --注：如果需要周期创建快照，可以创建作业----------------------

------------------------------------------在DemoDB中更新数据----------------------

use DemoDB
update T1 set name='Funny' where id=1

go
-----------------------------------------数据库快照和数据库中查询T1和T2表----------------------

select * from Demodb.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T2

----------------------------------------在DemoDB中更新数据----------------------

use DemoDB
update T1 set name='Bob' where id=1

go
----------------------------------数据库快照和数据库中查询T1和T2表----------------------

select * from Demodb.dbo.T1
select * from DemoDB_dbsnapshot_200510201600.dbo.T1

select * from DemoDB_dbsnapshot_200510201600.dbo.T2
-----------------------------------------------

/*使用数据库快照还原整个数据库*/
-------------------------------------------使用数据库快照恢复DemoDB数据库----------------------

use master
restore Database DemoDB from
Database_snapshot='DemoDB_dbsnapshot_200510201600'

-------------------------------------------

select * from DemoDB.dbo.T1
select * from DemoDB_dbsnapshot_200510201600.dbo.T1

-------------------------------------------

use master
drop database DemoDB_dbsnapshot_200510201600

--删除数据库快照

drop Database DemoDB

--删除数据库

posted @ 2015-01-05 01:29 princessd8251 阅读(228) 评论(0) 收藏举报

刷新页面返回顶部