sql的一些操作

1.sql判断字段列是否存在

IF NOT EXISTS(

SELECT * 

FROM syscolumns

WHERE id = ( SELECT id

FROM sysobjects 

WHERE name = '表名t'

) 

AND name = '字段名')

2.给数据表加一个字段

alter table TB_YB_Research_Result add DepaID int （not null/null）;
3,判断该字段是否为外键

select
oSub.name AS [子表名称],
fk.name AS [外键名称],
SubCol.name AS [子表列名],
oMain.name AS [主表名称],
MainCol.name AS [主表列名]
from
sys.foreign_keys fk
JOIN sys.all_objects oSub
ON (fk.parent_object_id = oSub.object_id)
JOIN sys.all_objects oMain
ON (fk.referenced_object_id = oMain.object_id)
JOIN sys.foreign_key_columns fkCols
ON (fk.object_id = fkCols.constraint_object_id)
JOIN sys.columns SubCol
ON (oSub.object_id = SubCol.object_id
AND fkCols.parent_column_id = SubCol.column_id)
JOIN sys.columns MainCol
ON (oMain.object_id = MainCol.object_id
AND fkCols.referenced_column_id = MainCol.column_id)
WHERE
oSub.name = '你的表名字'
AND SubCol.name = '你的列名'

4,给字段加上外键

alter table TB_YB_Research_Result add constraint FK_TB_YB_Research_Result_DepaID foreign key (DepaID) references TB_Department(DepaID);

5，如果想几行sql语句一起执行则中间加go;

如下：

IF NOT EXISTS(

SELECT * 

FROM syscolumns

WHERE id = ( SELECT id

FROM sysobjects 

WHERE name = 'TB_YB_Research_Result'

) 

AND name = 'DepaID')

alter table TB_YB_Research_Result add DepaID int;
go

IF NOT EXISTS(
select   oSub.name  AS  [TB_YB_Research_Result],  fk.name AS  [FK_TB_YB_Research_Result_DepaID],  SubCol.name AS [DepaID],  oMain.name  AS  [TB_Department],
 MainCol.name AS [DepaID]from   sys.foreign_keys fk    
  JOIN sys.all_objects oSub        
   ON (fk.parent_object_id = oSub.object_id)  
    JOIN sys.all_objects oMain     
       ON (fk.referenced_object_id = oMain.object_id)  
        JOIN sys.foreign_key_columns fkCols
                 ON (fk.object_id = fkCols.constraint_object_id) 
                   JOIN sys.columns SubCol        
                   ON (oSub.object_id = SubCol.object_id    
                            AND fkCols.parent_column_id = SubCol.column_id)
                               JOIN sys.columns MainCol      
                                 ON (oMain.object_id = MainCol.object_id   
  AND fkCols.referenced_column_id = MainCol.column_id)WHERE  oSub.name  =  'TB_YB_Research_Result'  AND  SubCol.name  =  'DepaID')
alter table TB_YB_Research_Result add constraint FK_TB_YB_Research_Result_DepaID foreign key (DepaID) references TB_Department(DepaID);
go
update TB_YB_Research_Result set DepaID=1;
go
alter table TB_YB_Research_Result alter column DepaID int not null;