sql schema scripts

--1. 获取表的基本字段属性

SELECT syscolumns.name,systypes.name,syscolumns.isnullable,
       syscolumns.length
       FROM syscolumns, systypes  WHERE syscolumns.xusertype = systypes.xusertype
                                  AND syscolumns.id = object_id('inventory')

2.  sql modules

     SELECT sm.object_id, OBJECT_NAME(sm.object_id) AS object_name, o.type, o.type_desc, sm.definition
     FROM sys.sql_modules AS sm
     JOIN sys.objects AS o ON sm.object_id = o.object_id
     ORDER BY o.type;
GO

 --3.单独查询表的递增字段
 
 select [name] from syscolumns
                    where  id=object_id(N'Inventory')
                    and COLUMNPROPERTY(id,name,'IsIdentity')=1

--4.获取表的主外键,获取表主外键约束
    exec sp_helpconstraint   'Inventory' ;