本文档汇总了SQL Server中处理数据库单用户模式、并发问题、数据查询与合并、以及表结构提取的多种方法。包括强制进入和退出单用户模式的步骤，解决并发操作错误的解决方案，SQL查询特定日期范围内的数据，以及如何通过SQL语句合并重复记录。同时，提供了创建存储过程和批量建表的示例，方便数据库管理和维护。
怎么样进入单用户模式与取消单用户模式，服务器有人访问的时候？
为什么总是出现如下的提示，该怎么办呢？
服务器: 消息 5070，级别 16，状态 2，行 1
如果其他用户正使用数据库 'elong'，则数据库状态不能更改
服务器: 消息 5069，级别 16，状态 1，行 1
ALTER DATABASE 语句失败。
sp_dboption 命令失败。
use master
go
--kill所有会话
Declare @tblConnectedUsers Table (SPID int)
Declare @vcSQLText varchar(200),@iSPID int)
Insert into @tblConnectedUsers
Select p.spid
from master.dbo.sysprocesses p (nolock)
join master..sysdatabases d (nolock) on p.dbid = d.dbid
Where d.[name] = 'elong'
While 1 = 1
Begin
Select top 1 @iSPID = SPID
From @tblConnectedUsers
Where SPID > IsNull(@iSPID, 0)
order by SPID asc
If @@RowCount = 0
Break
Set @vcSQLText = 'Kill ' + Convert(varchar(10), @iSPID)
Exec(@vcSQLText)
End
--执行操作
ALTER DATABASE ............
iwl 于 2005-10-14 0:48:00
重起服务，在执行你的语句
wyb0026(小小) 于 2005-10-14 9:20:16
sp_dboption 'database_name', 'single user','true'
---------------------------------------------------------------------------------
我sql比较烂，还有一个简单的问题～
这个问题解决了7天前是否在10.1和10.3之间；
declare @date datetime
set @date=dateadd(day,-7,getdate())
if (datepart(month,@date)=10) and (datepart(day,@date) between 1 and 3)
print '在10.1和10.3之间'
else
print '不在'
但是getdate()不能为当前日期，它是从数据库里查出来的；
为select createtime form table where id='1';
getdate() 为createtime，
这个在存储过程怎么改
karonlux 于 2005-10-19 11:05:12
怎样判断createtime是否在10.1和10.3之间，完整点的，谢谢～
libin_ftsafe(子陌红尘) 于 2005-10-19 11:10:51
if exists(select
1
from
(select createtime=dateadd(dd,-7,createtime) from table where id='1') a
where
datepart(month,createtime)=10 and (datepart(day,createtime) between 1 and 3)
print '在10.1和10.3之间'
else
print '不在'
churchatp1(别看资料，看聊效！) 于 2005-10-19 11:13:07
select createtime form table where id='1' and datepart(month,dateadd(day,-7,createtime))=10 and (datepart(day,dateadd(day,-7,createtime)) between 1 and 3 )
scmail81(freedom) 于 2005-10-19 11:13:46
select case when (select 1 form table where table.id='1' and table.id=T.id and dateadd(day,-7,createtime)=1 between '2005-10-1' and '2005-10-3') then '在10.1和10.3之间' else '不在' end
from table T
singlepine(小山) 于 2005-10-19 11:42:24
CREATE PROCEDURE tp_Copy_Relative (
@date datetime
)
AS
BEGIN
SET NOCOUNT ON
--declare @date datetime
set @date=dateadd(day,-7,createtime)
if (datepart(month,@date)=10) and (datepart(day,@date) between 1 and 3)
print '在10.1和10.3之间'
else
print '不在'
end
IF @@ERROR <> 0
RETURN 1
return 0
go
------------------------------------------------------------------------------------------------------------------------
在线求解答。。。。。。想了半天了还是无从下手。。。
表中有物品名称Product，使用人user 字段
由于一件物品可能有多个使用人
我现在想select得到的结果是
Product users
－－－－－－－－－－－－－－
桌子 张三，李四，王五，
电脑 赵六，李四，
椅子 孙一，张三
。 。
。 。
。 。
。 。
。 。
（即把表中的物品名都列出来，然后物品所对应的使用人用逗号隔开放在一个字段里）
多谢！！！！！！！！！！！
zlp321002(飘过) 于 2005-10-26 16:27:29
--建函数然后处理:
--Sample:
有表內容﹕
編號 內容
A abc
A aaa
A dddd
B 1223
B fkdjfd
....
實現結果﹕
A abc,aaa,dddd
B 1223,fkdjfd
要求用一條SQL實現﹐如﹕select sum(內容) from table group by 編號
--该问题,写一个合并函数,后,分组合并既可！
--测试数据
create Table 表(編號 varchar(20),內容 varchar(20))
insert 表 select 'A','abc'
union all select 'A','aaa'
union all select 'A','dddd'
union all select 'B','1223'
union all select 'B','fkdjfd'
--处理分组合并函数学
Create Function JoinStr(@SNO as varchar(20))
returns varchar(200)
begin
declare @s as varchar(8000)
set @s=''
select @s=@s+','+ltrim(rtrim(內容)) from
(
select 內容 from 表 where 編號=@SNO
)
A
set @s=stuff(@s,1,1,'')
return @s
end
--查询语句
select 編號,dbo.JoinStr(編號) as 内容 from 表 group by 編號
--测试结果:
編號 内容
A abc,aaa,dddd
B 1223,fkdjfd
vivianfdlpw 于 2005-10-26 16:32:03
create function f_str(@Product varchar(20))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
selelct @str=@str+','+[user] from 表 where Product=@Product
return stuff(@str,1,1,'')
end
go
--调用
select Product
,dbo.f_str(Product)
from 表
group by Product
scmail81(freedom) 于 2005-10-26 16:51:03
见例子！--生成测试数据
create table 表(编码 varchar(10),描述 varchar(20))
insert into 表 select '001','www'
insert into 表 select '001','eee'
insert into 表 select '001','fff'
insert into 表 select '002','ppp'
insert into 表 select '002','999'
go
--创建用户定义函数
create function f_str(@code int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+' '+描述 from 表 where 编码 = @code
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select 编码,描述=dbo.f_str(编码) from 表 group by 编码 order by 编码
go
--输出结果
/*
编码 描述
---- --------------
001 www eee fff
002 ppp ggg
*/
zmj122112(大大懒猪) 于 2005-10-26 17:05:01
服务器: 消息 208，级别 16，状态 1，行 1
对象名 'dbo.f_str' 无效。
－－－－－－－－－－－－－－－－－－－－－－
为什么都显示我对象名无效呢。。奇怪
------------------------------------------------------------------------------------------------------------------------
sql server 2000 中查询不唯五记录问题
各位大侠：
小弟有个问题请教，在sql server 2000的一个表xsxx（学生信息表）中有几万条记录，记录的顺序没有规律，xsxx表中有xh,xm,xb,bj,jxnf,bynf六个字段，我要查询出里面相同的记录，也就是说查询里面不唯一的记录，把它列出来，请问如何查询？
xueguang(xg) 于 2005-10-27 19:04:52
select xh,xm,xb,bj,jxnf,bynf from xsxx group by xh,xm,xb,bj,jxnf,bynf having count(1)>1
vivianfdlpw 于 2005-10-27 19:18:57
select * from tb t
where (select count(1)
from tb
where xh=t.xh
and xm=t.xm
and xb=t.xb
and bj=t.bj
and jxnf=t.jxnf
and bynf=t.bynf
)>1
------------------------------------------------------------------------------------------------------------------------
如何用一个SQL语句将同一个字段的多个值以一条记录返回？请进
表结构是这样的：
serialno parameter value
--------------------------------------
LJ832785QA Voltage 0.2179481
LJ832785QA Current 2.920481
LJ832785QA Resistance 74.62748
请问能否用SQL语句得到一个如下的记录集?
serialno Voltage Current Resistance
---------------------------------------------------
LJ832785QA 0.2179481 2.920481 74.62748
请帮忙，谢谢！
libin_ftsafe(子陌红尘) 于 2005-10-28 10:01:42
example:
--生成测试数据
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1,'张三'
insert into 表 select 1,'李四'
insert into 表 select 1,'王五'
insert into 表 select 2,'赵六'
insert into 表 select 2,'邓七'
insert into 表 select 2,'刘八'
go
--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+人员 from 表 where 部门 = @department
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go
--输出结果
/*
部门 人员
---- --------------
1 张三,李四,王五
2 赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go
freegoal(呵呵) 于 2005-10-28 10:06:34
libin_ftsafe(子陌红尘),谢谢你的回复，但我觉得你提供的跟我所要求的不是一个意思啊。
zlp321002(飘过) 于 2005-10-28 10:11:33
--测试环境
create table #T(serialno varchar(20),parameter varchar(20),value float)
insert into #T select 'LJ832785QA','Voltage',0.2179481
union all select 'LJ832785QA','Current',2.920481
union all select 'LJ832785QA','Resistance',74.62748
--动态SQL
declare @s varchar(2000)
select @s='select serialno '
select @s=@s+',['+parameter+']=sum(case when parameter='''+parameter+''' then value end) '
from #T
Group by parameter
set @s=@s+' From #T group by serialno'
exec(@s)
--结果
serialno Current Resistance Voltage
-------------------------------------------------------------------------------
LJ832785QA 2.9204810000000001 74.627480000000006 0.21794810000000001
--删除测试环境
Drop table #T
libin_ftsafe(子陌红尘) 于 2005-10-28 10:11:40
抱歉，看错了，以为是字符串求和，更正如下：
declare @s varchar(8000)
set @s = ''
select @s = @s+',['+parameter+']=sum(case parameter when '''+parameter+''' then value end)' from 表 group by parameter
set @s = 'select serialno'+@s+' from 表 group by serialno'
exec(@s)
qhqh310(qh) 于 2005-10-28 10:12:00
可不可以用游标和表变量啊？
libin_ftsafe(子陌红尘) 于 2005-10-28 10:13:39
如果parameter固定只有三个值，用静态SQL语句：
select
serialno,
Voltage = sum(case parameter when 'Voltage' then value end),
Current = sum(case parameter when 'Current' then value end),
Resistance = sum(case parameter when 'Resistance' then value end)
from
表
group by
serialno
vivianfdlpw 于 2005-10-28 10:14:12
create table A
(
serialno varchar(20),
parameter varchar(20),
value float
)
insert A
select 'LJ832785QA', 'Voltage', 0.2179481 union
select 'LJ832785QA', 'Current', 2.920481 union
select 'LJ832785QA', 'Resistance', 74.62748
--查询
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',max(case when parameter='''+parameter+''' then value else 0 end) as '''+parameter+''''
from A group by parameter
select @sql='select serialno'+@sql+' from A group by serialno'
exec(@sql)
--删除测试环境
drop table A
--结果
/*
serialno Current Resistance Voltage
-------------------- -----------------------------------------------------
LJ832785QA 2.9204810000000001 74.627480000000006 0.21794810000000001
*/
qhqh310(qh) 于 2005-10-28 10:35:00
create table mytable(serialno varchar(20),parameter varchar(20),value float)
insert into mytable select 'LJ832785QA','Voltage',0.2179481
union all select 'LJ832785QA','Current',2.920481
union all select 'LJ832785QA','Resistance',74.62748
select @sql=@sql+', '+convert(char(10),value)+' as '+'['+parameter+']' from mytable where serialno='LJ832785QA'
set @sql='select ''LJ832785QA'' as serialno'+@sql
--select @sql
exec( @sql)
drop table mytable
怎么就结了？？？？
------------------------------------------------------------------------------------------------------------------------
求一SQL语句，结果输出为每一种商品编号最高价格的前两个，谢谢帮忙~
问题如题所求，表格形式如下
商品编码 价格
01.016.01.01.01.00638450.0000000000
01.016.01.01.09.00134102.5600000000
01.053.01.02.01.07071200.0000000000
01.053.01.02.01.07078497.0900000000
01.053.01.02.01.07078497.1000000000
01.053.01.02.01.07191760.6800000000
01.053.01.02.01.07193750.0000000000
01.053.01.02.01.075224100.0000000000
01.053.01.02.01.07880499.1450000000
01.053.01.02.01.07885000.0000000000
01.053.01.02.01.079.0000000000
01.053.01.02.01.07973800.0000000000
01.053.01.02.02.021760388.0000000000
01.053.01.02.02.023204900.0000000000
01.053.01.02.02.023260683.7600000000
01.053.01.02.02.024204870.0000000000
01.053.01.02.02.029171000.0000000000
01.053.01.02.02.029196600.0000000000
01.053.01.02.02.03386200.0000000000
01.053.01.02.02.03393200.0000000000
01.053.01.02.02.037189600.0000000000
01.053.01.02.02.037199600.0000000000
01.053.01.02.02.037205570.0000000000
01.053.01.02.02.039215100.0000000000
01.053.01.02.02.039222100.0000000000
01.053.01.02.03.064160800.0000000000
01.053.01.02.03.066171000.0000000000
01.053.01.02.03.067188400.0000000000
01.053.01.02.03.067191490.0000000000
01.053.01.02.03.067196770.0000000000
01.053.01.02.03.067205570.0000000000
01.053.01.02.03.068196770.0000000000
01.053.01.02.03.068205570.0000000000
01.053.01.02.03.069241880.3400000000
01.053.01.02.03.104.0000000000
01.053.01.02.03.115263076.9250000000
01.053.01.02.03.116.0000000000
01.053.01.02.03.116263846.1500000000
01.053.01.02.04.05893700.0000000000
01.053.01.02.04.05993700.0000000000
01.053.01.02.04.059100700.0000000000
01.053.01.02.04.060116923.0800000000
01.053.01.02.04.062179700.0000000000
01.053.01.02.04.062186700.0000000000
01.053.01.02.04.068143750.0000000000
01.053.01.02.04.069143750.0000000000
01.053.01.02.04.072123076.9200000000
01.053.01.02.05.04060140.0000000000
01.053.01.02.05.04074910.0900000000
01.053.01.02.05.04186200.0000000000
01.053.01.02.05.04193200.0000000000
01.053.01.02.05.041103760.6800000000
01.053.01.02.05.041105811.9700000000
01.053.01.02.05.042106666.6700000000
01.053.01.02.05.047244786.0000000000
01.053.01.02.05.049166700.0000000000
01.076.01.01.02.002169914.5300000000
01.076.01.01.02.002170769.2300000000
01.250.01.02.01.00273760.0000000000
01.250.01.02.02.001117950.0000000000
02.164.01.01.03.001176666.6700000000
02.261.01.01.01.001512820.5100000000
zlp321002(飘过) 于 2005-10-27 10:58:30
--刚问过这个问题!
select * from 表 A
where 价格 in (select top 2 价格 from 表 where 商品编码=A.商品编码 order by 价格 DESC)
vivianfdlpw 于 2005-10-27 11:12:44
select * from 表 t
where 价格 in (select distinct top 2 价格
from 表
where 商品编码=t.商品编码
order by 价格 desc)
samfeng_2003(风云) 于 2005-10-27 11:15:56
呵呵！那么就只有支持了！:)
zouqiang122(先天不足) 于 2005-10-27 11:19:13
我也支持一下@@@
xyunsh(学海无涯,回头是岸) 于 2005-10-27 15:09:29
up
zsforever(虎虎) 于 2005-10-27 15:37:16
select distinct 商品编码,价格 from yourtable A
where (select count(1) from yourtable B where A.商品编码=B.商品编码 and B.价格>=A.价格) <= 2
说明：用 distinct 是为了避免有重复价格的出现，其它和一楼和二楼的的效果一样
skyrakehell(天涯浪子) 于 2005-10-27 18:03:22
我也使用了二楼兄弟的方法，万分感谢！
yfenfang(天上的小神) 于 2005-10-27 18:19:50
select *from 表名
huang2005(不懂) 于 2005-10-27 19:00:55
select max(价格) from able
where 商品编号=（select dictity *from 商品编号）
------------------------------------------------------------------------------------------------------------------------
简单语句写不出
有表A
有三字段
ID TITLE CLASS_ID
1 A 2
2 A 2
3 A 1
4 B 2
6 A 3
7 B 2
8 A 3
9 B 1
查询结果
CLASS_ID titlenum Anum Bnum
１ 2 1 1
２ 4 2 1
３ 2 1 1
写一SQL语句
实现按CLASS_ID分组 title记录数 A的个数 B的个数
AgilePicker(敏捷人生) 于 2005-10-27 17:43:55
select class_ID,count(title),(select count(title) from 表A where title='A'),
(select count(title) from 表A where title='B') from 表A group by class_ID
AgilePicker(敏捷人生) 于 2005-10-27 18:00:03
对不起，刚才看错了，呵呵，应该这样写
select class_ID,(select count(title) from 表A where class_ID=a.class_ID),(select count(title) from 表A where title='A' and class_ID=a.class_ID),
(select count(title) from 表A where title='B' and class_ID=a.class_ID) from 表A a group by class_ID
QQMagicer(在IT的路上越走越远) 于 2005-10-27 18:00:22
select class_ID,
count(title) as titlenum,
(select count(title) from 表A as a where a.class_ID =表A.class_ID and title='A') as Anum,
(select count(title) from 表A as a where a.class_ID =表A.class_ID and title='B') as Bnum
from 表A
group by class_ID
vivianfdlpw 于 2005-10-27 18:02:50
select CLASS_ID
,count(1) as 'titlenum'
,sum(case when TITLE='A' then 1 else 0 end) as 'Anum'
,sum(case when TITLE='B' then 1 else 0 end) as 'Bnum'
from A
group by CLASS_ID
order by CLASS_ID
singlepine(小山) 于 2005-10-27 18:03:50
select
CLASS_ID,
titlenum = sum(TITLE),
Anum = sum(case TITLE when 'A' then 1 else 0 end),
Bnum = sum(case TITLE when 'B' then 1 else 0 end)
from a group by CLASS_ID
------------------------------------------------------------------------------------------------------------------------
请帮忙写一个简单的存储过程，谢谢
这是一个无限级菜单的数据结构
id int
topic_id int
msg varchar(30)
topic_id对应同一个表中已经存在的ID，如果为0表示根节点
现在想要删除关联的数据，删除指定id纪录的同时要删除topic_id指向这个id的纪录，以及被删除的子节点的关联节点，即topic_id指向子节点的纪录，如此反复
传入的id可能是数字，也可能是形如“0,1,2,3”这样的字符串
谢谢
libin_ftsafe(子陌红尘) 于 2005-10-8 16:24:44
参考一下这个用户定义函数：
---------------------------------------------------------
--生成测试数据
create table BOM(ID VARCHAR(10),PID VARCHAR(10))
insert into BOM select 'a',NULL
insert into BOM select 'b','a'
insert into BOM select 'c','a'
insert into BOM select 'd','b'
insert into BOM select 'e','b'
insert into BOM select 'f','c'
insert into BOM select 'g','c'
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),PID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,PID,@i from BOM where PID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.PID,@i
from
BOM a,@t b
where
a.PID=b.ID and b.Level = @i-1
end
return
end
go
--执行查询
select * from dbo.f_getChild('a')
go
--输出结果
/*
b a 1
c a 1
d b 2
e b 2
f c 2
g c 2
*/
--删除测试数据
drop function f_getChild
drop table BOM
beyond_xiruo(CorruptionException) 于 2005-10-8 16:33:57
结构不同，差别不小，参考价值不大，还是谢谢
libin_ftsafe(子陌红尘) 于 2005-10-8 16:35:44
--生成测试数据
create table BOM(ID INT,Topic_ID INT,MSG VARCHAR(1000))
insert into BOM select 1,0,NULL
insert into BOM select 2,1,NULL
insert into BOM select 3,1,NULL
insert into BOM select 4,2,NULL
insert into BOM select 5,3,NULL
insert into BOM select 6,5,NULL
insert into BOM select 7,6,NULL
go
--创建用户定义函数
create function f_getChild(@ID VARCHAR(10))
returns @t table(ID VARCHAR(10),Topic_ID VARCHAR(10),Level INT)
as
begin
declare @i int,@ret varchar(8000)
set @i = 1
insert into @t select ID,Topic_ID,@i from BOM where ID = @ID
while @@rowcount<>0
begin
set @i = @i + 1
insert into @t
select
a.ID,a.Topic_ID,@i
from
BOM a,@t b
where
a.Topic_ID=b.ID and b.Level = @i-1
end
return
end
go
--创建用户定义存储过程
create procedure sp_relationDel(@var varchar(100))
as
begin
declare @ID varchar(100)
while charindex(',',@var)>0
begin
set @ID = left(@var,charindex(',',@var)-1)
set @var = stuff(@var,1,charindex(',',@var),'')
delete BOM where ID in(select ID from dbo.f_getChild(@ID))
end
delete BOM where ID in(select ID from dbo.f_getChild(@var))
end
go
--执行删除操作
exec sp_relationDel '3,5,6'
--查看执行结果
select * from BOM
/*
ID Topic_ID MSG
---- ---------- -----
1 0 NULL
2 1 NULL
4 2 NULL
*/
--删除测试数据
drop procedure sp_relationDel
drop function f_getChild
drop table BOM
vivianfdlpw 于 2005-10-8 16:49:51
create table tb(ID int,topic_id int)
insert into tb select 1,0
insert into tb select 2,1
insert into tb select 3,1
insert into tb select 4,2
insert into tb select 5,2
insert into tb select 6,3
insert into tb select 7,3
go
--创建用户定义函数
create function f_getChild(@ID int)
returns @tb table(ID int,topic_id int)
as
begin
insert @tb select ID,topic_id from tb where topic_id = @ID
while @@rowcount<>0
begin
insert @tb
select
a.ID,a.topic_id
from
tb a,@tb b
where
a.topic_id=b.ID
and
not exists(select 1 from @tb where ID=a.ID)
end
return
end
go
--创建存储过程
create procedure deleteNodes
@IDList varchar(1000) --要删除的id列表
as
declare @tb table
(
ID int,
num int identity
)
while charindex(',',@IDList)>0
begin
insert @tb(ID) select left(@IDList,charindex(',',@IDList)-1)
set @IDList=stuff(@IDList,1,charindex(',',@IDList),'')
end
insert @tb(ID) select @IDList
set xact_abort on
begin tran
declare @i int,@tmp int
set @i=1
while @i<=(select max(num) from @tb)
begin
select @tmp=ID from @tb where num=@i
delete from tb where exists(select 1 from f_getChild(@tmp) where ID=tb.ID)
set @i=@i+1
end
commit tran
go
--测试
exec deleteNodes '3'
/*
ID topic_id
----------- -----------
1 0
2 1
3 1
4 2
5 2
（所影响的行数为 5 行）
*/
exec deleteNodes '2,1'
/*
ID topic_id
----------- -----------
1 0
（所影响的行数为 1 行）
*/
--删除测试环境
drop function f_getChild
drop procedure deleteNodes
drop table tb
lsp69(浪子神剑) 于 2005-10-8 16:51:06
create proc DelMenu
@idi int
as
declare @id int
declare @topic varchar(10)
declare Del_Cursor cursor local for
select idi,topic_id from table1 where idi=@idi
open Del_Cursor
fetch next from Del_Cursor
into @id,@topicid
while @@fetch_status=0
begin
delete from table1 where idi=@id
delete from table1 where topic_id=@topic_id
fetch next from Del_Cursor
into @id,@topicid
end
close Del_Cursor
deallocate Del_Cursor
go
-- 或者二
Delect From Table1 Where ID = @ID Or Topic_ID = @ID
lsp69(浪子神剑) 于 2005-10-9 11:42:58
上面错了,这个应该能满足你的要求:
create table TableMenu(aID int,topic_id int)
--测试数据
insert into TableMenu select 1,0
insert into TableMenu select 2,1
insert into TableMenu select 3,1
insert into TableMenu select 4,2
insert into TableMenu select 5,2
insert into TableMenu select 6,3
insert into TableMenu select 7,4
insert into TableMenu select 8,3
insert into TableMenu select 9,7
go
create proc DelNode
@id int
as
declare @FilterID int
delete from TableMenu where aid=@id
if exists(select aid,topic_id from TableMenu where topic_id=@id)
begin
select @FilterID=aid from TableMenu where topic_id=@id
--递归
exec DelNode @FilterID
end
go
--测试执行
exec DelNode 4
--所影响的行数为:
--（所影响的行数为 1 行）
--（所影响的行数为 1 行）
--（所影响的行数为 1 行）
------------------------------------------------------------------------------------------------------------------------
大虾帮忙~~~
现有一表,记录如下形式
id logtime
1 2005-09-26 15:18:15.000
2 2005-09-26 15:18:15.000
3 2005-09-26 15:18:16.000
5 2005-09-26 15:18:19.000
12 2005-09-26 15:18:30.000
13 2005-09-26 15:18:31.000
14 2005-09-26 15:18:31.000
...
...
...
134000 2005-09-27 13:27:31.000
=====================
我想过滤掉前后记录相隔5分钟内的记录
就是说..
上一条记录与下一条记录的时间间隔必须大于5分钟,如果在5分钟内,就过滤掉..
求各位大虾帮帮忙
631799(杭州工人) 于 2005-10-8 10:43:57
up
vivianfdlpw 于 2005-10-8 10:45:24
比如id为3，5，12的记录，3和5的相隔3分钟，5和12相隔11分钟，id为5的记录是保留还是不保留呢？
rainxiang(努力赚钱买老婆 v1.0)(闹钟坏了) 于 2005-10-8 10:50:08
to vivianfdlpw()
这样id为5的记录就不保留了
churchatp1(早起的鸟儿有虫吃，早起的虫儿被鸟吃) 于 2005-10-8 10:59:18
如果你是希望插入的时候就过滤掉，那你可以在插入的时候就判断有没有时间和插入时间是5分钟内的，没有就插入，有就不插入。
如果你是希望插入完成后在过滤，那估计一条sql实现不了吧，写个函数可以实现。
libin_ftsafe(子陌红尘) 于 2005-10-8 11:01:11
select *,0 as flag into #t from 表 order by id
declare @logtime datetime
update #t
set
flag = case when @logtime is null or datediff(ss,@logtime,logtime)>300 then 1 else 0 end,
@logtime = case when @logtime is null or datediff(ss,@logtime,logtime)>300 then logtime else @logtime end
select * from #t where flag = 1 order by id
vivianfdlpw 于 2005-10-8 11:17:40
select * from 表 t
where (
exists(select 1
from 表
where id=(select max(id) from 表 where id<t.id)
and datediff(minute,logtime,t.logtime)>=5)
and
exists(select 1
from 表
where id=(select min(id) from 表 where id>t.id)
and datediff(minute,t.logtime,logtime)>=5)
)
or
(
t.id=(select max(id) from 表)
and exists(select 1
from 表
where id=(select max(id) from 表 where id<t.id)
and datediff(minute,logtime,t.logtime)>=5)
)
or
(
t.id=(select min(id) from 表)
and
exists(select 1
from 表
where id=(select min(id) from 表 where id>t.id)
and datediff(minute,t.logtime,logtime)>=5)
)
rainxiang(努力赚钱买老婆 v1.0)(闹钟坏了) 于 2005-10-8 13:38:23
to libin_ftsafe(子陌红尘) :
你的方法中好像 @logtime有问题
waterfirer(水清) 于 2005-10-8 15:22:07
vivianfdlpw的可能不行吧，按vivianfdlpw的例子，就会把id为3，5的记录都滤掉了。
用一条sql恐怕很难做。
rivery(river) 于 2005-10-8 15:26:37
--看楼主的数据很多，性能上可能差了点。暂时没有想到更好的方法。
--测试表及数据
declare @tb table(id int,logtime datetime)
insert into @tb
select 1, '2005-09-26 15:18:15.000' union all
select 2, '2005-09-26 15:20:15.000' union all
select 3, '2005-09-26 15:21:16.000' union all
select 5, '2005-09-26 15:26:19.000' union all
select 12, '2005-09-26 15:29:30.000' union all
select 13, '2005-09-26 15:35:31.000' union all
select 14, '2005-09-26 15:37:31.000'
--辅助表
select *,flag=0 into #
from @tb
--处理
declare @flag int,@dt1 datetime
update #
set @flag=case when @dt1 is null then 1 else (case when datediff(mi,@dt1,logtime)>5 then 1 else 0 end) end,
@dt1=case when @flag=1 then logtime else @dt1 end,
flag=@flag
--获取结果
select id,logtime from # where flag=1
--删除辅助表
drop table #
------------------------------------------------------------------------------------------------------------------------
一段看不懂的T—SQL。高手指点一下
--待分拆得字符串
declare @s varchar(1000)
set @s='1,23,a'
--按逗号进行分拆
declare @sql varchar(8000)
set @sql='select col='''
print @sql
set @sql=@sql + replace(@s,',',''' union all select ''')
print @sql
set @sql=@sql + ''''
print @sql
exec(@sql)
运行结果
1
23
a
rivery(river) 于 2005-10-12 14:06:00
1,23,a
->按照下面的格式组织，然后动态执行。
select '1' union all select '23' union all select 'a'
--在原来的语句运行完成后，请楼主到下面的窗口的消息窗口看看。
zyg0(影子(成功减肥10斤,可给我饿坏了,我要补回来) 于 2005-10-12 14:09:00
把 '1,23,a' 中的 , 替换成 union all select
select col='1,23,a' 编成了
select col=1
union all
select 23
union all
select a
burningfire_wqf(云中鹤) 于 2005-10-12 14:10:00
关键是看不明白那个union all select 是如何拆分字符串的
burningfire_wqf(云中鹤) 于 2005-10-12 14:11:00
也就是说是如何自动生成
select '1' union all select '23' union all select 'a'的
zyg0(影子(成功减肥10斤,可给我饿坏了,我要补回来) 于 2005-10-12 14:17:00
他实际上采用的是拼字符串的方法
------------------------------------------------------------------------------------------------------------------------
查询表返回值用一个字符串表示所有记录
如何在视图中的一个字段记录对应子表的记录（该字段是另一个视图返回值，该返回值用一个字符串表示所有记录）
简单来说如何把 Select c1 From t1的返回值用一个字符串表示。不用存储过程
lizhaogui 于 2005-10-12 14:28:18
帮顶！！
libin_ftsafe(子陌红尘) 于 2005-10-12 14:33:11
--假设两个视图v1和t1通过字段ID关联，用户定义函数UDF：
create function f_str(@ID varchar(100))
returns varchar(8000)
as
begin
declare @ret varchar(8000)
select @ret = isnull(@ret,'')+','+rtrim(c1) from t1 where ID = @ID
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
select *,dbo.f_str(ID) from v1
readersm68(地主) 于 2005-10-12 14:36:38
不用存储过程，用游标我会的
readersm68(地主) 于 2005-10-12 14:37:41
啊，我看错了，用函数
readersm68(地主) 于 2005-10-12 14:40:15
我好像没有说明白，不是一条记录的字段值合并，是多条记录，嗯在函数中调用游标就可以了。
然后在视图中调用，视图可以调用函数吗？
谢谢！libin_ftsafe(子陌红尘)
libin_ftsafe(子陌红尘) 于 2005-10-12 15:12:24
视图可以调用函数吗？
---------------------------------------------------------------------
当然可以。
另外，不需要用游标，UDF足够了，有下例为证：
--生成测试数据
create table 表(部门 int,人员 varchar(20))
insert into 表 select 1,'张三'
insert into 表 select 1,'李四'
insert into 表 select 1,'王五'
insert into 表 select 2,'赵六'
insert into 表 select 2,'邓七'
insert into 表 select 2,'刘八'
go
--创建用户定义函数
create function f_str(@department int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret+','+人员 from 表 where 部门 = @department
set @ret = stuff(@ret,1,1,'')
return @ret
end
go
--执行
select 部门,人员=dbo.f_str(部门) from 表 group by 部门 order by 部门
go
--输出结果
/*
部门 人员
---- --------------
1 张三,李四,王五
2 赵六,邓七,刘八
*/
--删除测试数据
drop function f_str
drop table 表
go
readersm68(地主) 于 2005-10-12 15:16:53
OK
谢谢！
还问一个小问题
如何把时间类型转换为varchar显示的格式为 2005-10-12 而不是Oct 12 2005
------------------------------------------------------------------------------------------------------------------------
数据库日志文件超级的大，如何在不停止数据库服务的情况下。截断日志文件。
我的客户的数据库无比的大，检查发现原因在于日志文件非常的大。我想把它弄小一点，但由于客户一直在使用数据库，所以服务不能停止。那我就不知道该怎处理了。请高手指点一下。
insert2003(高级打字员) 于 2005-10-12 11:42:24
最简单的：
建个作业
每天运行一次
BACKUP LOG table_name WITH NO_LOG
DBCC SHRINKDATABASE(table_name)
详细的：
压缩日志及数据库文件大小
/*--特别注意
请按步骤进行,未进行前面的步骤,请不要做后面的步骤
否则可能损坏你的数据库.
一般不建议做第4,6两步
第4步不安全,有可能损坏数据库或丢失数据
第6步如果日志达到上限,则以后的数据库处理会失败,在清理日志后才能恢复.
--*/
--下面的所有库名都指你要处理的数据库的库名
1.清空日志
DUMP TRANSACTION 库名 WITH NO_LOG
2.截断事务日志：
BACKUP LOG 库名 WITH NO_LOG
3.收缩数据库文件(如果不压缩,数据库的文件不会减小
企业管理器--右键你要压缩的数据库--所有任务--收缩数据库--收缩文件
--选择日志文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
--选择数据文件--在收缩方式里选择收缩至XXM,这里会给出一个允许收缩到的最小M数,直接输入这个数,确定就可以了
也可以用SQL语句来完成
--收缩数据库
DBCC SHRINKDATABASE(库名)
--收缩指定数据文件,1是文件号,可以通过这个语句查询到:select * from sysfiles
DBCC SHRINKFILE(1)
4.为了最大化的缩小日志文件(如果是sql 7.0,这步只能在查询分析器中进行)
a.分离数据库:
企业管理器--服务器--数据库--右键--分离数据库
b.在我的电脑中删除LOG文件
c.附加数据库:
企业管理器--服务器--数据库--右键--附加数据库
此法将生成新的LOG，大小只有500多K
或用代码：
下面的示例分离 pubs，然后将 pubs 中的一个文件附加到当前服务器。
a.分离
EXEC sp_detach_db @dbname = '库名'
b.删除日志文件
c.再附加
EXEC sp_attach_single_file_db @dbname = '库名',
@physname = 'c:/Program Files/Microsoft SQL Server/MSSQL/Data/库名.mdf'
5.为了以后能自动收缩,做如下设置:
企业管理器--服务器--右键数据库--属性--选项--选择"自动收缩"
--SQL语句设置方式:
EXEC sp_dboption '库名', 'autoshrink', 'TRUE'
6.如果想以后不让它日志增长得太大
企业管理器--服务器--右键数据库--属性--事务日志
--将文件增长限制为xM(x是你允许的最大数据文件大小)
--SQL语句的设置方式:
alter database 库名 modify file(name=逻辑文件名,maxsize=20)
联机帮助有说的！
Leftie(左手,为人民币服务) 于 2005-10-12 11:43:03
DUMP TRANSACTION 数据库名 WITH NO_LOG
wgsasd311(自强不息) 于 2005-10-12 11:51:12
1.首先清空日志
DUMP TRANSACTION 库名 WITH NO_LOG
2.最后压缩数据库就可以了
DBCC SHRINKDATABASE(库名)
cary2006(西葫芦◆www.DBpub.net◆) 于 2005-10-12 14:31:10
1、首先以下情况下，数据库日志的非活动部分才会被截断：
简单恢复模式，设置在检查点截断日志；简单恢复模式，备份数据库；back up log db_name with no_log
2、日志被截断后只是让出了逻辑空间，如果日志文件没有设置成自动收缩则要通过以下方式收缩：
DBCC SHRINKDATABASE或DBCC SHRINKFILE
------------------------------------------------------------------------------------------------------------------------
如何得到这样一个查询结果 请大家帮忙。
数据记录
编号 金额 时间
20051001 500 2005-10-20
20051002 100 2005-7-25
20050502 150 2005-8-26
得到结查如下：
编号 金额 7月 8月 10月
20051001 500 500
20051002 100 100
20050502 150 150
根据时间将金额显示在相应的月份。
wgsasd311(自强不息) 于 2005-10-12 20:57:08
select 编号,金额,
[7月]=case datename(mm,时间) when 7 then 金额 end,
[8月]=case datename(mm,时间) when 8 then 金额 end,
[10月]=case datename(mm,时间) when 10 then 金额 end
from tb
------------------------------------------------------------------------------------------------------------------------
求一条sql语句,实在是急!
表的内容如下:
姓名,金额
张三 200
张三 30
张三 20
张三 50
张三 100
李四 40
李四 10
李四 50
.......
我要通过一条sql语句显示为
姓名,金额
张三 400
李四 100
也就是说把每个人的金额累加
libin_ftsafe(子陌红尘) 于 2005-10-12 16:48:23
select 姓名,金额=sum(金额) from 表 group by 姓名
churchatp1(早起的鸟儿有虫吃，早起的虫儿被鸟吃) 于 2005-10-12 16:50:14
select 姓名,金额=sum(金额) from 表 group by 姓名
lizhaogui 于 2005-10-12 16:57:52
楼上说了。
singlepine(小山) 于 2005-10-12 17:04:48
select 姓名,sum(金额) as 金额 from table1 group by 姓名
bugchen888(臭虫) 于 2005-10-12 17:43:31
select 姓名,金额=sum(金额) from 表 group by 姓名
sirious(xLzing) 于 2005-10-12 17:48:16
select 姓名,金额=sum(金额) from 表 group by 姓名
scmail81(freedom) 于 2005-10-12 17:52:15
select 姓名,sum(金额) as 金额 from table1 group by 姓名
wgsasd311(自强不息) 于 2005-10-12 18:09:07
select 姓名,金额=sum(金额) from 表
group by 姓名
order by 姓名
wushimiang12 于 2005-10-12 18:18:31
select 姓名,sum(金额) as 金额
from tablename
group by 姓名
------------------------------------------------------------------------------------------------------------------------
在那个地方我可以看到一条select语句执行以后花费的时间？谢谢！
在查询分析器右下角那个一般都是0：00：0（数据量少的时候）
我不知道人家那些几百几千毫秒都在在那里看出来的？
谢谢！
vivianfdlpw 于 2005-9-28 0:20:27
SET STATISTICS TIME
显示分析、编译和执行各语句所需的毫秒数。
语法
SET STATISTICS TIME { ON | OFF }
注释
当 SET STATISTICS TIME 为 ON 时，显示语句的时间统计。为 OFF 时，不显示时间统计。
SET STATISTICS TIME 的设置是在执行或运行时设置，而不是在分析时设置。
Microsoft® SQL Server™ 不能在纤程模式下提供准确的统计，而纤程模式在启用 lightweight pooling 配置选项时激活。
只有当使用 SET STATISTICS TIME ON 执行查询时才更新 sysprocesses 表中的 cpu 列。当 SET STATISTICS TIME 为 OFF 时，将返回 0。
ON 和 OFF 设置也影响 SQL Server 企业管理器内的"当前活动的进程信息视图"中的 CPU 列。
权限
SET STATISTICS TIME 权限默认授予所有用户。
yangfanchuhai(同志们，努力啊) 于 2005-9-28 4:16:34
SET STATISTICS IO ON
SET STATISTICS TIME ON
select * from T
上面三句一起执行信息会更详细,其中第三句改成你自己的
------------------------------------------------------------------------------------------------------------------------
浅谈数据库设计技巧
TheEdge推荐 [2007-6-4]
出处：中国IT实验室
作者：keda
说到数据库，我认为不能不先谈数据结构。1996年，在我初入大学学习计算机编程时，当时的老师就告诉我们说：计算机程序＝数据结构＋算法。尽管现在的程序开发已由面向过程为主逐步过渡到面向对象为主，但我还是深深赞同8年前老师的告诉我们的公式：计算机程序＝数据结构＋算法。面向对象的程序开发，要做的第一件事就是，先分析整个程序中需处理的数据，从中提取出抽象模板，以这个抽象模板设计类，再在其中逐步添加处理其数据的函数(即算法)，最后，再给类中的数据成员和函数划分访问权限，从而实现封装。
数据库的最初雏形据说源自美国一个奶牛场的记账薄(纸质的，由此可见，数据库并不一定是存储在电脑里的数据^_^)，里面记录的是该奶牛场的收支账目，程序员在将其整理、录入到电脑中时从中受到启发。当按照规定好的数据结构所采集到的数据量大到一定程度后，出于程序执行效率的考虑，程序员将其中的检索、更新维护等功能分离出来，做成单独调用的模块，这个模块后来就慢慢发展、演变成现在我们所接触到的数据库管理系统(DBMS)——程序开发中的一个重要分支。
下面进入正题，首先按我个人所接触过的程序给数据库设计人员的功底分一下类：
１、没有系统学习过数据结构的程序员。这类程序员的作品往往只是他们的即兴玩具，他们往往习惯只设计有限的几个表，实现某类功能的数据全部塞在一个表中，各表之间几乎毫无关联。网上不少的免费管理软件都是这样的东西，当程序功能有限，数据量不多的时候，其程序运行起来没有什么问题，但是如果用其管理比较重要的数据，风险性非常大。
２、系统学习过数据结构，但是还没有开发过对程序效率要求比较高的管理软件的程序员。这类人多半刚从学校毕业不久，他们在设计数据库表结构时，严格按照教科书上的规定，死扣E-R图和3NF(别灰心，所有的数据库设计高手都是从这一步开始的)。他们的作品，对于一般的access型轻量级的管理软件，已经够用。但是一旦该系统需要添加新功能，原有的数据库表差不多得进行大换血。
３、第二类程序员，在经历过数次程序效率的提升，以及功能升级的折腾后，终于升级成为数据库设计的老鸟，第一类程序员眼中的高人。这类程序员可以胜任二十个表以上的中型商业数据管理系统的开发工作。他们知道该在什么样的情况下保留一定的冗余数据来提高程序效率，而且其设计的数据库可拓展性较好，当用户需要添加新功能时，原有数据库表只需做少量修改即可。
４、在经历过上十个类似数据库管理软件的重复设计后，第三类程序员中坚持下来没有转行，而是希望从中找出“偷懒”窍门的有心人会慢慢觉悟，从而完成量变到质变的转换。他们所设计的数据库表结构有一定的远见，能够预测到未来功能升级所需要的数据，从而预先留下伏笔。这类程序员目前大多晋级成数据挖掘方面的高级软件开发人员。
５、第三类程序员或第四类程序员，在对现有的各家数据库管理系统的原理和开发都有一定的钻研后，要么在其基础上进行二次开发，要么自行开发一套有自主版权的通用数据库管理系统。
我个人正处于第三类的末期，所以下面所列出的一些设计技巧只适合第二类和部分第三类数据库设计人员。同时，由于我很少碰到有兴趣在这方面深钻下去的同行，所以文中难免出现错误和遗漏，在此先行声明，欢迎大家指正，不要藏私哦8)
一、树型关系的数据表
不少程序员在进行数据库设计的时候都遇到过树型关系的数据，例如常见的类别表，即一个大类，下面有若干个子类，某些子类又有子类这样的情况。当类别不确定，用户希望可以在任意类别下添加新的子类，或者删除某个类别和其下的所有子类，而且预计以后其数量会逐步增长，此时我们就会考虑用一个数据表来保存这些数据。按照教科书上的教导，第二类程序员大概会设计出类似这样的数据表结构：
类别表_1(Type_table_1)
名称 类型 约束条件 说明
type_id int 无重复 类别标识，主键
type_name char(50) 不允许为空 类型名称，不允许重复
type_father int 不允许为空 该类别的父类别标识，如果是顶节点的话设定为某个唯一值
这样的设计短小精悍，完全满足3NF，而且可以满足用户的所有要求。是不是这样就行呢？答案是NO！Why？
我们来估计一下用户希望如何罗列出这个表的数据的。对用户而言，他当然期望按他所设定的层次关系一次罗列出所有的类别，例如这样：
总类别
类别1
类别1.1
类别1.1.1
类别1.2
类别2
类别2.1
类别3
类别3.1
类别3.2
……
看看为了实现这样的列表显示(树的先序遍历)，要对上面的表进行多少次检索？注意，尽管类别1.1.1可能是在类别3.2之后添加的记录，答案仍然是N次。这样的效率对于少量的数据没什么影响，但是日后类型扩充到数十条甚至上百条记录后，单单列一次类型就要检索数十次该表，整个程序的运行效率就不敢恭维了。或许第二类程序员会说，那我再建一个临时数组或临时表，专门保存类型表的先序遍历结果，这样只在第一次运行时检索数十次，再次罗列所有的类型关系时就直接读那个临时数组或临时表就行了。其实，用不着再去分配一块新的内存来保存这些数据，只要对数据表进行一定的扩充，再对添加类型的数量进行一下约束就行了，要完成上面的列表只需一次检索就行了。下面是扩充后的数据表结构：
类别表_2(Type_table_2)
名称 类型 约束条件 说明
type_id int 无重复 类别标识，主键
type_name char(50) 不允许为空 类型名称，不允许重复
type_father int 不允许为空 该类别的父类别标识，如果是顶节点的话设定为某个唯一值
type_layer char(6) 限定3层,初始值为000000 类别的先序遍历，主要为减少检索数据库的次数
按照这样的表结构，我们来看看上面例子记录在表中的数据是怎样的：
type_id type_name type_father type_layer
1 总类别 0 000000
2 类别1 1 010000
3 类别1.1 2 010100
4 类别1.2 2 010200
5 类别2 1 020000
6 类别2.1 5 020100
7 类别3 1 030000
8 类别3.1 7 030100
9 类别3.2 7 030200
10 类别1.1.1 3 010101
……
现在按type_layer的大小来检索一下：SELECT * FROM Type_table_2 ORDER BY type_layer
列出记录集如下：
type_id type_name type_father type_layer
1 总类别 0 000000
2 类别1 1 010000
3 类别1.1 2 010100
10 类别1.1.1 3 010101
4 类别1.2 2 010200
5 类别2 1 020000
6 类别2.1 5 020100
7 类别3 1 030000
8 类别3.1 7 030100
9 类别3.2 7 030200
……
现在列出的记录顺序正好是先序遍历的结果。在控制显示类别的层次时，只要对type_layer字段中的数值进行判断，每2位一组，如大于0则向右移2个空格。当然，我这个例子中设定的限制条件是最多3层，每层最多可设99个子类别，只要按用户的需求情况修改一下type_layer的长度和位数，即可更改限制层数和子类别数。其实，上面的设计不单单只在类别表中用到，网上某些可按树型列表显示的论坛程序大多采用类似的设计。
或许有人认为，Type_table_2中的type_father字段是冗余数据，可以除去。如果这样，在插入、删除某个类别的时候，就得对type_layer 的内容进行比较繁琐的判定，所以我并没有消去type_father字段，这也正符合数据库设计中适当保留冗余数据的来降低程序复杂度的原则，后面我会举一个故意增加数据冗余的案例。
二、商品信息表的设计
假设你是一家百货公司电脑部的开发人员，某天老板要求你为公司开发一套网上电子商务平台，该百货公司有数千种商品出售，不过目前仅打算先在网上销售数十种方便运输的商品，当然，以后可能会陆续在该电子商务平台上增加新的商品出售。现在开始进行该平台数据库的商品信息表的设计。每种出售的商品都会有相同的属性，如商品编号，商品名称，商品所属类别，相关信息，供货厂商，内含件数，库存，进货价，销售价，优惠价。你很快就设计出4个表：商品类型表(Wares_type)，供货厂商表(Wares_provider)，商品信息表(Wares_info)：
商品类型表(Wares_type)
名称 类型 约束条件 说明
type_id int 无重复 类别标识，主键
type_name char(50) 不允许为空 类型名称，不允许重复
type_father int 不允许为空 该类别的父类别标识，如果是顶节点的话设定为某个唯一值
type_layer char(6) 限定3层,初始值为000000 类别的先序遍历，主要为减少检索数据库的次数
供货厂商表(Wares_provider)
名称 类型 约束条件 说明
provider_id int 无重复 供货商标识，主键
provider_name char(100) 不允许为空 供货商名称
商品信息表(Wares_info)
名称 类型 约束条件 说明
wares_id int 无重复 商品标识，主键
wares_name char(100) 不允许为空 商品名称
wares_type int 不允许为空 商品类型标识，和Wares_type.type_id关联
wares_info char(200) 允许为空 相关信息
provider int 不允许为空 供货厂商标识，和Wares_provider.provider_id关联
setnum int 初始值为1 内含件数，默认为1
stock int 初始值为0 库存，默认为0
buy_price money 不允许为空 进货价
sell_price money 不允许为空 销售价
discount money 不允许为空 优惠价
你拿着这3个表给老板检查，老板希望能够再添加一个商品图片的字段，不过只有一部分商品有图片。OK，你在商品信息表(Wares_info)中增加了一个haspic的BOOL型字段，然后再建了一个新表——商品图片表(Wares_pic)：
商品图片表(Wares_pic)
名称 类型 约束条件 说明
pic_id int 无重复 商品图片标识，主键
wares_id int 不允许为空 所属商品标识，和Wares_info.wares_id关联
pic_address char(200) 不允许为空 图片存放路径
程序开发完成后，完全满足老板目前的要求，于是正式启用。一段时间后，老板打算在这套平台上推出新的商品销售，其中，某类商品全部都需添加“长度”的属性。第一轮折腾来了……当然，你按照添加商品图片表的老方法，在商品信息表(Wares_info)中增加了一个haslength的BOOL型字段，又建了一个新表——商品长度表(Wares_length)：
商品长度表(Wares_length)
名称 类型 约束条件 说明
length_id int 无重复 商品图片标识，主键
wares_id int 不允许为空 所属商品标识，和Wares_info.wares_id关联
length char(20) 不允许为空 商品长度说明
刚刚改完没多久，老板又打算上一批新的商品，这次某类商品全部需要添加“宽度”的属性。你咬了咬牙，又照方抓药，添加了商品宽度表(Wares_width)。又过了一段时间，老板新上的商品中有一些需要添加“高度”的属性，你是不是开始觉得你所设计的数据库按照这种方式增长下去，很快就能变成一个迷宫呢？那么，有没有什么办法遏制这种不可预见性，但却类似重复的数据库膨胀呢？我在阅读《敏捷软件开发：原则、模式与实践》中发现作者举过类似的例子：7.3 “Copy”程序。其中，我非常赞同敏捷软件开发这个观点：在最初几乎不进行预先设计，但是一旦需求发生变化，此时作为一名追求卓越的程序员，应该从头审查整个架构设计，在此次修改中设计出能够满足日后类似修改的系统架构。下面是我在需要添加“长度”的属性时所提供的修改方案：
去掉商品信息表(Wares_info)中的haspic字段，添加商品额外属性表(Wares_ex_property)和商品额外信息表(Wares_ex_info)2个表来完成添加新属性的功能。
商品额外属性表(Wares_ex_property)
名称 类型 约束条件 说明
ex_pid int 无重复 商品额外属性标识，主键
p_name char(20) 不允许为空 额外属性名称
商品额外信息表(Wares_ex_info)
名称 类型 约束条件 说明
ex_iid int 无重复 商品额外信息标识，主键
wares_id int 不允许为空 所属商品标识，和Wares_info.wares_id关联
property_id int 不允许为空 商品额外属性标识，和Wares_ex_property.ex_pid关联
property_value char(200) 不允许为空 商品额外属性值
在商品额外属性表(Wares_ex_property)中添加2条记录：
ex_pid p_name
1 商品图片
2 商品长度
再在整个电子商务平台的后台管理功能中追加一项商品额外属性管理的功能，以后添加新的商品时出现新的属性，只需利用该功能往商品额外属性表(Wares_ex_property)中添加一条记录即可。不要害怕变化，被第一颗子弹击中并不是坏事，坏的是被相同轨道飞来的第二颗、第三颗子弹击中。第一颗子弹来得越早，所受的伤越重，之后的抵抗力也越强8。
三、多用户及其权限管理的设计
开发数据库管理类的软件，不可能不考虑多用户和用户权限设置的问题。尽管目前市面上的大、中型的后台数据库系统软件都提供了多用户，以及细至某个数据库内某张表的权限设置的功能，我个人建议：一套成熟的数据库管理软件，还是应该自行设计用户管理这块功能，原因有二：
1.那些大、中型后台数据库系统软件所提供的多用户及其权限设置都是针对数据库的共有属性，并不一定能完全满足某些特例的需求；
2.不要过多的依赖后台数据库系统软件的某些特殊功能，多种大、中型后台数据库系统软件之间并不完全兼容。否则一旦日后需要转换数据库平台或后台数据库系统软件版本升级，之前的架构设计很可能无法重用。
下面看看如何自行设计一套比较灵活的多用户管理模块，即该数据库管理软件的系统管理员可以自行添加新用户，修改已有用户的权限，删除已有用户。首先，分析用户需求，列出该数据库管理软件所有需要实现的功能；然后，根据一定的联系对这些功能进行分类，即把某类用户需使用的功能归为一类；最后开始建表：
功能表(Function_table)
名称 类型 约束条件 说明
f_id int 无重复 功能标识，主键
f_name char(20) 不允许为空 功能名称，不允许重复
f_desc char(50) 允许为空 功能描述
用户组表(User_group)
名称 类型 约束条件 说明
group_id int 无重复 用户组标识，主键
group_name char(20) 不允许为空 用户组名称
group_power char(100) 不允许为空 用户组权限表，内容为功能表f_id的集合
用户表(User_table)
名称 类型 约束条件 说明
user_id int 无重复 用户标识，主键
user_name char(20) 无重复 用户名
user_pwd char(20) 不允许为空 用户密码
user_type int 不允许为空 所属用户组标识，和User_group.group_id关联
采用这种用户组的架构设计，当需要添加新用户时，只需指定新用户所属的用户组；当以后系统需要添加新功能或对旧有功能权限进行修改时，只用操作功能表和用户组表的记录，原有用户的功能即可相应随之变化。当然，这种架构设计把数据库管理软件的功能判定移到了前台，使得前台开发相对复杂一些。但是，当用户数较大(10人以上)，或日后软件升级的概率较大时，这个代价是值得的。
四、简洁的批量m:n设计
碰到m:n的关系，一般都是建立3个表，m一个，n一个，m:n一个。但是，m:n有时会遇到批量处理的情况，例如到图书馆借书，一般都是允许用户同时借阅n本书，如果要求按批查询借阅记录，即列出某个用户某次借阅的所有书籍，该如何设计呢？让我们建好必须的3个表先：
书籍表(Book_table)
名称 类型 约束条件 说明
book_id int 无重复 书籍标识，主键
book_no char(20) 无重复 书籍编号
book_name char(100) 不允许为空 书籍名称
……
借阅用户表(Renter_table)
名称 类型 约束条件 说明
renter_id int 无重复 用户标识，主键
renter_name char(20) 不允许为空 用户姓名
……
借阅记录表(Rent_log)
名称 类型 约束条件 说明
rent_id int 无重复 借阅记录标识，主键
r_id int 不允许为空 用户标识，和Renter_table.renter_id关联
b_id int 不允许为空 书籍标识，和Book_table.book_id关联
rent_date datetime 不允许为空 借阅时间
……
为了实现按批查询借阅记录，我们可以再建一个表来保存批量借阅的信息，例如：
批量借阅表(Batch_rent)
名称 类型 约束条件 说明
batch_id int 无重复 批量借阅标识，主键
batch_no int 不允许为空 批量借阅编号，同一批借阅的batch_no相同
rent_id int 不允许为空 借阅记录标识，和Rent_log.rent_id关联
batch_date datetime 不允许为空 批量借阅时间
这样的设计好吗？我们来看看为了列出某个用户某次借阅的所有书籍，需要如何查询？首先检索批量借阅表(Batch_rent)，把符合条件的的所有记录的rent_id字段的数据保存起来，再用这些数据作为查询条件带入到借阅记录表(Rent_log)中去查询。那么，有没有什么办法改进呢？下面给出一种简洁的批量设计方案，不需添加新表，只需修改一下借阅记录表(Rent_log)即可。修改后的记录表(Rent_log)如下：
借阅记录表(Rent_log)
名称 类型 约束条件 说明
rent_id int 无重复 借阅记录标识，主键
r_id int 不允许为空 用户标识，和Renter_table.renter_id关联
b_id int 不允许为空 书籍标识，和Book_table.book_id关联
batch_no int 不允许为空 批量借阅编号，同一批借阅的batch_no相同
rent_date datetime 不允许为空 借阅时间
……
其中，同一次借阅的batch_no和该批第一条入库的rent_id相同。举例：假设当前最大rent_id是64，接着某用户一次借阅了3本书，则批量插入的3条借阅记录的batch_no都是65。之后另外一个用户租了一套碟，再插入出租记录的rent_id是68。采用这种设计，查询批量借阅的信息时，只需使用一条标准T_SQL的嵌套查询即可。当然，这种设计不符合3NF，但是和上面标准的3NF设计比起来，哪一种更好呢？答案就不用我说了吧。
五、冗余数据的取舍
上篇的“树型关系的数据表”中保留了一个冗余字段，这里的例子更进一步——添加了一个冗余表。先看看例子：我原先所在的公司为了解决员工的工作餐，和附近的一家小餐馆联系，每天吃饭记账，费用按人数平摊，月底由公司现金结算，每个人每个月的工作餐费从工资中扣除。当然，每天吃饭的人员和人数都不是固定的，而且，由于每顿工作餐的所点的菜色不同，每顿的花费也不相同。例如，星期一中餐5人花费40元，晚餐2人花费20，星期二中餐6人花费36元，晚餐3人花费18元。为了方便计算每个人每个月的工作餐费，我写了一个简陋的就餐记账管理程序，数据库里有3个表：
员工表(Clerk_table)
名称 类型 约束条件 说明
clerk_id int 无重复 员工标识，主键
clerk_name char(10) 不允许为空 员工姓名
每餐总表(Eatdata1)
名称 类型 约束条件 说明
totle_id int 无重复 每餐总表标识，主键
persons char(100) 不允许为空 就餐员工的员工标识集合
eat_date datetime 不允许为空 就餐日期
eat_type char(1) 不允许为空 就餐类型，用来区分中、晚餐
totle_price money 不允许为空 每餐总花费
persons_num int 不允许为空 就餐人数
就餐计费细表(Eatdata2)
名称 类型 约束条件 说明
id int 无重复 就餐计费细表标识，主键
t_id int 不允许为空 每餐总表标识，和Eatdata1.totle_id关联
c_id int 不允许为空 员工标识标识，和Clerk_table.clerk_id关联
price money 不允许为空 每人每餐花费
其中，就餐计费细表(Eatdata2)的记录就是把每餐总表(Eatdata1)的一条记录按就餐员工平摊拆开，是个不折不扣的冗余表。当然，也可以把每餐总表(Eatdata1)的部分字段合并到就餐计费细表(Eatdata2)中，这样每餐总表(Eatdata1)就成了冗余表，不过这样所设计出来的就餐计费细表重复数据更多，相比来说还是上面的方案好些。但是，就是就餐计费细表(Eatdata2)这个冗余表，在做每月每人餐费统计的时候，大大简化了编程的复杂度，只用类似这么一条查询语句即可统计出每人每月的寄餐次数和餐费总帐：
SELECT clerk_name AS personname,COUNT(c_id) as eattimes,SUM(price) AS ptprice FROM Eatdata2 JOIN Clerk_tabsle ON (c_id=clerk_id) JOIN eatdata1 ON (totleid=tid) WHERE eat_date>=CONVERT(datetime,'"&the_date&"') AND eat_date<DATEADD(month,1,CONVERT(datetime,'"&the_date&"')) GROUP BY c_id
想象一下，如果不用这个冗余表，每次统计每人每月的餐费总帐时会多麻烦，程序效率也够呛。那么，到底什么时候可以增加一定的冗余数据呢？我认为有2个原则：
１、用户的整体需求。当用户更多的关注于，对数据库的规范记录按一定的算法进行处理后，再列出的数据。如果该算法可以直接利用后台数据库系统的内嵌函数来完成，此时可以适当的增加冗余字段，甚至冗余表来保存这些经过算法处理后的数据。要知道，对于大批量数据的查询，修改或删除，后台数据库系统的效率远远高于我们自己编写的代码。
２、简化开发的复杂度。现代软件开发，实现同样的功能，方法有很多。尽管不必要求程序员精通绝大部分的开发工具和平台，但是还是需要了解哪种方法搭配哪种开发工具的程序更简洁，效率更高一些。冗余数据的本质就是用空间换时间，尤其是目前硬件的发展远远高于软件，所以适当的冗余是可以接受的。不过我还是在最后再强调一下：不要过多的依赖平台和开发工具的特性来简化开发，这个度要是没把握好的话，后期维护升级会栽大跟头的。
------------------------------------------------------------------------------------------------------------------------
SQL SERVER存储过程解密方法
TheEdge推荐 [2007-6-4]
出处：CSDN
作者：j9988
create PROCEDURE sp_decrypt(@objectName varchar(50))
AS
begin
set nocount on
--CSDN：j9988 copyright:2004.01.05
--V3.1
--破解字节不受限制，适用于SQLSERVER2000存储过程，函数，视图，触发器
--发现有错，请E_MAIL：CSDNj9988@tom.com
begin tran
declare @objectname1 varchar(100),@orgvarbin varbinary(8000)
declare @sql1 nvarchar(4000),@sql2 varchar(8000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)
DECLARE @OrigSpText1 nvarchar(4000), @OrigSpText2 nvarchar(4000) , @OrigSpText3 nvarchar(4000), @resultsp nvarchar(4000)
declare @i int,@status int,@type varchar(10),@parentid int
declare @colid int,@n int,@q int,@j int,@k int,@encrypted int,@number int
select @type=xtype,@parentid=parent_obj from sysobjects where id=object_id(@ObjectName)
create table #temp(number int,colid int,ctext varbinary(8000),encrypted int,status int)
insert #temp SELECT number,colid,ctext,encrypted,status FROM syscomments WHERE id = object_id(@objectName)
select @number=max(number) from #temp
set @k=0
while @k<=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
if @type='P'
set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '
end)
if @type='TR'
set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
if @type='FN' or @type='TF' or @type='IF'
set @sql1=(case @type when 'TF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)
if @type='V'
set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'
set @q=len(@sql1)
set @sql1=@sql1+REPLICATE('-',4000-@q)
select @sql2=REPLICATE('-',8000)
set @sql3='exec(@sql1'
select @colid=max(colid) from #temp where number=@k
set @n=1
while @n<=CEILING(1.0*(@colid-1)/2) and len(@sQL3)<=3996
begin
set @sql3=@sql3+'+@'
set @n=@n+1
end
set @sql3=@sql3+')'
exec sp_executesql @sql3,N'@Sql1 nvarchar(4000),@ varchar(8000)',@sql1=@sql1,@=@sql2
end
set @k=@k+1
end
set @k=0
while @k<=@number
begin
if exists(select 1 from syscomments where id=object_id(@objectname) and number=@k)
begin
select @colid=max(colid) from #temp where number=@k
set @n=1
while @n<=@colid
begin
select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp WHERE colid=@n and number=@k
SET @OrigSpText3=(SELECT ctext FROM syscomments WHERE id=object_id(@objectName) and colid=@n and number=@k)
if @n=1
begin
if @type='P'
SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '
else 'CREATE PROCEDURE '+ @objectName +' WITH ENCRYPTION AS '
end)
if @type='FN' or @type='TF' or @type='IF'--刚才有错改一下
SET @OrigSpText2=(case @type when 'TF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '
when 'FN' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'
when 'IF' then
'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'
end)
if @type='TR'
set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '
if @type='V'
set @OrigSpText2='CREATE VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'
set @q=4000-len(@OrigSpText2)
set @OrigSpText2=@OrigSpText2+REPLICATE('-',@q)
end
else
begin
SET @OrigSpText2=REPLICATE('-', 4000)
end
SET @i=1
SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))
WHILE @i<=datalength(@OrigSpText1)/2
BEGIN
SET @resultsp = stuff(@resultsp, @i, 1, NCHAR(UNICODE(substring(@OrigSpText1, @i, 1)) ^
(UNICODE(substring(@OrigSpText2, @i, 1)) ^
UNICODE(substring(@OrigSpText3, @i, 1)))))
SET @i=@i+1
END
set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))
set @resultsp=(case when @encrypted=1
then @resultsp
else convert(nvarchar(4000),case when @status&2=2 then uncompress(@orgvarbin) else @orgvarbin end)
end)
print @resultsp
set @n=@n+1
end
end
set @k=@k+1
end
drop table #temp
rollback tran
end
GO
执行时，在查询分析器中执行 exec sp_decrypt '被解密存储过程名'即可。
------------------------------------------------------------------------------------------------------------------------
收藏几段SQL Server语句和存储过程
-- ======================================================
--列出SQL SERVER 所有表，字段名，主键，类型，长度，小数位数等信息
--在查询分析器里运行即可,可以生成一个表，导出到EXCEL中
-- ======================================================
SELECT
(case when a.colorder=1 then d.name else '' end)表名,
a.colorder 字段序号,
a.name 字段名,
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) 标识,
(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) 主键,
b.name 类型,
a.length 占用字节数,
COLUMNPROPERTY(a.id,a.name,'PRECISION') as 长度,
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as 小数位数,
(case when a.isnullable=1 then '√'else '' end) 允许空,
isnull(e.text,'') 默认值,
isnull(g.[value],'') AS 字段说明
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 a.id,a.colorder
-------------------------------------------------------------------------------------------------
列出SQL SERVER 所有表、字段定义，类型，长度，一个值等信息
并导出到Excel 中
-- ======================================================
-- Export all user tables definition and one sample value
-- jan-13-2003,Dr.Zhang
-- ======================================================
在查询分析器里运行：
SET ANSI_NULLS OFF
GO
SET NOCOUNT ON
GO
SET LANGUAGE 'Simplified Chinese'
go
DECLARE @tbl nvarchar(200),@fld nvarchar(200),@sql nvarchar(4000),@maxlen int,@sample nvarchar(40)
SELECT d.name TableName,a.name FieldName,b.name TypeName,a.length Length,a.isnullable IS_NULL INTO #t
FROM syscolumns a, systypes b,sysobjects d
WHERE a.xtype=b.xusertype and a.id=d.id and d.xtype='U'
DECLARE read_cursor CURSOR
FOR SELECT TableName,FieldName FROM #t
SELECT TOP 1 '_TableName ' TableName,
'FieldName ' FieldName,'TypeName ' TypeName,
'Length' Length,'IS_NULL' IS_NULL,
'MaxLenUsed' AS MaxLenUsed,'Sample Value ' Sample,
'Comment ' Comment INTO #tc FROM #t
OPEN read_cursor
FETCH NEXT FROM read_cursor INTO @tbl,@fld
WHILE (@@fetch_status <> -1) --- failes
BEGIN
IF (@@fetch_status <> -2) -- Missing
BEGIN
SET @sql=N'SET @maxlen=(SELECT max(len(cast('+@fld+' as nvarchar))) FROM '+@tbl+')'
--PRINT @sql
EXEC SP_EXECUTESQL @sql,N'@maxlen int OUTPUT',@maxlen OUTPUT
--print @maxlen
SET @sql=N'SET @sample=(SELECT TOP 1 cast('+@fld+' as nvarchar) FROM '+@tbl+' WHERE len(cast('+@fld+' as nvarchar))='+convert(nvarchar(5),@maxlen)+')'
EXEC SP_EXECUTESQL @sql,N'@sample varchar(30) OUTPUT',@sample OUTPUT
--for quickly
--SET @sql=N'SET @sample=convert(varchar(20),(SELECT TOP 1 '+@fld+' FROM '+
--@tbl+' order by 1 desc ))'
PRINT @sql
print @sample
print @tbl
EXEC SP_EXECUTESQL @sql,N'@sample nvarchar(30) OUTPUT',@sample OUTPUT
INSERT INTO #tc SELECT *,ltrim(ISNULL(@maxlen,0)) as MaxLenUsed,
convert(nchar(20),ltrim(ISNULL(@sample,' '))) as Sample,' ' Comment FROM #t where TableName=@tbl and FieldName=@fld
END
FETCH NEXT FROM read_cursor INTO @tbl,@fld
END
CLOSE read_cursor
DEALLOCATE read_cursor
GO
SET ANSI_NULLS ON
GO
SET NOCOUNT OFF
GO
select count(*) from #t
DROP TABLE #t
GO
select count(*)-1 from #tc
select * into ##tx from #tc order by tablename
DROP TABLE #tc
--select * from ##tx
declare @db nvarchar(60),@sql nvarchar(3000)
set @db=db_name()
--请修改用户名和口令 导出到Excel 中
set @sql='exec master.dbo.xp_cmdshell ''bcp ..dbo.##tx out c:/'+@db+'_exp.xls -w -C936 -Usa -Psa '''
print @sql
exec(@sql)
GO
DROP TABLE ##tx
GO
-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程，执行 spGenInsertSQL 表名
--感谢playyuer
-- ======================================================
CREATE proc spGenInsertSQL (@tablename varchar(256))
as
begin
declare @sql varchar(8000)
declare @sqlValues varchar(8000)
set @sql =' ('
set @sqlValues = 'values (''+'
select @sqlValues = @sqlValues + cols + ' + '','' + ' ,@sql = @sql + '[' + name + '],'
from
(select case
when xtype in (48,52,56,59,60,62,104,106,108,122,127)
then 'case when '+ name +' is null then ''NULL'' else ' + 'cast('+ name + ' as varchar)'+' end'
when xtype in (58,61)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast('+ name +' as varchar)'+ '+'''''''''+' end'
when xtype in (167)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (231)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'replace('+ name+','''''''','''''''''''')' + '+'''''''''+' end'
when xtype in (175)
then 'case when '+ name +' is null then ''NULL'' else '+''''''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
when xtype in (239)
then 'case when '+ name +' is null then ''NULL'' else '+'''N'''''' + ' + 'cast(replace('+ name+','''''''','''''''''''') as Char(' + cast(length as varchar) + '))+'''''''''+' end'
else '''NULL'''
end as Cols,name
from syscolumns
where id = object_id(@tablename)
) T
set @sql ='select ''INSERT INTO ['+ @tablename + ']' + left(@sql,len(@sql)-1)+') ' + left(@sqlValues,len(@sqlValues)-4) + ')'' from '+@tablename
--print @sql
exec (@sql)
end
GO
-- ======================================================
--根据表中数据生成insert语句的存储过程
--建立存储过程，执行 proc_insert 表名
--感谢Sky_blue
-- ======================================================
CREATE proc proc_insert (@tablename varchar(256))
as
begin
set nocount on
declare @sqlstr varchar(4000)
declare @sqlstr1 varchar(4000)
declare @sqlstr2 varchar(4000)
select @sqlstr='select ''insert '+@tablename
select @sqlstr1=''
select @sqlstr2=' ('
select @sqlstr1= ' values ( ''+'
select @sqlstr1=@sqlstr1+col+'+'',''+' ,@sqlstr2=@sqlstr2+name +',' from (select case
-- when a.xtype =173 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =104 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(1),'+a.name +')'+' end'
when a.xtype =175 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =61 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =106 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =62 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =56 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(11),'+a.name +')'+' end'
when a.xtype =60 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =239 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =108 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.xprec+2)+'),'+a.name +')'+' end'
when a.xtype =231 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
when a.xtype =59 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(23),'+a.name +',2)'+' end'
when a.xtype =58 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'convert(varchar(23),'+a.name +',121)'+ '+'''''''''+' end'
when a.xtype =52 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(12),'+a.name +')'+' end'
when a.xtype =122 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(22),'+a.name +')'+' end'
when a.xtype =48 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar(6),'+a.name +')'+' end'
-- when a.xtype =165 then 'case when '+a.name+' is null then ''NULL'' else '+'convert(varchar('+convert(varchar(4),a.length*2+2)+'),'+a.name +')'+' end'
when a.xtype =167 then 'case when '+a.name+' is null then ''NULL'' else '+'''''''''+'+'replace('+a.name+','''''''','''''''''''')' + '+'''''''''+' end'
else '''NULL'''
end as col,a.colid,a.name
from syscolumns a where a.id = object_id(@tablename) and a.xtype <>189 and a.xtype <>34 and a.xtype <>35 and a.xtype <>36
)t order by colid
select @sqlstr=@sqlstr+left(@sqlstr2,len(@sqlstr2)-1)+') '+left(@sqlstr1,len(@sqlstr1)-3)+')'' from '+@tablename
-- print @sqlstr
exec( @sqlstr)
set nocount off
end
GO
说明：本贴纯属收藏，目的在于大家交流，在此对作者表示感谢！
------------------------------------------------------------------------------------------------------------------------
批量表结构提取和批量建表
TheEdge推荐 [2007-5-16]
出处：CSDN
作者：cb0912cn
在进行系统设计和测试时，经常需要建立多库，并且各库内容相同。一般思路是导入或者是复制表的脚本来一个一个的建表，显然这样操作费时繁琐，而且不能保证索引等信息全部都一样。本文介绍的是如何生成一个库的表结构，并通过该表结构反向生成表。
一 建立一个存储表结构的表
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tablestruc]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tablestruc]
GO
CREATE TABLE [dbo].[tablestruc] (
[表名] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[表说明] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[字段名] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[字段说明] [nvarchar] (100) COLLATE Chinese_PRC_CI_AS NULL ,
[标识] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[主键] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[类型] [nvarchar] (128) COLLATE Chinese_PRC_CI_AS NULL ,
[占用字节数] [smallint] NOT NULL ,
[允许空] [varchar] (2) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[默认值] [nvarchar] (4000) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[长度] [int] NULL ,
[小数位数] [int] NOT NULL ,
[字段序号] [smallint] NOT NULL ,
[索引] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
二 提取表结构
SELECT
表名=case when a.colorder=1 then d.name else '' end,
表说明=case when a.colorder=1 then convert(nvarchar(100) ,isnull(f.value,'')) else '' end,
字段名=a.name,
字段说明=convert(nvarchar(100),isnull(g.[value],'')),
标识=case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,
主键=case when exists(SELECT 1 FROM sysobjects where xtype='PK' and name in (
SELECT name FROM sysindexes WHERE indid in(
SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid
))) then '√' else '' end,
类型=b.name,
占用字节数=a.length,
允许空=case when a.isnullable=1 then '√'else '' end,
默认值=isnull(e.text,''),
长度=COLUMNPROPERTY(a.id,a.name,'PRECISION'),
小数位数=isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),
字段序号=a.colorder,
索引=(SELECT top 1 name FROM sysindexes m , sysindexkeys n WHERE m.id=n.id and m.indid=n.indid and n.colid=a.colid and m.id=a.id)
FROM syscolumns a
left join systypes b on a.xusertype=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
left join sysproperties f on d.id=f.id and f.smallid=0
--如果只查询指定表,加上此条件;查询所有表，去除此条件
order by d.name,a.id,a.colorder
三 建表
f exists(select name from sysobjects
where name='c_createTable' and type='p')
drop procedure dbo.c_createTable
go
create procedure dbo.c_createTable
as
SET NOCOUNT ON
declare cursorName cursor for
select 表名,表说明,字段名,字段说明,标识,主键,类型,占用字节数,允许空,默认值,索引 from dbo.[tablestruc]
open cursorName
while 1=1
begin
declare @TableName nvarchar(50)
declare @TableName1 nvarchar(50)
declare @TableDescription nvarchar(50)
declare @columnName nvarchar(50)
declare @identityFlag nvarchar(50)
declare @keyFlag nvarchar(50)
declare @columnType nvarchar(50)
declare @TypeLength smallint
declare @Nullflag nvarchar(50)
declare @columnDefault nvarchar(50)
declare @columnDescription nvarchar(50)
declare @IndexName nvarchar(50) --索引
fetch next from cursorname into @TableName1, @TableDescription, @columnName, @columnDescription,@identityFlag, @keyFlag, @columnType, @TypeLength, @Nullflag, @columnDefault,@IndexName
if @@fetch_status<>0
break
if @tablename1 is not null and @tablename1<>' '
select @tablename=@tablename1
if @columnType='nvarchar' or @columnType='nchar'
select @TypeLength=@TypeLength/2
if @TypeLength>4000
select @TypeLength=4000
if @columnType='varchar' or @columnType='nvarchar' or @columnType='char' or @columnType='nchar'
select @columnType=@columnType+'('+ltrim(str(@TypeLength))+')'
if @nullflag='√'
select @nullflag=''
else
select @nullflag='not null'
if @columnDefault<>''
begin
if @columnType='int' or @columnType='tinyint' or @columnType='smallint'
select @columnDefault='default '+@columnDefault
else
select @columnDefault='default '+char(39)+@columnDefault+char(39)
end
if @identityFlag='√'
select @identityFlag='identity(1,1)'
else
select @identityFlag=''
if @keyFLag='√'
select @keyFlag='PRIMARY KEY '
else
select @keyFlag=''
if @tablename1 is not null and @tablename1<>' '
begin
print @tablename
print 'create table '+ @TableName+'('+@columnName+' '+@columnType+' '+@nullflag+' '+@identityFlag+
' '+ @keyFlag+' '+@columnDefault +')'
if exists(select name from sysobjects where name=@tablename and type='u')
exec ('drop table '+ @tablename)
exec ('create table '+ @TableName+'('+@columnName+' '+@columnType+' '+@nullflag+' '+@identityFlag+
' '+ @keyFlag+' '+@columnDefault +')')
if @tableDescription is not null and @tableDescription<>' '
EXEC sp_addextendedproperty 'MS_Description', @tableDescription, 'user', dbo, 'table', @TableName
if @columnDescription is not null and @columnDescription<>' '
EXEC sp_addextendedproperty 'MS_Description', @columnDescription, 'user', dbo, 'table', @TableName, 'column', @columnName
end
else
begin
exec (' alter table '+@TableName +' add '+@columnName+' '+@columnType+' '+@identityFlag+' '+@columnDefault)
if @columnDescription is not null and @columnDescription<>' '
EXEC sp_addextendedproperty 'MS_Description', @columnDescription, 'user', dbo, 'table', @TableName, 'column', @columnName
if @nullflag='not null'
exec (' alter table '+@TableName +' alter column '+@columnName+' '+@columnType+ ' not null')
if @keyFlag='PRIMARY KEY '
exec (' alter table '+@TableName +' add constraint '+'pk_'+@TableName+'_'+@columnName+' '+' primary key('+@columnName+')')
else
if isnull(@indexName,'')<>''
begin
exec(' create index ' + @indexName+ ' on ' + @tablename+ ' ( '+@columnName+' )')
select @indexName=''
end
end
end
close cursorname
deallocate cursorname
go
上述方法不适合于复合索引。
扫一扫
835
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
