一、 只复制一个表结构，不复制数据
select
top
0
*
into
[
t1
]
from
[
t2
]
二、 获取数据库中某个对象的创建脚本
1、 先用下面的脚本创建一个函数
if
exists
(
select
1
from
sysobjects
where
id
=
object_id
(
'
fgetscript
'
)
and
objectproperty
(id,
'
IsInlineFunction
'
)
=
0
)
drop
function
fgetscript
go
create
function
fgetscript(
@servername
varchar
(
50
)
--
服务器名
,
@userid
varchar
(
50
)
=
'
sa
'
--
用户名,如果为nt验证方式,则为空
,
@password
varchar
(
50
)
=
''
--
密码
,
@databasename
varchar
(
50
)
--
数据库名称
,
@objectname
varchar
(
250
)
--
对象名
)
returns
varchar
(
8000
)
as
begin
declare
@re
varchar
(
8000
)
--
返回脚本
declare
@srvid
int
,
@dbsid
int
--
定义服务器、数据库集id
declare
@dbid
int
,
@tbid
int
--
数据库、表id
declare
@err
int
,
@src
varchar
(
255
),
@desc
varchar
(
255
)
--
错误处理变量
--
创建sqldmo对象
exec
@err
=
sp_oacreate
'
sqldmo.sqlserver
'
,
@srvid
output
if
@err
<>
0
goto
lberr
--
连接服务器
if
isnull
(
@userid
,
''
)
=
''
--
如果是 Nt验证方式
begin
exec
@err
=
sp_oasetproperty
@srvid
,
'
loginsecure
'
,
1
if
@err
<>
0
goto
lberr
exec
@err
=
sp_oamethod
@srvid
,
'
connect
'
,
null
,
@servername
end
else
exec
@err
=
sp_oamethod
@srvid
,
'
connect
'
,
null
,
@servername
,
@userid
,
@password
if
@err
<>
0
goto
lberr
--
获取数据库集
exec
@err
=
sp_oagetproperty
@srvid
,
'
databases
'
,
@dbsid
output
if
@err
<>
0
goto
lberr
--
获取要取得脚本的数据库id
exec
@err
=
sp_oamethod
@dbsid
,
'
item
'
,
@dbid
output,
@databasename
if
@err
<>
0
goto
lberr
--
获取要取得脚本的对象id
exec
@err
=
sp_oamethod
@dbid
,
'
getobjectbyname
'
,
@tbid
output,
@objectname
if
@err
<>
0
goto
lberr
--
取得脚本
exec
@err
=
sp_oamethod
@tbid
,
'
script
'
,
@re
output
if
@err
<>
0
goto
lberr
--
print @re
return
(
@re
)
lberr:
exec
sp_oageterrorinfo
NULL
,
@src
out,
@desc
out
declare
@errb
varbinary
(
4
)
set
@errb
=
cast
(
@err
as
varbinary
(
4
))
exec
master..xp_varbintohexstr
@errb
,
@re
out
set
@re
=
'
错误号:
'
+
@re
+
char
(
13
)
+
'
错误源:
'
+
@src
+
char
(
13
)
+
'
错误描述:
'
+
@desc
return
(
@re
)
end
go
2、 用法如下
用法如下,
print
dbo.fgetscript(
'
服务器名
'
,
'
用户名
'
,
'
密码
'
,
'
数据库名
'
,
'
表名或其它对象名
'
)
3、 如果要获取库里所有对象的脚本，如如下方式
declare
@name
varchar
(
250
)
declare
#aa
cursor
for
select
name
from
sysobjects
where
xtype
not
in
(
'
S
'
,
'
PK
'
,
'
D
'
,
'
X
'
,
'
L
'
)
open
#aa
fetch
next
from
#aa
into
@name
while
@@fetch_status
=
0
begin
print
dbo.fgetscript(
'
onlytiancai
'
,
'
sa
'
,
'
sa
'
,
'
database
'
,
@name
)
fetch
next
from
#aa
into
@name
end
close
#aa
deallocate
#aa
4、 声明，此函数是csdn邹建邹老大提供的
三、 分隔字符串
如果有一个用逗号分割开的字符串，比如说"a,b,c,d,1,2,3,4"，如何用t-sql获取这个字符串有几个元素，获取第几个元素的值是多少呢？因为t-sql里没有split函数，也没有数组的概念，所以只能自己写几个函数了。
1、 获取元素个数的函数
create
function
getstrarrlength (
@str
varchar
(
8000
))
returns
int
as
begin
declare
@int_return
int
declare
@start
int
declare
@next
int
declare
@location
int
select
@str
=
'
,
'
+
@str
+
'
,
'
select
@str
=
replace
(
@str
,
'
,,
'
,
'
,
'
)
select
@start
=
1
select
@next
=
1
select
@location
=
charindex
(
'
,
'
,
@str
,
@start
)
while
(
@location
<>
0
)
begin
select
@start
=
@location
+
1
select
@location
=
charindex
(
'
,
'
,
@str
,
@start
)
select
@next
=
@next
+
1
end
select
@int_return
=
@next
-
2
return
@int_return
end
2、 获取指定索引的值的函数
create
function
getstrofindex (
@str
varchar
(
8000
),
@index
int
=
0
)
returns
varchar
(
8000
)
as
begin
declare
@str_return
varchar
(
8000
)
declare
@start
int
declare
@next
int
declare
@location
int
select
@start
=
1
select
@next
=
1
--
如果习惯从0开始则select @next =0
select
@location
=
charindex
(
'
,
'
,
@str
,
@start
)
while
(
@location
<>
0
and
@index
>
@next
)
begin
select
@start
=
@location
+
1
select
@location
=
charindex
(
'
,
'
,
@str
,
@start
)
select
@next
=
@next
+
1
end
if
@location
=
0
select
@location
=
len
(
@str
)
+
1
--
如果是因为没有逗号退出，则认为逗号在字符串后
select
@str_return
=
substring
(
@str
,
@start
,
@location
-
@start
)
--
@start肯定是逗号之后的位置或者就是初始值1
if
(
@index
<>
@next
)
select
@str_return
=
''
--
如果二者不相等，则是因为逗号太少，或者@index小于@next的初始值1。
return
@str_return
end
3、 测试
SELECT
[
dbo
]
.
[
getstrarrlength
]
(
'
1,2,3,4,a,b,c,d
'
)
SELECT
[
dbo
]
.
[
getstrofindex
]
(
'
1,2,3,4,a,b,c,d
'
,
5
)
四、 一条语句执行跨越若干个数据库
我要在一条语句里操作不同的服务器上的不同的数据库里的不同的表,怎么办呢?
第一种方法:
select
*
from
OPENDATASOURCE
(
'
SQLOLEDB
'
,
'
Data Source=远程ip;User ID=sa;Password=密码
'
).库名.dbo.表名
第二种方法:
先使用联结服务器:
EXEC
sp_addlinkedserver
'
别名
'
,
''
,
'
MSDASQL
'
,
NULL
,
NULL
,
'
DRIVER={SQL Server};SERVER=远程名;UID=用户;PWD=密码;
'
exec
sp_addlinkedsrvlogin
@rmtsrvname
=
'
别名
'
,
@useself
=
'
false
'
,
@locallogin
=
'
sa
'
,
@rmtuser
=
'
sa
'
,
@rmtpassword
=
'
密码
'
GO
然后你就可以如下：
select
*
from
别名.库名.dbo.表名
insert
库名.dbo.表名
select
*
from
别名.库名.dbo.表名
select
*
into
库名.dbo.新表名
from
别名.库名.dbo.表名
go
五、 怎样获取一个表中所有的字段信息
蛙蛙推荐：怎样获取一个表中所有字段的信息
先创建一个视图
Create
view
fielddesc
as
select
o.name
as
table_name,c.name
as
field_name,t.name
as
type,c.length
as
length,c.isnullable
as
isnullable,
convert
(
varchar
(
30
),p.value)
as
desp
from
syscolumns c
join
systypes t
on
c.xtype
=
t.xusertype
join
sysobjects o
on
o.id
=
c.id
left
join
sysproperties p
on
p.smallid
=
c.colid
and
p.id
=
o.id
where
o.xtype
=
'
U
'
查询时:
Select
*
from
fielddesc
where
table_name
=
'
你的表名
'
还有个更强的语句，是邹建写的，也写出来吧
SELECT
(
case
when
a.colorder
=
1
then
d.name
else
''
end
) N
'
表名
'
,
a.colorder N
'
字段序号
'
,
a.name N
'
字段名
'
,
(
case
when
COLUMNPROPERTY
( a.id,a.name,
'
IsIdentity
'
)
=
1
then
'
√
'
else
''
end
) N
'
标识
'
,
(
case
when
(
SELECT
count
(
*
)
FROM
sysobjects
WHERE
(name
in
(
SELECT
name
FROM
sysindexes
WHERE
(id
=
a.id)
AND
(indid
in
(
SELECT
indid
FROM
sysindexkeys
WHERE
(id
=
a.id)
AND
(colid
in
(
SELECT
colid
FROM
syscolumns
WHERE
(id
=
a.id)
AND
(name
=
a.name)))))))
AND
(xtype
=
'
PK
'
))
>
0
then
'
√
'
else
''
end
) N
'
主键
'
,
b.name N
'
类型
'
,
a.length N
'
占用字节数
'
,
COLUMNPROPERTY
(a.id,a.name,
'
PRECISION
'
)
as
N
'
长度
'
,
isnull
(
COLUMNPROPERTY
(a.id,a.name,
'
Scale
'
),
0
)
as
N
'
小数位数
'
,
(
case
when
a.isnullable
=
1
then
'
√
'
else
''
end
) N
'
允许空
'
,
isnull
(e.
text
,
''
) N
'
默认值
'
,
isnull
(g.
[
value
]
,
''
)
AS
N
'
字段说明
'
--
into ##tx
FROM
syscolumns a
left
join
systypes b
on
a.xtype
=
b.xusertype
inner
join
sysobjects d
on
a.id
=
d.id
and
d.xtype
=
'
U
'
and
d.name
<>
'
dtproperties
'
left
join
syscomments e
on
a.cdefault
=
e.id
left
join
sysproperties g
on
a.id
=
g.id
AND
a.colid
=
g.smallid
order
by
object_name
(a.id),a.colorder
六、 时间格式转换问题
因为新开发的软件需要用一些旧软件生成的一些数据，在时间格式上不统一，只能手工转换，研究了一下午写了三条语句，以前没怎么用过convert函数和case语句，还有"+"操作符在不同上下文环境也会起到不同的作用，把我搞晕了要，不过现在看来是差不多弄好了。
1、把所有"70.07.06"这样的值变成"1970-07-06"
UPDATE
lvshi
SET
shengri
=
'
19
'
+
REPLACE
(shengri,
'
.
'
,
'
-
'
)
WHERE
(zhiyezheng
=
'
139770070153
'
)
2、在"1970-07-06"里提取"70","07","06"
SELECT
SUBSTRING
(shengri,
3
,
2
)
AS
year
,
SUBSTRING
(shengri,
6
,
2
)
AS
month
,
SUBSTRING
(shengri,
9
,
2
)
AS
day
FROM
lvshi
WHERE
(zhiyezheng
=
'
139770070153
'
)
3、把一个时间类型字段转换成"1970-07-06"
UPDATE
lvshi
SET
shenling
=
CONVERT
(
varchar
(
4
),
YEAR
(shenling))
+
'
-
'
+
CASE
WHEN
LEN
(
MONTH
(shenling))
=
1
THEN
'
0
'
+
CONVERT
(
varchar
(
2
),
month
(shenling))
ELSE
CONVERT
(
varchar
(
2
),
month
(shenling))
END
+
'
-
'
+
CASE
WHEN
LEN
(
day
(shenling))
=
1
THEN
'
0
'
+
CONVERT
(
char
(
2
),
day
(shenling))
ELSE
CONVERT
(
varchar
(
2
),
day
(shenling))
END
WHERE
(zhiyezheng
=
'
139770070153
'
)
七、 分区视图
分区视图是提高查询性能的一个很好的办法
--
看下面的示例
--
示例表
create
table
tempdb.dbo.t_10(
id
int
primary
key
check
(id
between
1
and
10
),name
varchar
(
10
))
create
table
pubs.dbo.t_20(
id
int
primary
key
check
(id
between
11
and
20
),name
varchar
(
10
))
create
table
northwind.dbo.t_30(
id
int
primary
key
check
(id
between
21
and
30
),name
varchar
(
10
))
go
--
分区视图
create
view
v_t
as
select
*
from
tempdb.dbo.t_10
union
all
select
*
from
pubs.dbo.t_20
union
all
select
*
from
northwind.dbo.t_30
go
--
插入数据
insert
v_t
select
1
,
'
aa
'
union
all
select
2
,
'
bb
'
union
all
select
11
,
'
cc
'
union
all
select
12
,
'
dd
'
union
all
select
21
,
'
ee
'
union
all
select
22
,
'
ff
'
--
更新数据
update
v_t
set
name
=
name
+
'
_更新
'
where
right
(id,
1
)
=
1
--
删除测试
delete
from
v_t
where
right
(id,
1
)
=
2
--
显示结果
select
*
from
v_t
go
--
删除测试
drop
table
northwind.dbo.t_30,pubs.dbo.t_20,tempdb.dbo.t_10
drop
view
v_t
/**/
/*
--测试结果
id name
----------- ----------
1 aa_更新
11 cc_更新
21 ee_更新
（所影响的行数为 3 行）
==
*/
八、 树型的实现
--
参考
--
树形数据查询示例
--
作者: 邹建
--
示例数据
create
table
[
tb
]
(
[
id
]
int
identity
(
1
,
1
),
[
pid
]
int
,name
varchar
(
20
))
insert
[
tb
]
select
0
,
'
中国
'
union
all
select
0
,
'
美国
'
union
all
select
0
,
'
加拿大
'
union
all
select
1
,
'
北京
'
union
all
select
1
,
'
上海
'
union
all
select
1
,
'
江苏
'
union
all
select
6
,
'
苏州
'
union
all
select
7
,
'
常熟
'
union
all
select
6
,
'
南京
'
union
all
select
6
,
'
无锡
'
union
all
select
2
,
'
纽约
'
union
all
select
2
,
'
旧金山
'
go
--
查询指定id的所有子
create
function
f_cid(
@id
int
)
returns
@re
table
(
[
id
]
int
,
[
level
]
int
)
as
begin
declare
@l
int
set
@l
=
0
insert
@re
select
@id
,
@l
while
@@rowcount
>
0
begin
set
@l
=
@l
+
1
insert
@re
select
a.
[
id
]
,
@l
from
[
tb
]
a,
@re
b
where
a.
[
pid
]
=
b.
[
id
]
and
b.
[
level
]
=
@l
-
1
end
/**/
/**/
/**/
/*
--如果只显示最明细的子(下面没有子),则加上这个删除
delete a from @re a
where exists(
select 1 from [tb] where [pid]=a.[id])
--
*/
return
end
go
--
调用(查询所有的子)
select
a.
*
,层次
=
b.
[
level
]
from
[
tb
]
a,f_cid(
2
)b
where
a.
[
id
]
=
b.
[
id
]
go
--
删除测试
drop
table
[
tb
]
drop
function
f_cid
go
九、 排序问题
CREATE
TABLE
[
t
]
(
[
id
]
[
int
]
IDENTITY
(
1
,
1
)
NOT
NULL
,
[
GUID
]
[
uniqueidentifier
]
NULL
)
ON
[
PRIMARY
]
GO
下面这句执行5次
insert
t
values
(
newid
())
查看执行结果
select
*
from
t
1、 第一种
select
*
from
t
order
by
case
id
when
4
then
1
when
5
then
2
when
1
then
3
when
2
then
4
when
3
then
5
end
2、 第二种
select
*
from
t
order
by
(id
+
2
)
%
6
3、 第三种
select
*
from
t
order
by
charindex
(
cast
(id
as
varchar
),
'
45123
'
)
4、 第四种
select
*
from
t
WHERE
id
between
0
and
5
order
by
charindex
(
cast
(id
as
varchar
),
'
45123
'
)
5、 第五种
select
*
from
t
order
by
case
when
id
>
3
then
id
-
5
else
id
end
6、 第六种
select
*
from
t
order
by
id
/
4
desc
,id
asc
十、 一条语句删除一批记录
首先id列是int标识类类型，然后删除ID值为5,6,8,9,10,11的列，这里的cast函数不能用convert函数代替，而且转换的类型必须是varchar，而不能是char，否则就会执行出你不希望的结果，这里的"5,6,8,9,10,11"可以是你在页面上获取的一个chkboxlist构建成的值，然后用下面的一句就全部删
除了，比循环用多条语句高效吧应该。
delete
from
[
fujian
]
where
charindex
(
'
,
'
+
cast
(
[
id
]
as
varchar
)
+
'
,
'
,
'
,
'
+
'
5,6,8,9,10,11,
'
+
'
,
'
)
>
0
还有一种就是
delete
from
table1
where
id
in
(
1
,
2
,
3
,
4
)
十一、获取子表内的一列数据的组合字符串
下面这个函数获取05年已经注册了的某个所的律师，唯一一个参数就是事务所的名称，然后返回zhuce字段里包含05字样的所有律师。
CREATE
FUNCTION
fn_Get05LvshiNameBySuo (
@p_suo
Nvarchar
(
50
))
RETURNS
Nvarchar
(
2000
)
AS
BEGIN
DECLARE
@LvshiNames
varchar
(
2000
),
@name
varchar
(
50
)
select
@LvshiNames
=
''
DECLARE
lvshi_cursor
CURSOR
FOR