?
一、?只復制一個表結構，不復制數據
?
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