sql2005一点点心得

卸载ms sql 2005

-------------------------------------------------------------------------------

首先卸载ms sql server
再卸载ms sql native client
再卸载ms sql 2005安装程序支持文件
其他剩下的全删除掉。

删除
系统盘下的/program files/sql server 2005目录

-------------------------------------------------------------------------------

MS SQL中取得表结构信息的SQL
关键词： SQL    表结构                                          
 Select Sysobjects.Name As 表名, Syscolumns.Name As 列名, Systypes.Name As 数据类型, Syscolumns.Length As 长度, Isnull(Sysproperties.Value,Syscolumns.Name) As 备注,
 Case When Syscolumns.Name In
 (Select 主键=A.Name
 From Syscolumns A
 Inner Join Sysobjects B On A.Id=B.Id And B.Xtype='U' And B.Name<>'Dtproperties'
 Where 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
 )))
 And B.Name=Sysobjects.Name
 )
 Then 1 Else 0 End As 是否主键
 From Sysobjects,Systypes,Syscolumns
 Left Join Sysproperties On (Syscolumns.Id = Sysproperties.Id And
 Syscolumns.Colid = Sysproperties.Smallid)
 Where (Sysobjects.Xtype ='U' Or Sysobjects.Xtype ='V')
 And Sysobjects.Id = Syscolumns.Id And Systypes.Xtype = Syscolumns.Xtype
 and Systypes.Name <> 'Sysname' And Sysobjects.Name Like '%'
 Order By Sysobjects.Name, Syscolumns.Colid

-------------------------------------------------------------------------------

及联更新和删除

设置表myBBS中的authorId为外键，参照author表的Id字段，直接使用transact sql语句，过程如下：
--增加表mybbs(authorId)的外键约束FK_mybbs_author，表myBBS中的authorId受表author中的主键ID约束：
BEGIN TRANSACTION
alter table dbo.mybbs add constraint FK_mybbs_author
  foreign key (authorId)
  references  dbo.author([id]) ON UPDATE CASCADE ON DELETE CASCADE

--删除外键约束FK_mybbs_author：
--alter table dbo.mybbs drop constraint FK_mybbs_author
--rollback
commit transaction

上面ON UPDATE CASCADE，ON DELETE CASCADE两个选项，指明以后author表的id字段有delete,update操作时，myBBS表中的id也会被级联删除或更新。如果没有选中，是不可以对author表中已被myBBS表关联的id进行update或者delete操作的。

-------------------------------------------------------------------------------

USE [xxx]
GO
ALTER TABLE [dbo].[xxx]  WITH CHECK ADD  CONSTRAINT [FK_xxx] FOREIGN KEY(xxx)
REFERENCES [dbo].[xxx] ([xxx])
ALTER TABLE [dbo].[xxx]  WITH CHECK ADD  CONSTRAINT [FK_xxx] FOREIGN KEY(xxx)
REFERENCES [dbo].[xxx] ([xxx])

USE [xxx]
GO
/****** 对象:  Index [uk_name]    脚本日期: 07/02/2008 14:57:55 ******/
ALTER TABLE [dbo].[A] ADD  CONSTRAINT [uk_name] UNIQUE NONCLUSTERED
(
 [NAME] ASC,
 [update_on] ASC
)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

alter table b with check add constraint FK_NAME_1 foreign key([id],[name]) references a([id],[name])

关于建立外键约束
外键的类型必须一致，包括长度，但是是否为空无所谓
外键必须关联到他所关联的表中的一条记录，也就是多对一的原则

-------------------------------------------------------------------------------