第一课：学习工具准备（磨刀不误砍材功）

最新推荐文章于 2022-03-31 14:51:56 发布

NO66781

最新推荐文章于 2022-03-31 14:51:56 发布

阅读量575

点赞数

分类专栏：时空智友

本文链接：https://blog.csdn.net/NO66781/article/details/91405542

版权

时空智友专栏收录该内容

61 篇文章 65 订阅

订阅专栏

查看库中所有表

SELECT a.name AS TBNAME,ISNULL(b.TBDESC,'') AS TBDESC,ISNULL(b.TBTYPE,'') AS TBTYPE,ISNULL(b.CATFLAG,'') AS CATFLAG,ISNULL(b.EXPLAINS,'') AS EXPLAINS
FROM sys.tables AS a
     LEFT JOIN TBLIST AS b ON a.name = b.TBNAME
ORDER BY b.CATFLAG DESC,b.TBDESC ASC

查看表中所有字段信息

select ID = a.Column_id
      ,FDNAME = a.Name
      ,FDDESC = ISNULL(g.FDDESC,'')
      ,FDIDENTITY = case when is_identity=1 then '√' else '' END
      ,FDKEY = case when exists ( select 1 
                                   FROM sys.objects x
                                        JOIN sys.indexes y on x.Type=N'PK' and x.Name=y.Name
                                        JOIN sysindexkeys z on z.ID=a.Object_id and z.indid=y.index_id and z.Colid=a.Column_id ) 
                     THEN '√'  else '' END
      ,FDTYPE = b.Name
      ,FDSIZE = case when ColumnProperty(a.object_id,a.Name,'Precision')=-1 then '2^31-1' else rtrim(ColumnProperty(a.object_id,a.Name,'Precision')) END
      ,FDDEC = isnull(ColumnProperty(a.object_id,a.Name,'Scale'),0)
      ,FDISNULL = case when a.is_nullable=1 then '√' else '' END
      ,DEFAULTVALUE = isnull(d.text,'')
from    sys.columns a
        left join sys.types b on a.user_type_id=b.user_type_id
        inner join sys.objects c on a.object_id=c.object_id and c.Type='U'
        left join syscomments d on a.default_object_id=d.ID
        left join sys.extended_properties e on e.major_id=c.object_id  and e.minor_id=a.Column_id and e.class=1
        left join sys.extended_properties f on f.major_id=c.object_id and f.minor_id=0 and f.class=1
        LEFT JOIN FLDLIST AS g ON a.name = g.FDNAME
WHERE   c.name='TBLIST'