几则技巧-[SQL2000未公开函数:加密,姓氏排序.etc]
/*1)按姓氏笔画排序*/
select * from TableName order by ColumnName Collate CHINESE_PRC_STROKE_CI_AS
/*2)数据库加密函数*/
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
/*3)比较A,B表是否相等*/
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
/*4)杀掉所有的事件探察器进程:*/
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 '?'
select * from TableName order by ColumnName Collate CHINESE_PRC_STROKE_CI_AS
/*2)数据库加密函数*/
select encrypt('原始密码')
select pwdencrypt('原始密码')
select pwdcompare('原始密码','加密后密码') = 1--相同;否则不相同 encrypt('原始密码')
/*3)比较A,B表是否相等*/
if (select checksum_agg(binary_checksum(*)) from A)
=
(select checksum_agg(binary_checksum(*)) from B)
print '相等'
else
print '不相等'
/*4)杀掉所有的事件探察器进程:*/
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 '?'
--eg::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
/*管理员登录检测*/
CREATE PROCEDURE [dbo].[ST_CheckAdmin_F]
@AdminNO VARCHAR(50),--登陆名称
@Password VARCHAR(15) --密码
AS
DECLARE @Status tinyint
DECLARE @Pwd varbinary(255)
SELECT @Pwd=[Admin_PSW]
FROM dbo.ST_Admin
WHERE [Admin_NO]=@AdminNO AND [IsValid]=1
IF @@RowCount<>1 SELECT @Status=0--NO Exist;
ELSE
BEGIN
IF PWDCOMPARE(@Password,@Pwd,0)<>1 SELECT @Status=2--Password error;
ELSE SELECT @Status=1--OK;
END
SELECT @Status
/*
修改密码:
UPDATE [dbo].[ST_Admin]
SET [Admin_PSW] = CONVERT(VARBINARY(255),PWDENCRYPT('admin'))
WHERE [ID]=3
*/
GO
/*管理员登录检测*/
CREATE PROCEDURE [dbo].[ST_CheckAdmin_F]
@AdminNO VARCHAR(50),--登陆名称
@Password VARCHAR(15) --密码
AS
DECLARE @Status tinyint
DECLARE @Pwd varbinary(255)
SELECT @Pwd=[Admin_PSW]
FROM dbo.ST_Admin
WHERE [Admin_NO]=@AdminNO AND [IsValid]=1
IF @@RowCount<>1 SELECT @Status=0--NO Exist;
ELSE
BEGIN
IF PWDCOMPARE(@Password,@Pwd,0)<>1 SELECT @Status=2--Password error;
ELSE SELECT @Status=1--OK;
END
SELECT @Status
/*
修改密码:
UPDATE [dbo].[ST_Admin]
SET [Admin_PSW] = CONVERT(VARBINARY(255),PWDENCRYPT('admin'))
WHERE [ID]=3
*/
GO