-- Author: <vagerent>
-- Create date: <2006-12-14>
-- Description: <获取编码,形如HEAD20060512000001;(头+日期+6位整数)后面为六位,每新的一年编号重新
归0 >
-- =============================================
Create PROCEDURE [ dbo ] . [ mp_GetPKNo ]
@sTableName varchar ( 50 ),
@sFieldName varchar ( 50 ), -- 目标字段
@sHead varchar ( 50 ), -- 前缀,如Head等
@sPK varchar ( 50 ) out -- 输出
AS
BEGIN
declare @dToday datetime , @sToday varchar ( 10 ),
@sYear varchar ( 10 ), @sSubID varchar ( 20 ), @sSql nvarchar ( 1000 ); -- 动态sql一定要用
nvarchar !!!
set @dToday = getdate ();
set @sToday = Convert ( varchar , Year ( @dToday ))
+ SubString ( Convert ( varchar , Month ( @dToday ) + 100 ), 2 , 2 )
+ SubString ( Convert ( varchar , Day ( @dToday ) + 100 ), 2 , 2 );
-- select @sToday--形如20060512
set @sYear = Convert ( varchar ( 10 ), Year ( getdate ()))
-- select @sYear--形如2006
declare @sHead1 varchar ( 30 ), @iHeadLength int ;
set @sHead1 = @sHead + @sYear ; -- 形如HEAD2006
-- select @sHead1;--形如HEAD2006
set @iHeadLength = Len ( @sHead1 );
-- select @iHeadLength;--形如6
set @sSql = N ' select @sSubID=Max(SubString( '
+ @sFieldName + ' , ' + Convert ( varchar ( 10 ), @iHeadLength + 5 ) + ' ,6)) from '
+ @sTableName + ' where SubString( ' + @sFieldName + ' ,1, '
+ Convert ( varchar ( 10 ), @iHeadLength ) + ' )= ''' + @sHead1 + '''' ;
-- select @sSql;--形如select..
exec sp_executesql @sSql ,N ' @sSubID varchar(20) output ' , @sSubID output;
if (( @sSubID = '' ) or ( @sSubID is null ))
set @sSubID = ' 000001 '
else
begin
declare @iSubID int ;
set @iSubID = Convert ( int , @sSubID ) + 1000001 ;
set @sSubID = SubString ( Convert ( varchar , @iSubID ), 2 , 6 )
end
-- select @sSubID;
set @sPK = @sHead + @sToday + @sSubID ;
return
END
use northwind
go
set statistics IO on
go
select count ( * ) from northwind.dbo.employees
go
set statistics IO off
go
use northwind
go
exec sp_spaceused employees
go
-- Demo 2:
set statistics time on
go
select count ( * ) from northwind.dbo.employees
go
set statistics time off
go
-- Demo 3:
set showplan_text on
go
select count ( * ) from northwind.dbo.employees
go
set showplan_text off
go
-- Demo 4:
set nocount on
go
select count ( * ) from northwind.dbo.employees
go
set nocount off
go
-- Demo 5查询单条sql语句的执行时间:
declare @start_time datetime
select @start_time = getdate ()
select * from northwind.dbo.employees
select ' 查询语句的执行时间(毫秒) ' = datediff (ms, @start_time , getdate ())
-- Demo 6查询成批的sql语句的执行时间:
create table #save_time(start_time datetime not null )
insert #save_time values ( getdate ())
go
select * from employees
go
select * from orders
go
select ' 查询语句的执行时间(毫秒) ' = datediff (ms,start_time, getdate ())
from #save_time
drop table #save_time
go
-- Demo 7返回语句的执行计划内容:
set showplan_all on
go
select * from pubs.dbo.authors
go
set showplan_all off
go
-- Demo 8从执行计划判断是否需要优化SQL:
/*SEEK操作*/
set showplan_all on
go
select * from pubs.dbo.sales where stor_id >= ' 7131 '
go
set showplan_all off
go
/*SCAN操作*/
set showplan_all on
go
select * from pubs.dbo.sales where ord_date is not null
go
set showplan_all off
go
-- Demo 9连接查询VS子查询:
/*子查询*/
set statistics io on
go
select au_fname,au_lname from pubs.dbo.authors where au_id in
( select au_id from pubs.dbo.titleauthor)
set statistics io off
go
/*连接查询*/
set statistics io on
go
select distinct au_fname,au_lname from pubs.dbo.authors as a inner join
pubs.dbo.titleauthor as t on a.au_id = t.au_id
go
set statistics io off
go
-- Demo 10智能优化:
select p1.productname from northwind.dbo.products as p1 inner join
northwind.dbo.products as p2 on (p1.unitprice = p2.unitprice)
where p2.productname like ' Alice% '
1. 查看数据库的版本
select @@version
常见的几种SQL SERVER打补丁后的版本号:
8.00.194 Microsoft SQL Server 2000
8.00.384 Microsoft SQL Server 2000 SP1
8.00.532 Microsoft SQL Server 2000 SP2
8.00.760 Microsoft SQL Server 2000 SP3
8.00.818 Microsoft SQL Server 2000 SP3 w/ Cumulative Patch MS03-031
8.00.2039 Microsoft SQL Server 2000 SP4
2. 查看数据库所在机器操作系统参数
exec master..xp_msver
3. 查看数据库启动的参数
sp_configure
4. 查看数据库启动时间
select convert(varchar(30),login_time,120) from master..sysprocesses where spid=1
查看数据库服务器名和实例名
print 'Server Name: ' + convert(varchar(30),@@SERVERNAME)
print 'Instance: ' + convert(varchar(30),@@SERVICENAME)
5. 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的SQL
sp_renamedb 'old_dbname', 'new_dbname'
6. 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner [@objectname =] 'object', [@newowner =] 'owner'
注意: 更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
查看某数据库下,对象级用户权限
sp_helprotect
7. 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8.查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用sp_toptables过程看最大的N(默认为50)个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname
clustered索引是把记录按物理顺序排列的,索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束,fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9.查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext '@procedure_name'
查看包含某个字符串@str的数据对象名称
select distinct object_name(id) from syscomments where text like '%@str%'
创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
解密加密过的存储过程和函数可以用sp_decrypt过程
10.查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who 'active'
查看SQL Server数据库里的锁的情况
sp_lock
进程号1--50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3
检查死锁用sp_who_lock过程
sp_who_lock
11.查看和收缩数据库日志文件的方法
查看所有数据库日志文件大小
dbcc sqlperf(logspace)
如果某些日志文件较大,收缩简单恢复模式数据库日志,收缩后@database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile (@database_name_log, 5)
12.分析SQL Server SQL 语句的方法:
set statistics time {on | off}
set statistics io {on | off}
图形方式显示查询执行计划
在查询分析器->查询->显示估计的评估计划(D)-Ctrl-L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all {on | off}
set showplan_text { on | off }
set statistics profile { on | off }
13.出现不一致错误时,NT事件查看器里出3624号错误,修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作
alter database [@error_database_name] set single_user
修复出现不一致错误的表
dbcc checktable('@error_table_name',repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb('@error_database_name',repair_allow_data_loss)
alter database [@error_database_name] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,
以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。
修复完成后,请备份数据库。
repair_fast 进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。
这些修复可以很快完成,并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复,包括需要较长时间的修复(如重建索引)。
执行这些修复时不会有丢失数据的危险。
Select * From TableName Order By CustomerName Collate Chinese_PRC_Stroke_ci_as
2 .数据库加密:
select encrypt( ' 原始密码 ' )
select pwdencrypt( ' 原始密码 ' )
select pwdcompare( ' 原始密码 ' , ' 加密后密码 ' ) = 1 -- 相同;否则不相同 encrypt('原始密码')
select pwdencrypt( ' 原始密码 ' )
select pwdcompare( ' 原始密码 ' , ' 加密后密码 ' ) = 1 -- 相同;否则不相同
3 .取回表中字段:
declare @list varchar ( 1000 ), @sql nvarchar ( 1000 )
select @list = @list + ' , ' + b.name from sysobjects a,syscolumns b where a.id = b.id and a.name = ' 表A '
set @sql = ' select ' + right ( @list , len ( @list ) - 1 ) + ' from 表A '
exec ( @sql )
4 .查看硬盘分区:
EXEC master..xp_fixeddrives
5 .比较A,B表是否相等:
if ( select checksum_agg(binary_checksum( * )) from A)
=
( select checksum_agg(binary_checksum( * )) from B)
print ' 相等 '
else
print ' 不相等 '
6 .杀掉所有的事件探察器进程:
DECLARE hcforeach CURSOR GLOBAL FOR SELECT ' kill ' + RTRIM (spid) FROM master.dbo.sysprocesses
WHERE program_name IN ( ' SQL profiler ' ,N ' SQL 事件探查器 ' )
EXEC sp_msforeach_worker ' ? '
7 .记录搜索:
开头到N条记录
Select Top N * From 表
-- -----------------------------
N到M条记录(要有主索引ID)
Select Top M - N * From 表 Where ID in ( Select Top M ID From 表) Order by ID Desc
-- --------------------------------
N到结尾记录
Select Top N * From 表 Order by ID Desc
8 .如何修改数据库的名称:
sp_renamedb ' old_name ' , ' new_name '
9 :获取当前数据库中的所有用户表
select Name from sysobjects where xtype = ' u ' and status >= 0
10 :获取某一个表的所有字段
select name from syscolumns where id = object_id ( ' 表名 ' )
11 :查看与某一个表相关的视图、存储过程、函数
select a. * from sysobjects a, syscomments b where a.id = b.id and b. text like ' %表名% '
12 :查看当前数据库中所有存储过程
select name as 存储过程名称 from sysobjects where xtype = ' P '
13 :查询用户创建的所有数据库
select * from master..sysdatabases D where sid not in ( select sid from master..syslogins where name = ' sa ' )
或者
select dbid, name AS DB_NAME from master..sysdatabases where sid < > 0x01
14 :查询某一个表的字段和数据类型
select column_name,data_type from information_schema.columns
where table_name = ' 表名 '
[ n ] . [ 标题 ] :
Select * From TableName Order By CustomerName
[ n ] . [ 标题 ] :
select rows from sysindexes where id = object_id ( ' test ' ) and indid in ( 0 , 1 )
提取数据库内所有表的字段详细说明的SQL语句 :
SELECT
( case when a.colorder = 1 then d.name else '' end ) N ' 表名 ' ,
a.colorder N ' 字段序号 ' ,
a.name N ' 字段名 ' ,
( case when COLUMNPROPERTY ( a.id,a.name, ' IsIdentity ' ) = 1 then ' √ ' else ''
end ) N ' 标识 ' ,
( case when ( SELECT count ( * )
FROM sysobjects
WHERE (name in
( SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
( SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
( SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = ' PK ' )) > 0 then ' √ ' else '' end ) N ' 主键 ' ,
b.name N ' 类型 ' ,
a.length N ' 占用字节数 ' ,
COLUMNPROPERTY (a.id,a.name, ' PRECISION ' ) as N ' 长度 ' ,
isnull ( COLUMNPROPERTY (a.id,a.name, ' Scale ' ), 0 ) as N ' 小数位数 ' ,
( case when a.isnullable = 1 then ' √ ' else '' end ) N ' 允许空 ' ,
isnull (e. text , '' ) N ' 默认值 ' ,
isnull (g. [ value ] , '' ) AS N ' 字段说明 '
FROM syscolumns a
left join systypes b
on a.xtype = b.xusertype
inner join sysobjects d
on a.id = d.id and d.xtype = ' U ' and d.name <> ' dtproperties '
left join syscomments e
on a.cdefault = e.id
left join sysproperties g
on a.id = g.id AND a.colid = g.smallid
order by object_name (a.id),a.colorder
获取表结构 [ 把 'sysobjects' 替换 成 'tablename' 即可 ]
SELECT CASE IsNull (I.name, '' )
When '' Then ''
Else ' * '
End as IsPK,
Object_Name (A.id) as t_name,
A.name as c_name,
IsNull ( SubString (M. text , 1 , 254 ), '' ) as pbc_init,
T.name as F_DataType,
CASE IsNull ( TYPEPROPERTY (T.name, ' Scale ' ), '' )
WHEN '' Then Cast (A.prec as varchar )
ELSE Cast (A.prec as varchar ) + ' , ' + Cast (A.scale as varchar )
END as F_Scale,
A.isnullable as F_isNullAble
FROM Syscolumns as A
JOIN Systypes as T
ON (A.xType = T.xUserType AND A.Id = Object_id ( ' sysobjects ' ) )
LEFT JOIN ( SysIndexes as I
JOIN Syscolumns as A1
ON ( I.id = A1.id and A1.id = object_id ( ' sysobjects ' ) and (I.status & 0x800 ) = 0x800 AND A1.colid <= I.keycnt) )
ON ( A.id = I.id AND A.name = index_col ( ' sysobjects ' , I.indid, A1.colid) )
LEFT JOIN SysComments as M
ON ( M.id = A.cdefault and ObjectProperty (A.cdefault, ' IsConstraint ' ) = 1 )
ORDER BY A.Colid ASC
四种方法取表里n到m条纪录:
1 .
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
2 .
select top n * from ( select top m * from tablename order by columnname) a order by columnname desc
3 .如果tablename里没有其他identity列,那么:
select identity ( int ) id0, * into # temp from tablename
取n到m条的语句为:
select * from # temp where id0 >= n and id0 <= m
如果你在执行 select identity ( int ) id0, * into # temp from tablename这条语句的时候报错,那是因为你的DB中间的select into / bulkcopy属性没有打开要先执行:
exec sp_dboption 你的DB名字, ' select into/bulkcopy ' ,true
4 .如果表里有identity属性,那么简单:
select * from tablename where identitycol between n and m
-- ------------------------------------------------------------------------------
数据库管理常用SQL
1 . 查看数据库的版本
select @@version
2 . 查看数据库所在机器操作系统参数
exec master..xp_msver
3 . 查看数据库启动的参数
sp_configure
4 . 查看数据库启动时间
select convert ( varchar ( 30 ),login_time, 120 ) from master..sysprocesses where spid = 1
查看数据库服务器名和实例名
print ' Server Name: ' + convert ( varchar ( 30 ), @@SERVERNAME )
print ' Instance: ' + convert ( varchar ( 30 ), @@SERVICENAME )
5 . 查看所有数据库名称及大小
sp_helpdb
重命名数据库用的SQL
sp_renamedb ' old_dbname ' , ' new_dbname '
6 . 查看所有数据库用户登录信息
sp_helplogins
查看所有数据库用户所属的角色信息
sp_helpsrvrolemember
修复迁移服务器时孤立用户时,可以用的fix_orphan_user脚本或者LoneUser过程
更改某个数据对象的用户属主
sp_changeobjectowner [ @objectname = ] ' object ' , [ @newowner = ] ' owner '
注意: 更改对象名的任一部分都可能破坏脚本和存储过程。
把一台服务器上的数据库用户登录信息备份出来可以用add_login_to_aserver脚本
7 . 查看链接服务器
sp_helplinkedsrvlogin
查看远端数据库用户登录信息
sp_helpremotelogin
8 .查看某数据库下某个数据对象的大小
sp_spaceused @objname
还可以用sp_toptables过程看最大的N(默认为50)个表
查看某数据库下某个数据对象的索引信息
sp_helpindex @objname
还可以用SP_NChelpindex过程查看更详细的索引情况
SP_NChelpindex @objname
clustered索引是把记录按物理顺序排列的,索引占的空间比较少。
对键值DML操作十分频繁的表我建议用非clustered索引和约束,fillfactor参数都用默认值。
查看某数据库下某个数据对象的的约束信息
sp_helpconstraint @objname
9 .查看数据库里所有的存储过程和函数
use @database_name
sp_stored_procedures
查看存储过程和函数的源代码
sp_helptext ' @procedure_name '
查看包含某个字符串 @str的数据对象名称
select distinct object_name (id) from syscomments where text like ' %@str% '
创建加密的存储过程或函数在AS前面加WITH ENCRYPTION参数
解密加密过的存储过程和函数可以用sp_decrypt过程
10 .查看数据库里用户和进程的信息
sp_who
查看SQL Server数据库里的活动用户和进程的信息
sp_who ' active '
查看SQL Server数据库里的锁的情况
sp_lock
进程号1 -- 50是SQL Server系统内部用的,进程号大于50的才是用户的连接进程.
spid是进程编号,dbid是数据库编号,objid是数据对象编号
查看进程正在执行的SQL语句
dbcc inputbuffer ()
推荐大家用经过改进后的sp_who3过程可以直接看到进程运行的SQL语句
sp_who3
检查死锁用sp_who_lock过程
sp_who_lock
11 .收缩数据库日志文件的方法
收缩简单恢复模式数据库日志,收缩后 @database_name_log的大小单位为M
backup log @database_name with no_log
dbcc shrinkfile ( @database_name_log , 5 )
12 .分析SQL Server SQL 语句的方法:
set statistics time { on | off }
set statistics io { on | off }
图形方式显示查询执行计划
在查询分析器 -> 查询 -> 显示估计的评估计划(D) - Ctrl - L 或者点击工具栏里的图形
文本方式显示查询执行计划
set showplan_all { on | off }
set showplan_text { on | off }
set statistics profile { on | off }
13 .出现不一致错误时,NT事件查看器里出3624号错误,修复数据库的方法
先注释掉应用程序里引用的出现不一致性错误的表,然后在备份或其它机器上先恢复然后做修复操作
alter database [ @error_database_name ] set single_user
修复出现不一致错误的表
dbcc checktable( ' @error_table_name ' ,repair_allow_data_loss)
或者可惜选择修复出现不一致错误的小型数据库名
dbcc checkdb( ' @error_database_name ' ,repair_allow_data_loss)
alter database [ @error_database_name ] set multi_user
CHECKDB 有3个参数:
repair_allow_data_loss 包括对行和页进行分配和取消分配以改正分配错误、结构行或页的错误,
以及删除已损坏的文本对象,这些修复可能会导致一些数据丢失。
修复操作可以在用户事务下完成以允许用户回滚所做的更改。
如果回滚修复,则数据库仍会含有错误,应该从备份进行恢复。
如果由于所提供修复等级的缘故遗漏某个错误的修复,则将遗漏任何取决于该修复的修复。
修复完成后,请备份数据库。
repair_fast 进行小的、不耗时的修复操作,如修复非聚集索引中的附加键。
这些修复可以很快完成,并且不会有丢失数据的危险。
repair_rebuild 执行由 repair_fast 完成的所有修复,包括需要较长时间的修复(如重建索引)。
执行这些修复时不会有丢失数据的危险。