查询数据库表、字段、备注

查询数据库表、字段、备注

/************************************************************
 * Code formatted by SoftTree SQL Assistant ?v5.0.97
 * Time: 2020/3/24 8:50:02
 ************************************************************/
 
-- 名名称和字段名 
SELECT 
  (
    CASE WHEN a.colorder = 1 THEN d.name ELSE '' END
  ) 表名, 
  a.colorder 字段序号, 
  a.name 字段名, 
  (
    CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1 THEN '√' ELSE '' END
  ) 标识, 
  (
    CASE WHEN (
      SELECT 
        COUNT(*) 
      FROM 
        sysobjects 
      WHERE 
        (
          NAME IN (
            SELECT 
              NAME 
            FROM 
              sysindexes 
            WHERE 
              (id = a.id) 
              AND (
                indid IN (
                  SELECT 
                    indid 
                  FROM 
                    sysindexkeys 
                  WHERE 
                    (id = a.id) 
                    AND (
                      colid IN (
                        SELECT 
                          colid 
                        FROM 
                          syscolumns 
                        WHERE 
                          (id = a.id) 
                          AND (NAME = a.name)
                      )
                    )
                )
              )
          )
        ) 
        AND (xtype = 'PK')
    ) > 0 THEN '√' ELSE '' END
  ) 主键, 
  b.name 类型, 
  a.length 占用字节数, 
  COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度, 
  ISNULL(
    COLUMNPROPERTY(a.id, a.name, 'Scale'), 
    0
  ) AS 小数位数, 
  (
    CASE WHEN a.isnullable = 1 THEN '√' ELSE '' END
  ) 允许空, 
  ISNULL(e.text, '') 默认值, 
  ISNULL(g.[value], '') AS 字段说明 
FROM 
  syscolumns a 
  LEFT JOIN systypes b ON a.xtype = 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 
ORDER BY 
  a.id, 
  a.colorder

 

查询表、字段、备注

-- 名名称和字段名 
SELECT (CASE WHEN a.colorder=1 THEN d.name ELSE d.name END) 表名, a.colorder 字段序号, a.name 字段名, (CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity')=1 THEN '√' ELSE '' END) 标识, (CASE WHEN (SELECT COUNT(*)
                                                                                                                                                                                           FROM sysobjects
                                                                                                                                                                                           WHERE(name IN(SELECT name
                                                                                                                                                                                                         FROM sysindexes
                                                                                                                                                                                                         WHERE(id=a.id)AND(indid IN(SELECT indid
                                                                                                                                                                                                                                    FROM sysindexkeys
                                                                                                                                                                                                                                    WHERE(id=a.id)AND(colid IN(SELECT colid FROM syscolumns WHERE(id=a.id)AND(name=a.name)))))))AND(xtype='PK'))>0 THEN '√' ELSE '' END) 主键, b.name 类型, a.length 占用字节数, COLUMNPROPERTY(a.id, a.name, 'PRECISION') AS 长度, ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'), 0) AS 小数位数, (CASE WHEN a.isnullable=1 THEN '√' ELSE '' END) 允许空, ISNULL(e.text, '') 默认值, ISNULL(g.[value], '') AS 字段说明
INTO #tmp
FROM syscolumns a
     LEFT JOIN systypes b ON a.xtype=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
--  WHERE d.name LIKE '%Buy_%'
ORDER BY a.id, a.colorder
SELECT (CASE WHEN #tmp.字段序号=1 THEN 表名 ELSE '' END) 表名, (CASE WHEN #tmp.字段序号=1 THEN ISNULL(g.[value], '')ELSE '' END) AS 表说明, #tmp.字段序号, #tmp.字段名, #tmp.标识, #tmp.主键, #tmp.类型, #tmp.占用字节数, #tmp.长度, #tmp.小数位数, #tmp.允许空, #tmp.默认值, #tmp.字段说明
FROM sysobjects obj
     --LEFT JOIN syscolumns a ON a.id = obj.id AND obj.xtype = 'U' AND obj.name <> 'dtproperties'
     LEFT JOIN sys.extended_properties g ON g.major_id=obj.id
     JOIN #tmp ON #tmp.表名=obj.name
WHERE obj.type='U' AND obj.name<>'sysdiagrams' AND g.minor_id=0
DROP TABLE #tmp

 

历史SQL语句记录

 SELECT st.text as sql_statement,
       qs.creation_time as plan_last_compiled,
       qs.last_execution_time as plan_last_executed,
       qs.execution_count as plan_executed_count,
       qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.plan_handle) st
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp

WHERE DATEDIFF(SECOND, qs.last_execution_time,GETDATE())<10
order by plan_last_executed desc