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)

打赏

博主开通了微信公众号,欢迎关注啦

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.