好久没看SQL SERVER 2000,以为不会再用,然上次笔试,题多,竟不能答,遂有下文.
先复习一下系统环境变量:
1 @@CONNECTIONS返回自上次启动 Microsoft SQL Server以来连接或试图连接的次数。
返回类型 integer
示例:SELECT GETDATE() AS 'Todays Date and Time',
@@CONNECTIONS AS 'Login Attempts'
返回:
Today's Date and Time Login Attempts
--------------------------- ---------------
2008-02-19 15:53:20.340 22
2 @@CPU_BUSY返回自上次启动 Microsoft SQL Server以来 CPU 的工作时间，单位为毫秒（基于系统计时器的分辨率）。
返回类型integer
示例:
显示了到当前日期和时间为止 SQL Server CPU 的活动。
SELECT @@CPU_BUSY AS 'CPU ms', GETDATE() AS 'As of'
返回:
CPU ms As of
----------------- ---------------------------
153 2008-02-19 15:56:39.077
3 @@VERSION返回类型nvarchar
注释
@@VERSION 返回的信息与 xp_msver 存储过程返回的产品名、版本、平台和文件数据相似，但 xp_msver 存储过程提供更详细的信息。
示例
下面的示例返回当前安装的日期、版本和处理器类型。
SELECT @@VERSION
说明一下serverproperty:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition'),SERVERPROPERTY(N'Collation')
4 @@TEXTSIZE返回 SET 语句 TEXTSIZE 选项的当前值，它指定 SELECT 语句返回的 text 或 image 数据的最大长度，以字节为单位。返回类型integer
SELECT @@TEXTSIZE
SET TEXTSIZE 2048
SELECT @@TEXTSIZE
5 @@servicename 返回值NVARCHAR
select @@servicename
6 更新数据库类型和数据库内容
use pubs
alter table authors alter column state varchar(80)
update authors
set state='china' where au_id='172-32-1176'
select * from authors
use pubs
select au_id as 作者ID ,firstname=
case au_fname
when
'c' then 'cheng'
else 'not find'
end,
city as 城市
from authors
7 timestamp类型
SQL Server timestamp 数据类型与时间和日期无关。SQL Server timestamp 是二进制数字，它表明数据库中数据修改发生的相对顺序。实现 timestamp 数据类型最初是为了支持 SQL Server 恢复算法。每次修改页时，都会使用当前的 @@DBTS 值对其做一次标记，然后 @@DBTS 加1。这样做足以帮助恢复过程确定页修改的相对次序，但是 timestamp 值与时间没有任何关系。
二 系统函数＆视图
数据库文件大小相关：
exec sp_helpdb
exec sp_heldb 'master' --取出databaseid
sys.master_files -- 在视图里查详细信息
select database_id,file_id,type_desc,name,physical_name,state_desc,growth,size/128 as 'size(MB)',max_size/128 as 'max_siz(MB)' from sys.master_files where database_id='8'
sys.database_files --没有
sys.indexes --在存在二进制数据时统计信息不准备
sp_spaceused --表大小相关，这里贴几个常用的分析sql,第一个是sp_spaceused对表的取法
SELECT OBJECT_NAME(object_id),
SUM (reserved_page_count)*8/1024 as 'reserved(MB)',
SUM (used_page_count)*8/1024 as 'used(MB)',
SUM (
CASE
WHEN (index_id < 2) THEN (in_row_data_page_count + lob_used_page_count + row_overflow_used_page_count)
ELSE lob_used_page_count + row_overflow_used_page_count
END
)*8/1024 as 'pgaes(Mb)',
SUM (
CASE
WHEN (index_id < 2) THEN row_count
ELSE 0
END
) as 'ROWS'
FROM sys.dm_db_partition_stats where left(object_name(object_id),3)<>'sys'
group by object_Id order by rows 这个就是用sp_spaceused
declare @sql varchar(2000),@count int ,@maxbh int,@name varchar(200)
set @count=0
create table #tmp_cxf1
(name varchar(100),rows char(30),reserved char(40),data char(40),index_size varchar(40),unused char(30))
--
create table #tmp_cxf (i int identity(0,1),name varchar(300))
insert into #tmp_cxf
select name from sys.objects where type='u'
--
select @maxbh=max(i) from #tmp_cxf
while @count<@maxbh
begin
select @name=name from #tmp_cxf where i=@count
set @sql='exec sp_spaceused'
set @sql=@sql+''''+@name+''''
--select @sql
insert into #tmp_cxf1 exec(@sql)
set @count=@count+1
end
--取大于100mb的表的大小
select convert(int,replace(rtrim(data),'kb',''))/1024 as mb,* from #tmp_cxf1 where convert(int,replace(rtrim(data),'kb',''))/1024>100
drop table #tmp_cxf,#tmp_cxf1
--这个有lob对像不准
select object_name(id) as tablename,
8*reserved/1024 as reserved,
rtrim(8*dpages/1024)+'mb' as used,
8*(reserved-dpages)/1024 as unused,
8*dpages/1024-rows/1024*minlen/1024 as free, rows
from sysindexes
where indid=1
and rtrim(8*dpages/1024) >=100 /**//*已使用大于mb*/
or rows >=5000000 /**//*行数大于万行*/
order by reserved desc
三sqlserver用户管理
建立用户：
create login qdb with password='test';
use qdb
create user qdb for login qdb with default_schema=qdb;
grant create procedure,create view to qdb
--删除用户
drop user qdb
drop login qdb
--创建角色
create role ProgramerRole
--用于创建 存储过程 视图
grant create procedure,create view to ProgramerRole
--execute用以执行存储过程，alter用以创建、修改存储过程和视图，
--并可以新建立表，但不能修改表,但也可以删除表和对表改名了
grant update,delete,select,insert,execute,alter on schema::dbo to ProgramerRole
--用于允许用户查看 显示估计的执行计划(081205)
grant showplan to ProgramerRole
--将用户TestUser添加到TestRole角色中
exec sp_addrolemember 'ProgramerRole','username'
检常用户权限
exec sp_helpuser qdb
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
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
exec sp_helprotect @username = 'qdb'