SQL SERVER查询字段在哪个表里

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    

 

转载于:https://www.cnblogs.com/JinweiChang/p/10592817.html