sql2000不常用的一些功能

建立链接

exec   sp_dropserver 'ExcelSource','droplogins'

EXEC sp_addlinkedserver 'ExcelSource',
                        'Jet 4.0',
                        'Microsoft.Jet.OLEDB.4.0',
                        'D:/STATUSWEBPM.xls',
                        NULL,
                        'Excel 5.0'

SELECT * FROM ExcelSource...[STATUSWEBPM$]

--EXEC sp_addlinkedsrvlogin 'ExcelSource', 'false', 'SA', 'Admin', NULL

--表名加$

到xml

create table class (id varchar(5), title varchar(10))
insert into class select 1,   '美国'
union all select 2,   '中国'

create table item(id varchar(5), classid varchar(5),  title varchar(10))
insert into item select 1,   1,        'KFC'
union all select 2,   1,        '麦当'
union all select 3,   2,        '合和'

--自定义
select tag ,parent, [row!1],[id!2],[title!3],[item!4],[id!5],[title!6]
from(
select 1 tag,null parent,null [row!1],null [id!2],null [title!3],null [item!4]
,null [id!5],null [title!6],id from class
union all select 2,1,null,id,null,null,null,null,id from class
union all select 3,1,null,null,title,null,null,null,id from class
union all select 4,1,null,null,null,null,null,null,classid+id from item
union all select 5,4,null,null,null,null,id,null,classid+id from item
union all select 6,4,null,null,null,null,null,title,classid+id from item
)a
order by id,tag
for xml explicit

--默认

SELECT
 ID = row.ID,
 title = row.title,
 [ID] = item.id,
 [title] = item.title
FROM class row, item
WHERE row.id = item.classid
FOR XML AUTO, ELEMENTS

--------------------
<row>
  <id>1</id>
  <title>美国</title>
  <item>
    <id>1</id>
    <title>KFC</title>
  </item>
  <item>
    <id>2</id>
    <title>麦当</title>
  </item>
</row>

<row>
  <id>2</id>
  <title>中国</title>
  <item>
    <id>3</id>
    <title>合和</title>
  </item>
</row>

约束操作

create table aa(id int primary Key,name varchar(10))
insert into aa select 1,'select'

create table bb
(
id int CONSTRAINT
 FK__bb__id__03F0984C Foreign Key  References aa(id)
         ON UPDATE CASCADE
  ON DELETE CASCADE--Action
,name varchar(10)
)
insert into bb select 1,'*'

ALTER   TABLE   bb   NOCHECK   CONSTRAINT   FK__bb__id__03F0984C
insert into bb select 2,'*'
update bb set id=1 where id<>1
ALTER   TABLE   bb   CHECK   CONSTRAINT   FK__bb__id__03F0984C

ALTER TABLE dbo.bb
 DROP CONSTRAINT FK__bb__id__03F0984C

drop table aa,bb

全角半角

UNICODE('～')-UNICODE('~')=65248
nchar(65281-65248)='!'

自定义系统函数

CREATE FUNCTION fn_ame()--fn_开头
RETURNS varchar(10)
AS
begin
RETURN 'myname'
end
go
exec sp_configure 'allow updates',1--allow updates用户可否修改系统表
reconfigure with override
go
exec sp_mschangeobjectowner 'fn_ame','system_function_schema'--设所有者
go
exec sp_configure 'allow updates',0
reconfigure with override

go
select fn_ame()

go
exec sp_configure 'allow updates',1
reconfigure with override
go
update sysobjects set uid = 1 where name = 'fn_ame'
go
exec sp_configure 'allow updates',0
reconfigure with override

go
drop FUNCTION fn_ame

不解发事务下的删表数据

truncate table tablename