卸载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])
关于建立外键约束
外键的类型必须一致,包括长度,但是是否为空无所谓
外键必须关联到他所关联的表中的一条记录,也就是多对一的原则
-------------------------------------------------------------------------------