sql 中对象的判断

----判断表是否存在
IF exists(select * from syscolumns where id=object_id('dbo.ActivityAssociateSubject'))
BEGIN
----判断字段是否存在
IF NOT exists( select * from sysobjects o ,syscolumns c,systypes t  where o.id=c.id and c.xtype=t.xtype and o.name = 'ActivityAssociateSubject' and c.name='subject_code' and c.length = 33 and t.name = 'varchar')
BEGIN
print('dbo.BAINFO'+'中的'+'baname不存在')---判断字段是否存在
END
------判断主键


SELECT 
    * 
FROM sys.indexes IDX
    INNER JOIN sys.index_columns IDXC
        ON IDX.[object_id]=IDXC.[object_id]
            AND IDX.index_id=IDXC.index_id
    LEFT JOIN sys.key_constraints KC
        ON IDX.[object_id]=KC.[parent_object_id]
            AND IDX.index_id=KC.unique_index_id
    INNER JOIN sys.columns C
        ON IDX.[object_id]=C.[object_id]
            AND IDXC.Column_id=C.Column_id
            where object_name(IDX.object_id)=  'ActivityAssociateSubject' and KC.type = 'PK'
            
            
            
------判断索引
SELECT 
    * 
FROM sys.indexes IDX
    INNER JOIN sys.index_columns IDXC
        ON IDX.[object_id]=IDXC.[object_id]
            AND IDX.index_id=IDXC.index_id
    LEFT JOIN sys.key_constraints KC
        ON IDX.[object_id]=KC.[parent_object_id]
            AND IDX.index_id=KC.unique_index_id
    INNER JOIN sys.columns C
        ON IDX.[object_id]=C.[object_id]
            AND IDXC.Column_id=C.Column_id
            where object_name(IDX.object_id)=  'activitytable' and c.name in('EndTime','StartTime','ActCounter')
            
------判断默认值 


select  *
    from       syscolumns       a       
    join       sysobjects       b       on       a.id=b.id       
    join       syscomments       c       on       a.cdefault=c.id       
    join       sysobjects       d       on       c.id=d.id       
    where       b.name= 'ActivityAssociateSubject'           
    and       a.name= 'subject_code'




------

END

select * from sysobjects where name = 'ActivityAssociateSubject'


select * from sysobjects where   id = '779149821'       
select * from syscolumns where   id = '779149821' 
----xtype 类型，关联systypes表
select * from systypes where  xtype = '56'
select * from systypes where  xtype = '167'
----cdefault 默认值,为0表示没有默认值
select * from syscomments  where id = '791934143' 
-----isnullable 表示是否为空，0表示不允许为空，1表示可以为空
select name,isnullable from syscolumns where   id = '779149821' 
-----主键
 select * from  sys.indexes where   object_id = '779149821'     and  is_unique = 0 and  index_id = 7  
 select * from  sys.index_columns  where object_id = '779149821' and index_id = 7  
 select * from  sys.key_constraints where parent_object_id = '779149821'
 select * from  sys.columns  where object_id = '779149821' and column_id = 1


----- 聚集索引


 select * from  sys.indexes where   object_id = '779149821'     and   type_desc = 'CLUSTERED' 
 select * from  sys.index_columns  where object_id = '779149821' and index_id = 1 


 select * from  sys.columns  where object_id = '779149821' and column_id =4
 select * from  sys.columns  where object_id = '779149821' and column_id = 31
 
--- 非聚集索引


 select * from  sys.indexes where   object_id = '779149821'     and   type_desc = 'NONCLUSTERED' 
 select * from  sys.index_columns  where object_id = '779149821' and index_id =5 


 select * from  sys.columns  where object_id = '779149821' and column_id =2