MS SQL 笔记

查询外键约束是否存在并插入

if not exists (select 1 from sysobjects  where parent_obj = OBJECT_ID('[tableName]') and name = '[constraintName]')
	alter table [tableName]
	add constraint [constraintName]
	foreign key ([key]) 
	references [referenceTable]([referenceKey])
go

查询外键约束是否存在并删除

if exists (select 1 from sysobjects  where parent_obj = OBJECT_ID('[tableName]') and name = '[constraintName]')
	alter table [tableName]
	drop constraint [constraintName]
go

查询列名是否存在并添加

if not exists (select 1 from syscolumns where id = object_id('[tableName]') and name = '[columnName]') 
	alter table [tableName]
		add [columnname] [type] [nullable]
go

查询列名是否存在并删除

if exists (select 1 from syscolumns where id = object_id('[tableName]') and name = '[columnName]') 
	alter table [tableName]
		drop column [columnname]
go

查询表是否存在

if exists (select 1
            from  sysobjects
           where  id = object_id('Compliance.CalculationMap')
            and   type = 'U')
begin
...
end
go

对象类型(type)可以是下列对象类型之一

AF = Aggregate function (CLR)
C = CHECK constraint : check约束
D = Default or DEFAULT constraint：默认值约束
F = FOREIGN KEY constraint ：外键约束
L = Log ：日志
FN = Scalar function: 标量函数。
FS = Assembly (CLR) scalar-function：CLR的标量函数（SQL Server 2005以上）
FT = Assembly (CLR) table-valued function：CLR表值函数
IF = In-lined table-function ：内联表函数
IT = Internal table：外部表。
P = Stored procedure ：存储过程。
PC = Assembly (CLR) stored-procedure：clr存储过程。
PK = PRIMARY KEY constraint (type is K) ：主键约束
RF = Replication filter stored procedure
S = System table ：系统表
SN = Synonym
SQ = Service queue：服务队列
TA = Assembly (CLR) DML trigger：clr dml触发器。
TF = Table function ：表函数。
TR = SQL DML Trigger ：SQL DML触发器
TT = Table type：表类型
U = User table ：用户表
UQ = UNIQUE constraint (type is K) ：唯一约束
V = View ：视图
X = Extended stored procedure：扩展存储过程

检查列名是否存在并更改列名
要用到sp_rename 系统存储过程，语法
sp_rename ‘tablename.column’, ‘new name’, ‘type(column)’

IF EXISTS(select col.name from sys.columns col, sysobjects tab WHERE col.object_id = tab.id and col.name ='[your column name]' and tab.id = OBJECT_ID('[table name]'))
BEGIN
	exec sp_rename '[table.column]','[new name]', 'Column'
END
GO

更改列类型

alter table [tableName] alter column [columnName] varchar(50) not null;

判断类型是否存在并删除

IF TYPE_ID(N'[TYPENAME]') IS NOT NULL
BEGIN
	DROP TYPE [TYPENAME]
END

查询包含某一列名的表

select object_name(a.id) from (select id from syscolumns where name = '[columnName]') a

参考链接：
sys.sysobjects (Transact-SQL)