1.视图 存储过程 触发器 批量加密（With Encryption），单个解密
在运行过程中自己找不到启用DAC 的地方，链接的时候需要在服务器名称前面添加ADMIN:，如本机是ADMIN:WP-PC
另外加密后的对象可以用ＳＱＬ ｐｒｏｍｐｔ 直接单独查看，所以意义不是很大；防一些不懂的人吧，亲测可以使用；
网络上面有2000的，我自己测试在2008R2，作者本人在2012上面测试也是ＯＫ的
--加密存储过程 可以批量加密参数All 或者单个加密 已经加密的会有提示
Use
master
Go
if
object_ID
(
'
[sp_EncryptObject]
'
)
is
not
null
Drop
Procedure
[
sp_EncryptObject
]
Go
create
procedure
sp_EncryptObject
(
@Object
sysname
=
'
All
'
)
as
/*
当@Object=All的时候，对所有的函数，存储过程，视图和触发器进行加密
调用方法：
1. Execute sp_EncryptObject 'All'
2. Execute sp_EncryptObject 'ObjectName'
*/
begin
set
nocount
on
if
@Object
<>
'
All
'
begin
if
not
exists
(
select
1
from
sys.objects a
where
a.
object_id
=
object_id
(
@Object
)
And
a.type
in
(
'
P
'
,
'
V
'
,
'
TR
'
,
'
FN
'
,
'
IF
'
,
'
TF
'
))
begin
--
SQL Server 2008
raiserror
50001
N
'
无效的加密对象！加密对象必须是函数，存储过程，视图或触发器。
'
--
SQL Server 2012
--
throw 50001, N'无效的加密对象！加密对象必须是函数，存储过程，视图或触发器。',1
return
end
if
exists
(
select
1
from
sys.sql_modules a
where
a.
object_id
=
object_id
(
@Object
)
and
a.definition
is
null
)
begin
--
SQL Server 2008
raiserror
50001
N
'
对象已经加密！
'
--
SQL Server 2012
--
throw 50001, N'对象已经加密！',1
return
end
end
declare
@sql
nvarchar
(
max
),
@C1
nchar
(
1
),
@C2
nchar
(
1
),
@type
nvarchar
(
50
),
@Replace
nvarchar
(
50
)
set
@C1
=
nchar
(
13
)
set
@C2
=
nchar
(
10
)
declare
cur_Object
cursor
for
select
object_name
(a.
object_id
)
As
ObjectName,a.definition
from
sys.sql_modules a
inner
join
sys.objects b
on
b.
object_id
=
a.
object_id
and
b.is_ms_shipped
=
0
and
not
exists
(
select
1
from
sys.extended_properties x
where
x.major_id
=
b.
object_id
and
x.minor_id
=
0
and
x.class
=
1
and
x.name
=
'
microsoft_database_tools_support
'
)
where
b.type
in
(
'
P
'
,
'
V
'
,
'
TR
'
,
'
FN
'
,
'
IF
'
,
'
TF
'
)
and
(b.name
=
@Object
or
@Object
=
'
All
'
)
and
b.name
<>
'
sp_EncryptObject
'
and
a.definition
is
not
null
order
by
Case
when
b.type
=
'
V
'
then
1
when
b.type
=
'
TR
'
then
2
when
b.type
in
(
'
FN
'
,
'
IF
'
,
'
TF
'
)
then
3
else
4
end
,b.create_date,b.
object_id
open
cur_Object
fetch
next
from
cur_Object
into
@Object
,
@sql
while
@@fetch_status
=
0
begin
Begin
Try
if
objectproperty
(
object_id
(
@Object
),
'
ExecIsAfterTrigger
'
)
=
0
set
@Replace
=
'
As
'
;
else
set
@Replace
=
'
For
'
;
if
(
patindex
(
'
%
'
+
@C1
+
@C2
+
@Replace
+
@C1
+
@C2
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C1
+
@C2
+
@Replace
+
@C1
+
@C2
,
@C1
+
@C2
+
'
With Encryption
'
+
@C1
+
@C2
+
@Replace
+
@C1
+
@C2
)
end
else
if
(
patindex
(
'
%
'
+
@C1
+
@Replace
+
@C1
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C1
+
@Replace
+
@C1
,
@C1
+
'
With Encryption
'
+
@C1
+
@Replace
+
@C1
)
end
else
if
(
patindex
(
'
%
'
+
@C2
+
@Replace
+
@C2
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C2
+
@Replace
+
@C2
,
@C2
+
'
With Encryption
'
+
@C2
+
@Replace
+
@C2
)
end
else
if
(
patindex
(
'
%
'
+
@C2
+
@Replace
+
@C1
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C2
+
@Replace
+
@C1
,
@C1
+
'
With Encryption
'
+
@C2
+
@Replace
+
@C1
)
end
else
if
(
patindex
(
'
%
'
+
@C1
+
@C2
+
@Replace
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C1
+
@C2
+
@Replace
,
@C1
+
@C2
+
'
With Encryption
'
+
@C1
+
@C2
+
@Replace
)
end
else
if
(
patindex
(
'
%
'
+
@C1
+
@Replace
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C1
+
@Replace
,
@C1
+
'
With Encryption
'
+
@C1
+
@Replace
)
end
else
if
(
patindex
(
'
%
'
+
@C2
+
@Replace
+
'
%
'
,
@sql
)
>
0
)
begin
set
@sql
=
Replace
(
@sql
,
@C2
+
@Replace
,
@C2
+
'
With Encryption
'
+
@C2
+
@Replace
)
end
set
@type
=
case
when
object_id
(
@Object
,
'
P
'
)
>
0
then
'
Proc
'
when
object_id
(
@Object
,
'
V
'
)
>
0
then
'
View
'
when
object_id
(
@Object
,
'
TR
'
)
>
0
then
'
Trigger
'
when
object_id
(
@Object
,
'
FN
'
)
>
0
or
object_id
(
@Object
,
'
IF
'
)
>
0
or
object_id
(
@Object
,
'
TF
'
)
>
0
then
'
Function
'
end
set
@sql
=
Replace
(
@sql
,
'
Create
'
+
@type
,
'
Alter
'
+
@type
)
Begin
Transaction
exec
(
@sql
)
print
N
'
已完成加密对象(
'
+
@type
+
'
)：
'
+
@Object
Commit
Transaction
End
Try
Begin
Catch
Declare
@Error
nvarchar
(
2047
)
Set
@Error
=
'
Object:
'
+
@Object
+
@C1
+
@C2
+
'
Error:
'
+
Error_message()
Rollback
Transaction
print
@Error
print
@sql
End
Catch
fetch
next
from
cur_Object
into
@Object
,
@sql
end
close
cur_Object
deallocate
cur_Object
end
Go
exec
sp_ms_marksystemobject
'
sp_EncryptObject
'
--
标识为系统对象
go
--解密存储过程
Use
master
Go
if
object_ID
(
'
[sp_DecryptObject]
'
)
is
not
null
Drop
Procedure
[
sp_DecryptObject
]
Go
create
procedure
sp_DecryptObject
(
@Object
sysname,
--
要解密的对象名：函数，存储过程，视图或触发器
@MaxLength
int
=
4000
--
评估内容的长度
)
as
set
nocount
on
/*
1. 解密
*/
if
not
exists
(
select
1
from
sys.objects a
where
a.
object_id
=
object_id
(
@Object
)
And
a.type
in
(
'
P
'
,
'
V
'
,
'
TR
'
,
'
FN
'
,
'
IF
'
,
'
TF
'
))
begin
--
SQL Server 2008
raiserror
50001
N
'
无效的对象！要解密的对象必须是函数，存储过程，视图或触发器。
'
--
SQL Server 2012
--
throw 50001, N'无效的对象！要解密的对象必须是函数，存储过程，视图或触发器。',1
return
end
if
exists
(
select
1
from
sys.sql_modules a
where
a.
object_id
=
object_id
(
@Object
)
and
a.definition
is
not
null
)
begin
--
SQL Server 2008
raiserror
50001
N
'
对象没有加密！
'
--
SQL Server 2012
--
throw 50001, N'无效的对象！要解密的对象必须是函数，存储过程，视图或触发器。',1
return
end
declare
@sql
nvarchar
(
max
)
--
解密出来的SQL语句
,
@imageval
nvarchar
(
max
)
--
加密字符串
,
@tmpStr
nvarchar
(
max
)
--
临时SQL语句
,
@tmpStr_imageval
nvarchar
(
max
)
--
临时SQL语句(加密后)
,
@type
char
(
2
)
--
对象类型('P','V','TR','FN','IF','TF')
,
@objectID
int
--
对象ID
,
@i
int
--
While循环使用
,
@Oject1
nvarchar
(
1000
)
set
@objectID
=
object_id
(
@Object
)
set
@type
=
(
select
a.type
from
sys.objects a
where
a.
object_id
=
@objectID
)
declare
@Space4000
nchar
(
4000
)
set
@Space4000
=
replicate
(
'
-
'
,
4000
)
/*
@tmpStr 会构造下面的SQL语句
-------------------------------------------------------------------------------
alter trigger Tr_Name on Table_Name with encryption for update as return /*
*/
alter
proc
Proc_Name
with
encryption
as
select
1
as
col
/**/
alter
view
View_Name
with
encryption
as
select
1
as
col
/**/
alter
function
Fn_Name()
returns
int
with
encryption
as
begin
return
(
0
)
end
/**/
*/
set
@Oject1
=
quotename
(object_schema_name(
@objectID
))
+
'
.
'
+
quotename
(
@Object
)
set
@tmpStr
=
case
when
@type
=
'
P
'
then
N
'
Alter Procedure
'
+
@Oject1
+
'
with encryption as select 1 as column1
'
when
@type
=
'
V
'
then
N
'
Alter View
'
+
@Oject1
+
'
with encryption as select 1 as column1
'
when
@type
=
'
FN
'
then
N
'
Alter Function
'
+
@Oject1
+
'
() returns int with encryption as begin return(0) end
'
when
@type
=
'
IF
'
then
N
'
Alter Function
'
+
@Oject1
+
'
() returns table with encryption as return(Select a.name from sys.types a)
'
when
@type
=
'
TF
'
then
N
'
Alter Function
'
+
@Oject1
+
'
() returns @t table(name nvarchar(50)) with encryption as begin return end
'
else
'
Alter Trigger
'
+
@Oject1
+
'
on
'
+
quotename
(object_schema_name(
@objectID
))
+
'
.
'
+
(
select
Top
(
1
)
quotename
(
object_name
(parent_id))
from
sys.triggers a
where
a.
object_id
=
@objectID
)
+
'
with encryption for update as return
'
end
set
@tmpStr
=
@tmpStr
+
'
/*
'
+
@Space4000
set
@i
=
0
while
@i
<
(
ceiling
(
@MaxLength
*
1.0
/
4000
)
-
1
)
begin
set
@tmpStr
=
@tmpStr
+
@Space4000
Set
@i
=
@i
+
1
end
set
@tmpStr
=
@tmpStr
+
'
*/
'
--
----------
set
@imageval
=
(
select
top
(
1
) a.imageval
from
sys.sysobjvalues a
where
a.objid
=
@objectID
and
a.valclass
=
1
)
begin
tran
exec
(
@tmpStr
)
set
@tmpStr_imageval
=
(
select
top
(
1
) a.imageval
from
sys.sysobjvalues a
where
a.objid
=
@objectID
and
a.valclass
=
1
)
rollback
tran
--
-----------
set
@tmpStr
=
stuff
(
@tmpStr
,
1
,
5
,
'
create
'
)
set
@sql
=
''
set
@i
=
1
while
@i
<=
(
datalength
(
@imageval
)
/
2
)
begin
set
@sql
=
@sql
+
isnull
(
nchar
(
unicode
(
substring
(
@tmpStr
,
@i
,
1
))
^
unicode
(
substring
(
@tmpStr_imageval
,
@i
,
1
))
^
unicode
(
substring
(
@imageval
,
@i
,
1
)) ),
''
)
Set
@i
+=
1
end
/*
2. 列印
*/
declare
@patindex
int
while
@sql
>
''
begin
set
@patindex
=
patindex
(
'
%
'
+
char
(
13
)
+
char
(
10
)
+
'
%
'
,
@sql
)
if
@patindex
>
0
begin
print
substring
(
@sql
,
1
,
@patindex
-
1
)
set
@sql
=
stuff
(
@sql
,
1
,
@patindex
+
1
,
''
)
end
else
begin
set
@patindex
=
patindex
(
'
%
'
+
char
(
13
)
+
'
%
'
,
@sql
)
if
@patindex
>
0
begin
print
substring
(
@sql
,
1
,
@patindex
-
1
)
set
@sql
=
stuff
(
@sql
,
1
,
@patindex
,
''
)
end
else
begin
set
@patindex
=
patindex
(
'
%
'
+
char
(
10
)
+
'
%
'
,
@sql
)
if
@patindex
>
0
begin
print
substring
(
@sql
,
1
,
@patindex
-
1
)
set
@sql
=
stuff
(
@sql
,
1
,
@patindex
,
''
)
end
else
begin
print
@sql
set
@sql
=
''
end
end
end
end
Go
exec
sp_ms_marksystemobject
'
sp_DecryptObject
'
--
标识为系统对象
go
--解密测试
CREATE
PROC
sp_SplitResult2
With
Encryption
As
BEGIN
SELECT
*
FROM
dbo.Orders
END
exec
sp_DecryptObject sp_SplitResult2
http://www.cnblogs.com/lyhabc/p/3384906.html
http://www.cnblogs.com/wghao/archive/2012/12/30/2837642.html
下面的是利用工具批量解密 网址 都是华仔的
http://www.cnblogs.com/lyhabc/p/3505677.html
2.
以游标技术，列举出所有学生的名单，包括学生姓名、选择的课程的数量，SQL题目
-- 前面先取一次数据，后面再调用赋值的变量 在循环里面利用赋值的变量 去到课程表里面找所选课程数量
--假设有2个表 tStudent(sno,name ) tCourse(sno CourseName)
--
假设有2个表 tStudent(sno,name ) tCourse(sno CourseName)
IF
OBJECT_ID
(
'
tStudent
'
)
>
0
DROP
TABLE
tStudent
IF
OBJECT_ID
(
'
tCourse
'
)
>
0
DROP
TABLE
tCourse
CREATE
TABLE
tStudent
(
sno
VARCHAR
(
10
) ,
name
NVARCHAR
(
10
)
)
CREATE
TABLE
tCourse
(
sno
VARCHAR
(
10
) ,
CourseName
NVARCHAR
(
10
)
)
INSERT
dbo.tStudent
( sno, name )
VALUES
(
'
001
'
,
--
fstudentno - varchar(10)
N
'
小张
'
--
fname - nvarchar(10)
)
INSERT
dbo.tStudent
( sno, name )
VALUES
(
'
002
'
,
--
fstudentno - varchar(10)
N
'
小李
'
--
fname - nvarchar(10)
)
INSERT
dbo.tStudent
( sno, name )
VALUES
(
'
003
'
,
--
fstudentno - varchar(10)
N
'
小如
'
--
fname - nvarchar(10)
)
INSERT
dbo.tCourse
( sno, CourseName )
VALUES
(
'
001
'
,
--
sno - varchar(10)
N
'
英语
'
--
CourseName - nvarchar(10)
)
INSERT
dbo.tCourse
( sno, CourseName )
VALUES
(
'
001
'
,
--
sno - varchar(10)
N
'
语文
'
--
CourseName - nvarchar(10)
)
INSERT
dbo.tCourse
( sno, CourseName )
VALUES
(
'
002
'
,
--
sno - varchar(10)
N
'
语文
'
--
CourseName - nvarchar(10)
)
--建立存储过程 里面使用游标遍历所有学生
Create
PROC
GetInfo
AS
BEGIN
DECLARE
curName
CURSOR
FAST_FORWARD
FOR
(
SELECT
DISTINCT
*
FROM
dbo.tStudent
)
OPEN
curName
DECLARE
@sno
VARCHAR
(
10
) ,
@name
NVARCHAR
(
10
) ,
@coursenum
INT
DECLARE
@tb
TABLE
(
name
NVARCHAR
(
10
) ,
coursenum
INT
)
FETCH
NEXT
FROM
curName
INTO
@sno
,
@name
SELECT
@coursenum
=
ISNULL
(
COUNT
(
DISTINCT
CourseName),
0
)
FROM
tCourse
WHERE
sno
=
@sno
--
INSERT @tb
--
SELECT @name ,
--
@coursenum
WHILE
@@FETCH_STATUS
=
0
BEGIN
SELECT
@coursenum
=
ISNULL
(
COUNT
(
DISTINCT
CourseName),
0
)
FROM
tCourse
WHERE
sno
=
@sno
INSERT
@tb
SELECT
@name
,
@coursenum
FETCH
NEXT
FROM
curName
INTO
@sno
,
@name
END
SELECT
*
FROM
@tb
CLOSE
curName
DEALLOCATE
curName
END
-- 查看执行结果
exec GetInfo
