管理员专用连接 DAC
一、什么是DAC
SQL Server 2005 为管理员提供了一种特殊的诊断连接,以供在无法与服务器建立标准连接时使用。即使在 SQL Server 不响应标准连接请求时,管理员也可以使用这种连接访问 SQL Server,以便执行诊断查询并解决问题。命令行界面 (sqlcmd) 通过使用特殊的管理员开关 (-A),提供并支持这种专用管理员连接 (DAC)。
二、如何使用DAC登录到服务器
1 本机DAC登录
命令行方式下执行 sqlcmd -A -S sql服务器名
2 远程DAC登录
1)打开远程DAC选项
EXEC sp_configure ‘remote admin connections’, 1;
RECONFIGURE;
2)登录到远程服务器
sqlcmd -A -S 192.168.0.1 -U sa -P 123456
3 登录后, 可以通过查询动态管理视图来诊断问题
三、SQL Server Management Studio 与 DAC 配合使用
1、在 SQL Server Management Studio 中,不打开其他 DAC,单击工具栏上的“数据库引擎查询”。
2、在“连接到数据库引擎”对话框的“服务器名称”框中,键入 ADMIN:,并在其后继续键入服务器实例的名称。例如,若要连接到名为 ACCT\PAYABLE 的服务器实例,请键入 ADMIN:ACCT\PAYABLE。
3、完成“身份验证”部分(提供 sysadmin 组成员的凭据),然后单击“连接”。将建立连接。
如果已在使用 DAC,则连接将失败,并出现错误指示无法连接。
/*
目前这个存储过程只能解密存储过程,
至于解密函数、触发器、视图的存储过程本网站会进一步关注,调用形式为:
exec dbo.sp__windbi$decrypt @procedure,0
如果第二个参数使用的话,会给出该存储过程的一些提示。
–版本.0 修正存储过程过长引起的问题
警告:该存储过程会删除并重建原始的存储过程。’
在运行该存储过程之前确保你的数据库有一个备份。’
该存储过程通常应该运行在产品环境的一个备份的非产品环境下。
*/
/**********************************************************************/
print ‘creating stored procedure sp__windbi$decrypt……’
go
if exists(select name from sysobjects where name = ’sp__windbi$decrypt’ and type = ‘P’)
drop procedure sp__windbi$decrypt
go
create PROCEDURE [dbo].[sp__windbi$decrypt]
(@procedure sysname = NULL)
AS
SET NOCOUNT ON
DECLARE @intProcSpace bigint, @t bigint, @maxColID smallint,@procNameLength int
select @maxColID = max(subobjid) FROM
sys.sysobjvalues WHERE objid = object_id(@procedure)
–select @maxColID as ‘Rows in sys.sysobjvalues’
select @procNameLength = datalength(@procedure) + 29
DECLARE @real_01 nvarchar(max)
DECLARE @fake_01 nvarchar(max)
DECLARE @fake_encrypt_01 nvarchar(max)
DECLARE @real_decrypt_01 nvarchar(max),@real_decrypt_01a nvarchar(max)
declare @objtype varchar(2),@parentname nvarchar(max)
select @real_decrypt_01a = ”
–提取对象的类型如是存储过程还是函数,如果是触发器,还要得到其父对象的名称
select @objtype=type,@parentname=object_name(parent_object_id)
from sys.objects where [object_id]=object_id(@procedure)
– 从sys.sysobjvalues里提出加密的imageval记录
SET @real_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid)
–创建一个临时表
create table #output ( [ident] [int] IDENTITY (1, 1) NOT NULL ,
[real_decrypt] NVARCHAR(MAX) )
–开始一个事务,稍后回滚
BEGIN TRAN
–更改原始的存储过程,用短横线替换
if @objtype=’P'
SET @fake_01=’ALTER PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(cast(‘-’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)
else if @objtype=’FN’
SET @fake_01=’ALTER FUNCTION ‘+ @procedure +’() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/ END’
else if @objtype=’V'
SET @fake_01=’ALTER view ‘+ @procedure +’ WITH ENCRYPTION AS select 1 as col
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/’
else if @objtype=’TR’
SET @fake_01=’ALTER trigger ‘+ @procedure +’ ON ‘+@parentname+’WITHENCRYPTION AFTER INSERT AS RAISERROR (”N”,16,10)
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/’
EXECUTE (@fake_01)
–从sys.sysobjvalues里提出加密的假的
SET @fake_encrypt_01=(SELECT top 1 imageval FROM sys.sysobjvalues WHERE objid =
object_id(@procedure) and valclass = 1 order by subobjid )
if @objtype=’P'
SET @fake_01=’Create PROCEDURE ‘+ @procedure +’ WITH ENCRYPTION AS
‘+REPLICATE(cast(‘-’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)
else if @objtype=’FN’
SET @fake_01=’CREATE FUNCTION ‘+ @procedure +’() RETURNS INT WITH ENCRYPTION AS BEGIN RETURN 1
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/ END’
else if @objtype=’V'
SET @fake_01=’Create view ‘+ @procedure +’ WITH ENCRYPTION AS select 1 as col
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/’
else if @objtype=’TR’
SET @fake_01=’Create trigger ‘+ @procedure +’ ON ‘+@parentname+’WITHENCRYPTION AFTER INSERT AS RAISERROR (”N”,16,10)
/*’+REPLICATE(cast(‘*’as nvarchar(max)), datalength(@real_01) /2 – @procNameLength)+’*/’
–开始计数
SET @intProcSpace=1
–使用字符填充临时变量
SET @real_decrypt_01 = replicate(cast(‘A’as nvarchar(max)), (datalength(@real_01) /2 ))
–循环设置每一个变量,创建真正的变量
–每次一个字节
SET @intProcSpace=1
–如有必要,遍历每个@real_xx变量并解密
WHILE @intProcSpace <=(datalength(@real_01)/2)
BEGIN
–真的和假的和加密的假的进行异或处理
SET @real_decrypt_01 = stuff(@real_decrypt_01, @intProcSpace, 1,
NCHAR(UNICODE(substring(@real_01, @intProcSpace, 1)) ^
(UNICODE(substring(@fake_01, @intProcSpace, 1)) ^
UNICODE(substring(@fake_encrypt_01, @intProcSpace, 1)))))
SET @intProcSpace=@intProcSpace+1
END
–通过sp_helptext逻辑向表#output里插入变量
insert #output (real_decrypt) select @real_decrypt_01
– select real_decrypt AS ‘#output chek’ from #output –测试
– ————————————-
–开始从sp_helptext提取
– ————————————-
declare @dbname sysname
,@BlankSpaceAdded int
,@BasePos int
,@CurrentPos int
,@TextLength int
,@LineId int
,@AddOnLen int
,@LFCR int –回车换行的长度
,@DefinedLength int
,@SyscomText nvarchar(max)
,@Line nvarchar(255)
Select @DefinedLength = 255
SELECT @BlankSpaceAdded = 0 –跟踪行结束的空格。注意Len函数忽略了多余的空格
CREATE TABLE #CommentText
(LineId int
,Text nvarchar(255) collate database_default)
–使用#output代替sys.sysobjvalues
DECLARE ms_crs_syscom CURSOR LOCAL
FOR SELECT real_decrypt from #output
ORDER BY ident
FOR READ ONLY
–获取文本
SELECT @LFCR = 2
SELECT @LineId = 1
OPEN ms_crs_syscom
FETCH NEXT FROM ms_crs_syscom into @SyscomText
WHILE @@fetch_status >= 0
BEGIN
SELECT @BasePos = 1
SELECT @CurrentPos = 1
SELECT @TextLength = LEN(@SyscomText)
WHILE @CurrentPos != 0
BEGIN
–通过回车查找行的结束
SELECT @CurrentPos = CHARINDEX(char(13)+char(10), @SyscomText,
@BasePos)
–如果找到回车
IF @CurrentPos != 0
BEGIN
–如果@Lines的长度的新值比设置的大就插入@Lines目前的内容并继续
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@CurrentPos-@BasePos + @LFCR) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength-(isnull(LEN(@Line),0) +
@BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded = 0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @CurrentPos-@BasePos + @LFCR), N”)
SELECT @BasePos = @CurrentPos+2
INSERT #CommentText VALUES( @LineId, @Line )
SELECT @LineId = @LineId + 1
SELECT @Line = NULL
END
ELSE
–如果回车没找到
BEGIN
IF @BasePos <= @TextLength
BEGIN
–如果@Lines长度的新值大于定义的长度
While (isnull(LEN(@Line),0) + @BlankSpaceAdded +
@TextLength-@BasePos+1 ) > @DefinedLength
BEGIN
SELECT @AddOnLen = @DefinedLength -
(isnull(LEN(@Line),0) + @BlankSpaceAdded)
INSERT #CommentText VALUES
( @LineId,
isnull(@Line, N”) + isnull(SUBSTRING(@SyscomText,
@BasePos, @AddOnLen), N”))
SELECT @Line = NULL, @LineId = @LineId + 1,
@BasePos = @BasePos + @AddOnLen, @BlankSpaceAdded =
0
END
SELECT @Line = isnull(@Line, N”) +
isnull(SUBSTRING(@SyscomText, @BasePos, @TextLength-@BasePos+1 ), N”)
if LEN(@Line) < @DefinedLength and charindex(‘ ‘,
@SyscomText, @TextLength+1 ) > 0
BEGIN
SELECT @Line = @Line + ‘ ‘, @BlankSpaceAdded = 1
END
END
END
END
FETCH NEXT FROM ms_crs_syscom into @SyscomText
END
IF @Line is NOT NULL
INSERT #CommentText VALUES( @LineId, @Line )
select Text from #CommentText order by LineId
CLOSE ms_crs_syscom
DEALLOCATE ms_crs_syscom
DROP TABLE #CommentText
– ————————————-
–结束从sp_helptext提取
– ————————————-
–删除用短横线创建的存储过程并重建原始的存储过程
ROLLBACK TRAN
DROP TABLE #output
×××××××××××××××××××××××××××××××××××××××××××××1××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
描述
返回两个日期之间的时间间隔。
语法
DateDiff(interval, date1, date2 [,firstdayofweek[, firstweekofyear]])
DateDiff 函数的语法有以下参数:
参数 描述
interval 必选。字符串表达式,表示用于计算 date1 和 date2 之间的时间间隔。
有关数值,请参阅“设置”部分。
date1, date2 必选。日期表达式。用于计算的两个日期。
firstdayofweek 可选。指定星期中第一天的常数。如果没有指定,则默认为星期日。
有关数值,请参阅“设置”部分。
firstweekofyear 可选。指定一年中第一周的常数。如果没有指定,则默认为 1 月 1 日所在的星期。
有关数值,请参阅“设置”部分。
设置
interval 参数可以有以下值:
设置 描述
yyyy 年
q 季度
m 月
y 一年的日数
d 日
w 一周的日数
ww 周
h 小时
m 分钟
s 秒
firstdayofweek 参数可以有以下值:
常数 值 描述
vbUseSystem 0 使用区域语言支持 (NLS) API 设置。
vbSunday 1 星期日(默认)
vbMonday 2 星期一
vbTuesday 3 星期二
vbWednesday 4 星期三
vbThursday 5 星期四
vbFriday 6 星期五
vbSaturday 7 星期六
firstweekofyear 参数可以有以下值:
常数 值 描述
vbUseSystem 0 使用区域语言支持 (NLS) API 设置。
vbFirstJan1 1 由 1 月 1 日所在的星期开始(默认)。
vbFirstFourDays 2 由在新年中至少有四天的第一周开始。
vbFirstFullWeek 3 由在新的一年中第一个完整的周开始。
说明
DateDiff 函数用于判断在两个日期之间存在的指定时间间隔的数目。
例如可以使用 DateDiff 计算两个日期相差的天数,或者当天到当年最后一天之间的星期数。
要计算 date1 和 date2 相差的天数,可以使用“一年的日数”(“y”)或“日”(“d”)。
当 interval 为“一周的日数”(“w”)时,DateDiff 返回两个日期之间的星期数。
如果 date1 是星期一,则 DateDiff 计算到 date2 之前星期一的数目。
此结果包含 date2 而不包含 date1。
如果 interval 是“周”(“ww”),则 DateDiff 函数返回日历表中两个日期之间的星期数。
函数计算 date1 和 date2 之间星期日的数目。
如果 date2 是星期日,DateDiff 将计算 date2,但即使 date1 是星期日,也不会计算 date1。
如果 date1 晚于 date2,则 DateDiff 函数返回负数。
firstdayofweek 参数会对使用“w”和“ww”间隔符号的计算产生影响。
如果 date1 或 date2 是日期文字,则指定的年度会成为日期的固定部分。
但是如果 date1 或 date2 被包括在引号 (" ") 中并且省略年份,则在代码中每次计算 date1 或 date2 表达式时,将插入当前年份。
这样就可以编写适用于不同年份的程序代码。
在 interval 为“年”(“yyyy”)时,比较 12 月 31 日和来年的 1 月 1 日,虽然实际上只相差一天,DateDiff 返回 1 表示相差一个年份。
×××××××××××××××××××××××××××××××××××××××××××××2××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
excel导入到sql时的数据类型问题
这里介绍其他方法实现你的要求,
导入excel时,让系统自动创建表,自动选择数据类型,导入为一张临时表,
正常导入到数据库后,在数据库中实现把临时表的数据插入到目标表,
这样做的好处有2点,
能够迅速从excel数据转为数据库数据,
第二,可以用SQL脚本控制从临时表到目标表的数据迁移,操作更加灵活。
×××××××××××××××××××××××××××××××××××××××××××××3××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
inner join(等值连接) 只返回两个表中联结字段相等的行
left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录
right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录
INNER JOIN 语法:
INNER JOIN 连接两个数据表的用法:
SELECT * FROM 表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号
INNER JOIN 连接三个数据表的用法:
SELECT * FROM (表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号
INNER JOIN 连接四个数据表的用法:
SELECT * FROM ((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)
INNER JOIN 表4 ON Member.字段号=表4.字段号
INNER JOIN 连接五个数据表的用法:
SELECT * FROM (((表1 INNER JOIN 表2 ON 表1.字段号=表2.字段号) INNER JOIN 表3 ON 表1.字段号=表3.字段号)
INNER JOIN 表4 ON Member.字段号=表4.字段号) INNER JOIN 表5 ON Member.字段号=表5.字段号
连接六个数据表的用法:略,与上述联接方法类似,大家举一反三吧:)
注意事项:
?在输入字母过程中,一定要用英文半角标点符号,单词之间留一半角空格;
?在建立数据表时,如果一个表与多个表联接,那么这一个表中的字段必须是“数字”数据类型,而多个表中的相同字段必须是主键,而且是“自动编号”数据类型。
否则,很难联接成功。
?代码嵌套快速方法:如,想连接五个表,则只要在连接四个表的代码上加一个前后括号(前括号加在FROM的后面,后括号加在代码的末尾即可),
然后在后括号后面继续添加“INNER JOIN 表名X ON 表1.字段号=表X.字段号”代码即可,这样就可以无限联接数据表了:)
×××××××××××××××××××××××××××××××××××××××××××××4××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
SQL INNER JOIN 关键字
在表中存在至少一个匹配时,INNER JOIN 关键字返回行。
INNER JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
INNER JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释:INNER JOIN 与 JOIN 是相同的。
原始的表 (用在例子中的):
"Persons" 表:
Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
"Orders" 表:
Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65
内连接(INNER JOIN)实例
现在,我们希望列出所有人的定购。
您可以使用下面的 SELECT 语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集:
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
INNER JOIN 关键字在表中存在至少一个匹配时返回行。如果 "Persons" 中的行在 "Orders" 中没有匹配,就不会列出这些行。
×××××××××××××××××××××××××××××××××××××××××××××5××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
SQL LEFT JOIN 关键字
LEFT JOIN 关键字会从左表 (table_name1) 那里返回所有的行,即使在右表 (table_name2) 中没有匹配的行。
LEFT JOIN 关键字语法
SELECT column_name(s)
FROM table_name1
LEFT JOIN table_name2
ON table_name1.column_name=table_name2.column_name
注释:在某些数据库中, LEFT JOIN 称为 LEFT OUTER JOIN。
原始的表 (用在例子中的):
"Persons" 表:
Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing
"Orders" 表:
Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65
左连接(LEFT JOIN)实例
现在,我们希望列出所有的人,以及他们的定购 - 如果有的话。
您可以使用下面的 SELECT 语句:
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
结果集:
LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678
Bush George
LEFT JOIN 关键字会从左表 (Persons) 那里返回所有的行,即使在右表 (Orders) 中没有匹配的行。
×××××××××××××××××××××××××××××××××××××××××××××6××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
not in 和not exists
如果查询语句使用了not in 那么内外表都进行全表扫描,没有用到索引;
而not extsts 的子查询依然能用到表上的索引。
所以无论那个表大,用not exists都比not in要快。
----------------
not in 逻辑上不完全等同于not exists
not (不) exists (存在)
select * from a where not exists (select 1 from b where b.id = a.id)
exists 子查询和 in 子查询的区别之一就在于不直接返回结果集,而是判断结果集条数是否大于零返回true/false,
因此 select 1,select 2,select 什么都可以。
------------------------
就是不在这个范围的意思
select idfrom tablewhere name not exists(select 1
from table
where name = 'aaa')
也许你看不懂这个1的意思,不过在使用EXISTS的时候,通常都会使用1,它代表所查询出来的集合,等同于select name。
整个例子的意思是:查询name不等于aaa的所有ID。
--------------
注意in只允许有一个字段返回
--------------------
EXISTS:
系统要求进行SQL优化,对效率比较低的SQL进行优化,使其运行效率更高,其中要求对SQL中的部分in/not in修改为exists/not exists
修改方法如下:
in的SQL语句
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
FROM tab_oa_pub WHERE is_check=1 and
category_id in (select id from tab_oa_pub_cate where no='1')
order by begintime desc
修改为exists的SQL语句
SELECT id, category_id, htmlfile, title, convert(varchar(20),begintime,112) as pubtime
FROM tab_oa_pub WHERE is_check=1 and
exists (select id from tab_oa_pub_cate where tab_oa_pub.category_id=convert(int,no) and no='1')
order by begintime desc
--------------------------------
×××××××××××××××××××××××××××××××××××××××××××××7××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
一.存储过程中判别记录是否存在
1.--判断用户的ID和密码是否存在
if(exists(select * from UserInfo where UserId=@userId and UserPwd=@oldPwd))
begin
update UserInfo set UserPwd=@newPwd where UserId=@userId
set @result=1
end
else
begin
set @result=-1
end
2.--设置新的密码
declare @password nvarchar(50)
select @password = userpwd from userinfo where userID = @userId
if @password = @oldpassword
begin
update userinfo set userPwd = @newPassword where userId = @userId
set @result = 1
end
else
begin
set @result = -1
end
3.--由用户名称获取用户ID
select userId from UserInfo where UserName=@userName
通过类型转换后判断userId的值,若等于0就表示不存在。
二。程序中写SQL语句时,变量的使用。
如cmd.ComandText="select * from NewsDetail where NewsTypeId="+this.newsTypeId;
或着cmd.ComandText="select * from NewsDetail where NewsTypeId='"+this.newsTypeId+"'";(两单引号内加两双引号)
三.存储过程中如何获取自动增加的值
select @orderId=@@identity
四.isnull的使用。
ISNULL
使用指定的替换值替换 NULL。
语法
ISNULL ( check_expression , replacement_value )
参数
check_expression
将被检查是否为 NULL的表达式。check_expression 可以是任何类型的。
replacement_value
在 check_expression 为 NULL时将返回的表达式。replacement_value 必须与 check_expresssion 具有相同的类型。
返回类型
返回与 check_expression 相同的类型。
注释
如果 check_expression 不为 NULL,那么返回该表达式的值;否则返回 replacement_value。
示例
A. 将 ISNULL 与 AVG 一起使用
下面的示例查找所有书的平均价格,用值 $10.00 替换 titles 表的 price 列中的所有 NULL 条目。
USE pubs
GO
SELECT AVG(ISNULL(price, $10.00))
FROM titles
GO
下面是结果集:
--------------------------
14.24
(1 row(s) affected)
B. 使用 ISNULL
下面的示例为 titles 表中的所有书选择书名、类型及价格。如果一个书名的价格是 NULL,那么在结果集中显示的价格为 0.00。
USE pubs
GO
SELECT SUBSTRING(title, 1, 15) AS Title, type AS Type,
ISNULL(price, 0.00) AS Price
FROM titles
GO
下面是结果集:
Title Type Price
--------------- ------------ --------------------------
The Busy Execut business 19.99
Cooking with Co business 11.95
The Psychology UNDECIDED
c.在存储过程中获取新闻与新闻的前一条和后一条的标题
create procedure GetNews
(
@newsId int
)
as
declare
@priorId int,
@nextId int,
@priorTitle nvarchar(50),
@nextTitle nvarchar(50)
select @priorId=isnull(max(newsId),0) from NewsDetail where NewsId< @newsId
select @priorTitle=NewsTitle from NewsDetail where NewsId= @priorId
select @nextId=isnull(min(newsId),0) from NewsDetail where NewsId> @newsId
select @nextTitle=NewsTitle from NewsDetail where NewsId= @nextId
select
NewsTitle,NewsTime,NewsContent,Hit,@priorTitle as priorTitle,@nextTitle as nextTitle
from
NewsDetail
where
NewsId=@newsId
五.inner join 的使用
INNER JOIN 运算
组合两个表中的记录,只要在公共字段之中有相符的值。
语法
FROM table1 INNER JOIN table2 ON table1.field1 compopr table2.field2
table1, table2 记录被组合的表的名称。
field1, field2 被联接的字段的名称。若它们不是由数字构成的,则这些字段必须为相同的数据类型并包含同类数据,但它们无须具有相同的名称。
compopr 任何的关系比较运算子:"=," "<," ">," "<=," ">=," 或 "<>."
说明
可以在 FROM 子句中使用INNER JOIN运算。.这是最普通的联接类型。只要在这两个表的公共字段之中有相符值,内部联接将组合两个表中的记录。
可以使用 INNER JOIN 与部门表及员工表选择每一个部门中的全部员工。反之,可以使用 LEFT JOIN或 RIGHT JOIN运算创建 outer join,从而选择所有部门(即使有些并没有员工)或所有员工(即使有些尚未分配到部门)。
若试图联接包含 Memo或 OLE Object数据的字段,会导致错误。
可以联接任何两个相同类型的数值字段。例如,可以联接 AutoNumber和 Long字段,因为它们类型相似。但不能联接 Single 和 Double 类型的字段。
下列示例显示如何在类标识符字段联接类表及产品表:
SELECT CategoryName, ProductName
FROM Categories INNER JOIN Products
ON Categories.CategoryID = Products.CategoryID;
在上面的示例中,类标识符是已被联接的字段,但是它并不包含在查询输出中,因它并非被包含在 SELECT 语句之中。在这个示例中,若要包含联接字段,将字段名包含在 SELECT 语句中, Categories.CategoryID.
也可以使用下列语法,在一个 JOIN 语句中链接多个 ON 子句:
SELECT fields
FROM table1 INNER JOIN table2
ON table1.field1 compopr table2.field1 AND
ON table1.field2 compopr table2.field2) OR
ON table1.field3 compopr table2.field3)];
也可以使用下列语法,嵌套 JOIN 语句:
SELECT fields
FROM table1 INNER JOIN
(table2 INNER JOIN [( ]table3
[INNER JOIN [( ]tablex [INNER JOIN ...)]
ON table3.field3 compopr tablex.fieldx)]
ON table2.field2 compopr table3.field3)
ON table1.field1 compopr table2.field2;
在一个 INNER JOIN 之中,可以嵌套 LEFT JOIN 或 RIGHT JOIN,但是在 LEFT JOIN 或 RIGHT JOIN 中不能嵌套 INNER JOIN。
六.在存储过程中的模糊查询
string sql="select * from Article where pass=1 and deleted=0 and Title like '%"+@keyword+"%' order by UpdateTime desc"
把'%"+@keyword+"%'看成一起
直接写在存储过程中 select * from ProductInfowhere ProductName like '%'+@keyWords+'%'
七.使用SQL存储过程返回多结果集怎么处理
如果有多个结果集返回,那么客户端一定要用一个DataSet来接收才行,这样可以通过DataSet的.Tables来分别访问它返回的数据集了.
也可以用output
DataTable dt = ds.tables[2]
dt.rows[0]["newsId"].tostring
八.如何一次插入多个记录
insert into
OrderDetail
select
@orderId,ShoppingCart.ProductId,ShoppingCart.Quantity,Product.ProductPrice
from
ShoppingCart inner join
Product on ShoppingCart.ProductId=Product.ProductId
where
cartId=@cartId
九.求两个字段的值的积,然后再求和
cast(sum(OrderDetail.Quantity*OrderDetail.UnitCost) as money) as OrderTotal
十.定义为char类型的缺点:有空字符串
解决方法:
经过高手指点,才知道原来红色的代码有问题,interbase数据库如果某个字段是char类型的数据,才存放数据后会自动在字符串后面补上空格,以满足位数需要,因此表面上看,取到的密码和我的输入密码一样,实际上是不一样的。
解决办法:
办法1 if ibt_user_pass.FieldByName('passwd').AsString=edit2.Text then 这句话变成if trim(ibt_user_pass.FieldByName('passwd').AsString)=edit2.Text then
trim函数作用是去掉字符串中多加上去的空格
办法2 数据库的char类型改成varchar类型
十一。在程序中使用存储过程
SqlCommand cmd =new SqlCommand("proGetEmpPassword", cn);
cmd.CommandType = CommandType.StoredProcedure;
SqlParameter paramLoginName=new SqlParameter("@LoginName", SqlDbType.Char, 20);
paramLoginName.Value = strLoginName;
cmd.Parameters.Add(paramLoginName);
SqlParameter paramPassword=new SqlParameter("@Pword", SqlDbType.VarChar, 50);
paramPassword.Direction = ParameterDirection.Output;
cmd.Parameters.Add(paramPassword);
SqlParameter paramReturn=new SqlParameter("@ReturnValue", SqlDbType.Int);
paramReturn.Direction = ParameterDirection.ReturnValue;
cmd.Parameters.Add(paramReturn);//在存储过程中为return @@rowcount,用 来判断,区别select @orderId=@@identity
string arPword ;
try
{
cn.Open();
cmd.ExecuteNonQuery();
if (Convert.ToInt32(paramReturn.Value) == 1)
arPword = (paramPassword.Value).ToString();
else
return null;
}
catch
{
return null;
}
finally
{
if (cn.State == ConnectionState.Open)
cn.Close();
}
return arPword;
}
×××××××××××××××××××××××××××××××××××××××××××××8××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
set ANSI_NULLS OFF
set QUOTED_IDENTIFIER OFF
///是 SQL-92 设置语句,使 SQL Server 2000/2005 遵从 SQL-92 规则。
///当 SET QUOTED_IDENTIFIER 为 ON 时,标识符可以由双引号分隔,而文字必须由单引号分隔。
///当 SET QUOTED_IDENTIFIER 为 OFF 时,标识符不可加引号,且必须符合所有 Transact-SQL 标识符规则。
/// SQL-92 标准要求在对空值进行等于 (=) 或不等于 (<>) 比较时取值为 FALSE。
///当 SET ANSI_NULLS 为 ON 时,即使 column_name 中包含空值,使用 WHERE column_name = NULL 的 SELECT 语句仍返回零行。
///即使 column_name 中包含非空值,使用 WHERE column_name <> NULL 的 SELECT 语句仍会返回零行。
/// 当 SET ANSI_NULLS 为 OFF 时,等于 (=) 和不等于 (<>) 比较运算符不遵从 SQL-92 标准。
///使用 WHERE column_name = NULL 的 SELECT 语句返回 column_name 中包含空值的行。
///使用 WHERE column_name <> NULL 的 SELECT 语句返回列中包含非空值的行。
///此外,使用 WHERE column_name <> XYZ_value 的 SELECT 语句返回所有不为 XYZ_value 也不为 NULL 的行。
GO
/*
执行语句范例:
SELECT * FROM ARTICLE w1
WHERE ID in
(
SELECT top 30 ID FROM
(
SELECT top 1030 ID, YEAR FROM ARTICLE ORDER BY YEAR DESC, ID DESC
) w ORDER BY w.YEAR ASC, w.ID ASC
)
ORDER BY w1.YEAR DESC, w1.ID DESC
执行操作:
declare @a int,@b int
EXECUTE PageResult @cAction='do',@PageIndex='1',@PageSize='10',@sys_Table='tb_newall_product',@sys_Key='idno',@sys_Fields='*',@sys_Where='',@sys_OrderFields='dtordertime',@sys_OrderFields2='dtaddtime',@sys_Order='1',@OutTotalPage=@a output,@OutTotalRecord=@b output
EXECUTE PageResult @cAction='do',@PageIndex='1',@PageSize='10',@sys_Table=' tb_newall_product',@sys_JoinSql='left join d_company on iUserID=d_company.id',@sys_Key='idno',@sys_Fields='dtaddtime,*',@sys_Where=' cUsertype=''c'' and d_company.id=''15839'' ',@sys_OrderFields='dtaddtime',@sys_Order='1'
declare @a int,@b int
EXECUTE PageResult @cAction='do',@PageIndex=-1,@PageSize=1,@sys_Table=' Tb_Log_ck',@sys_JoinSql=' left join tb_xj_info on tb_xj_info.id=Tb_Log_ck.iRecordID',@sys_Key='id',@sys_Fields='tb_xj_info.*',@sys_Where='',@sys_OrderFields='',@sys_Order='2',@OutTotalPage=@a output,@OutTotalRecord=@b output
select @a,@b
*/
ALTER PROCEDURE [dbo].[PageResult]
@cAction varchar(30), --1:主键排序 2:主键与排序字段不相同
@PageIndex int, --当前页数 -PageIndex:-1 显示全部记录
@PageSize int, --页大小
@sys_Table nvarchar(1000), --查询表名
@sys_Key varchar(50), --主键
@sys_Fields nvarchar(3000), --查询字段
@sys_Where nvarchar(3000)='', --查询条件
@sys_OrderFields nvarchar(500)='', --排序字段
@sys_OrderFields2 nvarchar(500)='', --排序字段
@sys_OrderFields3 nvarchar(500)='', --排序字段
@sys_Order nvarchar(100)='1', --排序字段
@sys_JoinSql nvarchar(500)='', --关联表语句 格式:left join d_company on iUserID=d_company.id
@sys_JoinSql2 nvarchar(500)='',
@sys_JoinSql3 nvarchar(500)='',
@OutTotalPage int=0 Output,
@OutTotalRecord int=0 Output
AS
SET NOCOUNT ON
DECLARE @Sql NVARCHAR(4000)
DECLARE @Sql_order1 NVARCHAR(100)
DECLARE @Sql_order2 NVARCHAR(100)
DECLARE @Sql_order3 NVARCHAR(100)
DECLARE @Sql_Count NVARCHAR(4000)
DECLARE @Sql_1 NVARCHAR(4000),@Sql_2 NVARCHAR(4000)
if @cAction='do'
Begin
set @Sql_order1=''
set @Sql_order2=''
set @Sql_order3=''
if @sys_Where<>'' set @sys_Where=' where '+@sys_Where
if @sys_Order='1'
Begin
if @sys_OrderFields<>''
Begin
set @Sql_order1=@Sql_order1+@sys_OrderFields+' DESC,'
set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields+' ASC,'
set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields+' DESC,'
End
if @sys_OrderFields2<>''
Begin
set @Sql_order1=@Sql_order1+@sys_OrderFields2+' DESC,'
set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields2+' ASC,'
set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields2+' DESC,'
End
if @sys_OrderFields3<>''
Begin
set @Sql_order1=@Sql_order1+@sys_OrderFields3+' DESC,'
set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields3+' ASC,'
set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields3+' DESC,'
End
set @Sql_order1=' ORDER BY '+@Sql_order1+@sys_Table+'.'+@sys_Key+' DESC'
set @Sql_order2=' ORDER BY '+@Sql_order2+'w.'+@sys_Key+' ASC'
set @Sql_order3=' ORDER BY '+@Sql_order3+@sys_Table+'.'+@sys_Key+' DESC'
End
if @sys_Order='2'
Begin
if @sys_OrderFields<>''
Begin
set @Sql_order1=@Sql_order1+@sys_OrderFields+' ASC,'
set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields+' DESC,'
set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields+' ASC,'
End
if @sys_OrderFields2<>''
Begin
set @Sql_order1=@Sql_order1+@sys_OrderFields2+' ASC,'
set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields2+' DESC,'
set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields2+' ASC,'
End
if @sys_OrderFields3<>''
Begin
set @Sql_order1=@Sql_order1+@sys_OrderFields3+' ASC,'
set @Sql_order2=@Sql_order2+'w.'+@sys_OrderFields3+' DESC,'
set @Sql_order3=@Sql_order3+@sys_Table+'.'+@sys_OrderFields3+' ASC,'
End
set @Sql_order1=' ORDER BY '+@Sql_order1+@sys_Table+'.'+@sys_Key+' ASC'
set @Sql_order2=' ORDER BY '+@Sql_order2+'w.'+@sys_Key+' DESC'
set @Sql_order3=' ORDER BY '+@Sql_order3+@sys_Table+'.'+@sys_Key+' ASC'
End
--输出总记录数、总页数
SET @Sql_Count = 'SELECT @OutTotalRecord=COUNT(1),@OutTotalPage=CEILING((COUNT(1)+0.0)/'
+ CAST(@PageSize AS NVARCHAR)+') FROM ' + @sys_Table +' '+ @sys_JoinSql+' '+@sys_JoinSql2+' '+@sys_JoinSql3+@sys_Where
EXEC SP_EXECUTESQL @Sql_Count,N'@OutTotalRecord INT OUTPUT,@OutTotalPage INT OUTPUT', @OutTotalRecord OUTPUT,@OutTotalPage OUTPUT
--输出记录集
if @PageIndex=-1
set @Sql='SELECT '+@sys_Fields+' FROM '+@sys_Table+' '+@sys_JoinSql+' '+@sys_JoinSql2+' '+@sys_JoinSql3+@sys_Where+@Sql_order1
else
Begin
if @sys_OrderFields<>'' set @sys_OrderFields=','+@sys_OrderFields
if @sys_OrderFields2<>'' set @sys_OrderFields2=','+@sys_OrderFields2
if @sys_OrderFields3<>'' set @sys_OrderFields3=','+@sys_OrderFields3
set @Sql_1='SELECT top '+ CAST(@PageSize * @PageIndex AS NVARCHAR)+' '+@sys_Table+'.'+@sys_Key+@sys_OrderFields+@sys_OrderFields2+@sys_OrderFields3+' FROM '+@sys_Table+' '+@sys_JoinSql+' '+@sys_JoinSql2+' '+@sys_JoinSql3+@sys_Where+@Sql_order1
if @PageIndex=@OutTotalPage and (@OutTotalRecord % @PageSize!=0)
set @Sql_2='SELECT top '+ CAST(@OutTotalRecord % @PageSize AS NVARCHAR)+' '+@sys_Key+' FROM ('+@Sql_1+') w '+@Sql_order2
else
set @Sql_2='SELECT top '+ CAST(@PageSize AS NVARCHAR)+' '+@sys_Key+' FROM ('+@Sql_1+') w '+@Sql_order2
set @Sql='SELECT top '+ CAST(@PageSize AS NVARCHAR)+' '+@sys_Fields+' FROM '+@sys_Table+' '+@sys_JoinSql+' '+@sys_JoinSql2+' '+@sys_JoinSql3+' WHERE '+@sys_Table+'.'+@sys_Key+' in ('+@Sql_2+' ) '+@Sql_order3
End
--print @Sql
Exec(@Sql)
End
SET NOCOUNT OFF
×××××××××××××××××××××××××××××××××××××××××××××9××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
首先,在SQL Server中创建存储过程:
--创建添加数据的存储过程
if exists (select * from sysobjects where name='proc_AddUser')
drop proc proc_AddUser
go
create procedure proc_AddUser
@uId int output;
@uName nvarchar(20);
@uAge int;
@uPass nvarchar(200)
with encryption --对存储过程进行加密
as
insert into UserInfo (uName,uAge,password) values (@uName,@uAge,@uPass)
select @uId=@@identity
go
其次,在C#项目中搭建三层架构,并且在数据访问层创建DBService类(数据库操作通用类,
可以连接数据库,对数据库数据进行增删改查的操作)
DBService.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Configuration; //配置文件
namespace Ch3DemoDAL
{
public class DBService
{
#region 私有构造
///<summary>
///私有构造函数
///</summary>
private DBService()
{
}
#endregion
#region 私有成员
///<summary>
///静态私有的数据库连接字符串
///</summary>
private static readonly string strConn = ConfigurationManager.ConnectionStrings
["Ch3Demo.Properties.Settings.Setting"].ToString(); //这里需要配置文件App.config才可用
///<summary>
///静态私有的数据库连接对象,封装字段
///</summary>
private static SqlConnection conn = null;
public static SqlConnection Conn
{
get
{
if ( conn == null)
{
conn = new SqlConnection(strConn);
conn.Open();
}
else if(conn.State == ConnectionState.Closed)
{
conn.Open();
}
else if(conn.State == ConnectionState.Broken)
{
conn.Close();
conn.Open();
}
return DBService.conn;
}
}
#endregion
#region 公共方法,静态公共的数据库访问方法(必须是静态的)
///<summary>
///执行数据库的增删改查操作
///</summary>
///<param name="sql"></param>
///<returns>受影响行数</returns>
public static int ExecuteCommand(string sql)
{
using(SqlCommand.command = new SqlCommand(sql,Conn))
{
return command.ExecuteNonQuery();
}
}
///<summary>
///执行带参数的SQL语句
///</summary>
///<param name="sql"></param>
///<param name="values"></param>
///<returns></returns>
public static int ExecuteCommand(string sql,params SqlParameter[] values)
{
using(SqlCommand command = new SqlCommand(sql,Conn))
{
command.Parameters.AddRange(values);
return command.EndExecuteNonQuery();
}
}
///<summary>
///执行数据库的增删改查操作
///</summary>
///<param name="sql"></param>
///<returns>首行首列</returns>
public static int ExecuteScalar(string sql)
{
using(SqlCommand.command = new SqlCommand(sql,Conn))
{
return Convert.ToInt32(command.ExecuteScalar());
}
}
///<summary>
///执行带参数的SQL语句
///</summary>
///<param name="sql"></param>
///<param name="values"></param>
///<returns></returns>
public static int ExecuteScalar(string sql,params SqlParameter[] values)
{
using(SqlCommand command = new SqlCommand(sql,Conn))
{
command.Parameters.AddRange(values);
return Convert.ToInt32(command.ExecuteScalar());
}
}
///<summary>
///获得DataReader
///</summary>
///<param name="sql"></param>
///<returns></returns>
public static SqlDataReader GetRerader(string sql)
{
SqlCommand command = new SqlCommand(sql,Conn);
SqlDataReader dataReader = command .ExecuteReader(CommandBehavior.CloseConnection);
return dataReader;
}
///<summary>
///执行带参数的SQL语句
///</summary>
///<param name="sql"></param>
///<param name="values"></param>
///<returns></returns>
public static SqlDataReader GetRerader(string sql,params SqlParameter[] values)
{
SqlCommand command = new SqlCommand(sql,Conn);
command.Parameters.AddRange(values);
SqlDataReader dataReader = command.ExecuteReader(CommandBehavior.CloseConnection);
return dataReader;
}
///<summary>
///获得结果集
///</summary>
///<param name="sql"></param>
///<returns></returns>
public static DataSet GetResult(string sql)
{
DataSet dataSet = new DataSet();
using(SqlDataAdapter da = new SqlDataAdapter(sql,Conn))
{
da.Fill(dataSet);
}
return dataSet;
}
#endregion
}
}
之后,再在数据访问层创建一个AddUserService类,用于添加用户;该类就运用存储过程对程序进行增加用户的
AddUserService.cs
using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace Ch3DemoDAL
{
public class AddUserService
{
public static int AddUser(entity UserInfo user)
{
int id = 0;
//使用存储过程实现添加数据
//proc_AddUser 为存储过程名称
using(SqlCommand command = new SqlCommand(""proc_AddUser),DBService.Conn)
{
//指定command对象的执行方式
command.CommandType = CommandType.StoredProcedure;
//指定存储过程的参数并赋值
command.Parameter.Add("@uName",SqlDbType.NVarChar,20).Value = user.Name;
command.Parameter.Add("@uAge",SqlDbType.Int).Value = userAge;
command.Parameter.Add("@uPass",SqlDbType.NVarChar,200).Value = user.Password;
//设置输出参数
command.Parameters.Add("@uId",SqlDbType.Int).Direction
= Parameter.Direction.Output; //@uId,@uName等参数必须与数据库存储过程中的参数一致
//执行
command.ExecuteNonQuery();
//获取输出参数的值
id=Convert.ToInt32(command.Parameters["@uId".Value]);
}
return id;
}
}
}
--------------------------------------------------------------------------------------------------------------------
一个写出更好的分页代码的办法是使用存储过程。以下显示了一个示例存储过程,它为Northwind
数据库中的Orders数据表分页。总的来说,在这里所有需要做的就是传入页的索引和页的容量。
数据库会计算出适当的结果集然后返回它们。
CREATE PROCEDURE northwind_OrdersPaged
(
@PageIndex int,
@PageSize int
)
AS
BEGIN
DECLARE @PageLowerBound int
DECLARE @PageUpperBound int
DECLARE @RowsToReturn int
--First set the rowcount
SET @RowsToReturn = @PageSize * (@PageIndex + 1)
SET ROWCOUNT @RowToReturn
--Set the page bounds
SET @PageLowerBound = @PageSize * @PageIndex
SET @PageUpperBound = @PageLowerBound + @PageSize + 1
--Create a temp table to store the select results
CREATE TABLE #PageIndex
(
IndexId int IDENTITY(1,1) NOT NULL,
OrderID int
)
--Insert into the temp table
INSERT INTO #PageIndex(OrderID)
SELECT
OrderID
FROM
Orders
ORDER BY
OrderID DESC
--Return total count
SELECT COUNT(OrderID) FROM Orders
--Return paged results
SELECT
O.*
FROM
Orders O,
#PageIndex PageIndex
WHERE
O.OrderID = PageIndex.OrderID AND
PageIndex.IndexID>@PageLowerBound AND
PageIndex.IndexID<@PageUpperBound
ORDER BY
PageIndex.IndexID
END
-----------------------------------------------------------------------------------------------------------------
×××××××××××××××××××××××××××××××××××××××××××××10××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
模版:Create procedure procedue_name [@parameter data_type][output]
[with]{recompile|encryption} as sql_statement
解释:output:表示此参数是可传回的
with {recompile|encryption} recompile:表示每次执行此存储过程时都重新编译一次;encryption:所创建的存储过程的内容会被加密。
×××××××××××××××××××××××××××××××××××××××××××××11××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××
14.3.2 将所有的char、varchar改为nchar、nvarchar.sql
CREATE PROC p_ToUnicode
@type tinyint=0 --修改方式,0=仅查询可修改情况,1=仅所有列可修改时才修改,2=修改可修改列,报告不可修改列
AS
SET NOCOUNT ON
--查询非unicode列转换为unicode列的可行性
SELECT TableName=o.name,FieldName=c.name,
FieldType=t.name+N'('+CAST(c.prec as varchar)+N')'
+CASE WHEN c.isnullable=1 THEN N'' ELSE N' NOT' END
+N' NULL',
NoChangeCause=CAST(STUFF(
CASE WHEN COLUMNPROPERTY(c.id,c.name,N'IsComputed')=1
THEN N',计算列' ELSE N'' END
+CASE WHEN c.cdefault=0 THEN N'' ELSE N',列具有默认值' END
+CASE WHEN EXISTS(
SELECT * FROM sysindexkeys idxk,sysindexes idx
WHERE idxk.id=c.id
AND idxk.colid=c.colid
AND idxk.id=idx.id
AND idxk.indid=idx.indid
AND idx.indid NOT IN(0,255)
AND INDEXPROPERTY(idx.id,idx.name,N'IsAutoStatistics')=0)
THEN N',列被主键、唯一键、索引、STATISTICS引用' ELSE N'' END
+CASE WHEN EXISTS(
SELECT * FROM sysforeignkeys WHERE fkeyid=c.id AND fkey=c.colid)
THEN N',列被外键约束引用' ELSE N'' END
+CASE WHEN EXISTS(
SELECT * FROM sysobjects oc,sysdepends d
WHERE oc.parent_obj=o.id
AND OBJECTPROPERTY(oc.id,N'IsCheckCnst')=1
AND d.id=oc.id
AND d.depnumber=c.colid)
THEN N',列被CHECK约束引用' ELSE N'' END,1,1,N'') as nvarchar(4000))
INTO # FROM sysobjects o,syscolumns c,systypes t
WHERE o.id=c.id and o.status>=0
AND OBJECTPROPERTY(o.id,N'IsUserTable')=1
AND t.xusertype=c.xusertype
AND t.name in('char','varchar')
IF @@ROWCOUNT=0 RETURN
--如果需要,修改非unicode列为unicode列
IF @type=2 OR NOT EXISTS(SELECT * FROM # WHERE NoChangeCause>'')
BEGIN
SET XACT_ABORT ON
BEGIN TRAN
DECLARE tb CURSOR LOCAL
FOR
SELECT N'ALTER TABLE '+QUOTENAME(TableName)
+N' ALTER COLUMN '+QUOTENAME(FieldName)
+N' N'+FieldType
FROM #
WHERE NoChangeCause IS NULL
DECLARE @sql nvarchar(4000)
OPEN tb
FETCH tb INTO @sql
WHILE @@FETCH_STATUS=0
BEGIN
EXEC sp_executesql @sql
FETCH tb INTO @sql
END
CLOSE tb
DEALLOCATE tb
COMMIT TRAN
END
--显示不能修改的列
SELECT TableName,FieldName,FieldType,
NoChangeCause=ISNULL(NoChangeCause,N'可以修改(或者已经修改成功)')
FROM #
ORDER BY CASE WHEN NoChangeCause IS NULL THEN 1 ELSE 0 END,TableName
×××××××××××××××××××××××××××××××××××××××××××××12××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××××