SQL SERVER查询字段在哪个表里

最新推荐文章于 2023-04-07 14:27:58 发布

weixin_30859423

最新推荐文章于 2023-04-07 14:27:58 发布

阅读量127

点赞数

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/JinweiChang/p/10592817.html

本文提供了一段SQL脚本，用于从数据库中查询表的详细信息，包括表名、字段名、字段类型等，并支持模糊搜索。该脚本适用于需要了解数据库表结构的场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

DECLARE @ColumnName  AS VARCHAR(100);
SET  @ColumnName='字段名的模糊匹配';

SELECT     
表名=D.NAME,  
表说明 = CASE   WHEN   A.COLORDER=1   THEN   ISNULL(F.VALUE, ' ')   ELSE   ' '   END,   
字段序号 = A.COLORDER,   
字段名 =   A.NAME,   
标识  =   CASE   WHEN   COLUMNPROPERTY(   A.ID,A.NAME, 'ISIDENTITY ')=1   THEN   '√ 'ELSE   ' '   END,   
主鍵 =   CASE   WHEN   EXISTS(SELECT   1   FROM   SYSOBJECTS   WHERE   XTYPE= 'PK '   AND   PARENT_OBJ=A.ID   AND   NAME   IN   (   
SELECT   NAME   FROM   SYSINDEXES   WHERE   INDID   IN(   
SELECT   INDID   FROM   SYSINDEXKEYS   WHERE   ID   =   A.ID   AND   COLID=A.COLID)))   THEN   '√ '   ELSE   ' '   END,   
类型 =   B.NAME,   
占用字节数   =   A.LENGTH,   
长度 =  COLUMNPROPERTY(A.ID,A.NAME, 'PRECISION '),   
小数位数 =   ISNULL(COLUMNPROPERTY(A.ID,A.NAME, 'SCALE '),0),   
允许空 =   CASE   WHEN   A.ISNULLABLE=1   THEN   '√ 'ELSE   ' '   END,   
默认值  =   ISNULL(E.TEXT, ' '),   
字段说明  =   ISNULL(G.[VALUE], ' ')   
FROM   SYSCOLUMNS   A LEFT   JOIN   SYSTYPES   B   ON   A.XUSERTYPE=B.XUSERTYPE   
INNER   JOIN   SYSOBJECTS   D   ON   A.ID=D.ID     AND   D.XTYPE= 'U '   AND     D.NAME <> 'DTPROPERTIES '   
LEFT   JOIN   SYSCOMMENTS   E   ON   A.CDEFAULT=E.ID   
LEFT   JOIN   sys.extended_properties   G   ON   A.ID=G.major_id   AND   A.COLID=G.minor_id       
LEFT   JOIN   sys.extended_properties   F   ON   D.ID=F.major_id   AND   F.minor_id=0   
WHERE a.name like  @ColumnName AND RIGHT(D.NAME,3)<>'Log'  
ORDER BY A.ID,A.COLORDER