SQL示例 – Desen往事

查看 MSSQL 的审计日志

发表于: 2020-03-11 by 裑軆ケ喥

-- 注意：event_time 使用的是 UTC 时间，所以要加上当地时区的差值
SELECT
    CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, f.event_time), DATENAME(TZoffset, SYSDATETIMEOFFSET()))) AS event_time,
    f.sequence_number,
    f.action_id,
    a.name AS action_name,
    f.succeeded,
    f.session_server_principal_name,
    f.server_principal_name,
    f.database_name,
    f.object_id,
    f.schema_name,
    f.object_name,
    f.class_type,
    m.class_type_desc,
    f.statement
FROM sys.fn_get_audit_file('E:\sql_audit\*',default,default) f
INNER JOIN sys.dm_audit_actions a ON f.action_id=a.action_id
INNER JOIN sys.dm_audit_class_type_map m ON f.class_type=m.class_type
WHERE f.object_name='news_info'
  AND CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, f.event_time), DATENAME(TZoffset, SYSDATETIMEOFFSET())))>'2020-03-11 12:00:00'
ORDER BY f.event_time;

-- 或者
SELECT *
FROM sys.fn_get_audit_file('E:\sql_audit\*',default,default) f
WHERE f.object_name='news_info'
ORDER BY f.event_time;

-- 注意：event_time 使用的是 UTC 时间，所以要加上当地时区的差值

SELECT

CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, f.event_time), DATENAME(TZoffset, SYSDATETIMEOFFSET()))) AS event_time,

f.sequence_number,

f.action_id,

a.name AS action_name,

f.succeeded,

f.session_server_principal_name,

f.server_principal_name,

f.database_name,

f.object_id,

f.schema_name,

f.object_name,

f.class_type,

m.class_type_desc,

f.statement

FROM sys.fn_get_audit_file('E:\sql_audit\*',default,default) f

INNER JOIN sys.dm_audit_actions a ON f.action_id=a.action_id

INNER JOIN sys.dm_audit_class_type_map m ON f.class_type=m.class_type

WHERE f.object_name='news_info'

AND CONVERT(datetime, SWITCHOFFSET(CONVERT(datetimeoffset, f.event_time), DATENAME(TZoffset, SYSDATETIMEOFFSET())))>'2020-03-11 12:00:00'

ORDER BY f.event_time;

-- 或者

SELECT *

FROM sys.fn_get_audit_file('E:\sql_audit\*',default,default) f

WHERE f.object_name='news_info'

ORDER BY f.event_time;

MySQL 存储过程示例

发表于: 2020-02-282021-05-10 by 裑軆ケ喥

DROP PROCEDURE IF EXISTS order_summary_month;
DELIMITER //
CREATE PROCEDURE order_summary_month()
BEGIN
  DECLARE m datetime DEFAULT '2019-01-01';
  WHILE ( DATE_FORMAT(m,'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m') )
    DO
    IF NOT EXISTS
      (
        SELECT insertdate
        FROM app.order_summary
        WHERE statdate=DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1'
      )
      THEN
        INSERT INTO app.order_summary
          (
            statdate,
            time_unit,
            type,
            cnt_order,
            sum_price,
            cnt_weight,
            insertdate
          )
        VALUES
          (
            DATE_FORMAT(m,'%Y-%m'),
            3,
            1,
            ( 
              SELECT COUNT(order_sn) AS cnt_order
              FROM app.wx_order
              WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND type='1'
            ),
            ( 
              SELECT SUM(price) AS sum_price
              FROM app.wx_order
              WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND type='1'
                    AND gains_type='1'
            ),
            ( 
              SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight
              FROM app.wx_order O
              LEFT JOIN app.wx_order_goods G on O.id=G.order_id
              LEFT JOIN app.wx_goods_class C on G.goods_id=C.id
              WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                    AND O.type='1'
            ),
            NOW()
          );
-- /*      --不更新旧数据则可以跳过
        ELSE
          UPDATE app.wx_order_summary
            SET
              time_unit = 3,
              type = 1,
              cnt_order =
              ( 
                SELECT COUNT(order_sn) AS cnt_order
                FROM app.wx_order
                WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND type='1'
              ),
              sum_price =
              ( 
                SELECT SUM(price) AS sum_price
                FROM app.wx_order
                WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND type='1'
                      AND gains_type='1'
              ),
              cnt_weight =
              ( 
                SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight
                FROM app.wx_order O
                LEFT JOIN app.wx_order_goods G on O.id=G.order_id
                LEFT JOIN app.wx_goods_class C on G.goods_id=C.id
                WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')
                      AND O.type='1'
              ),
              insertdate = NOW()
            WHERE statdate = DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1';
-- */      -- 不更新旧数据则可以跳过
    END IF;
    COMMIT;
    SET m = DATE_SUB(m, INTERVAL -1 MONTH);
  END WHILE;
END; //
DELIMITER ;
CALL order_summary_month();

DROP PROCEDURE IF EXISTS order_summary_month;

DELIMITER //

CREATE PROCEDURE order_summary_month()

BEGIN

DECLARE m datetime DEFAULT '2019-01-01';

WHILE ( DATE_FORMAT(m,'%Y-%m') <= DATE_FORMAT(NOW(),'%Y-%m') )

IF NOT EXISTS

(

SELECT insertdate

FROM app.order_summary

WHERE statdate=DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1'

)

THEN

INSERT INTO app.order_summary

(

statdate,

time_unit,

type,

cnt_order,

sum_price,

cnt_weight,

insertdate

)

VALUES

(

DATE_FORMAT(m,'%Y-%m'),

(

SELECT COUNT(order_sn) AS cnt_order

FROM app.wx_order

WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

(

SELECT SUM(price) AS sum_price

FROM app.wx_order

WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

AND gains_type='1'

(

SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight

FROM app.wx_order O

LEFT JOIN app.wx_order_goods G on O.id=G.order_id

LEFT JOIN app.wx_goods_class C on G.goods_id=C.id

WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND O.type='1'

NOW()

);

-- /* --不更新旧数据则可以跳过

ELSE

UPDATE app.wx_order_summary

SET

time_unit = 3,

type = 1,

cnt_order =

(

SELECT COUNT(order_sn) AS cnt_order

FROM app.wx_order

WHERE FROM_UNIXTIME(order_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

sum_price =

(

SELECT SUM(price) AS sum_price

FROM app.wx_order

WHERE FROM_UNIXTIME(finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND type='1'

AND gains_type='1'

cnt_weight =

(

SELECT SUM(CAST(G.goods_weight as DECIMAL(50,2))) AS cnt_weight

FROM app.wx_order O

LEFT JOIN app.wx_order_goods G on O.id=G.order_id

LEFT JOIN app.wx_goods_class C on G.goods_id=C.id

WHERE FROM_UNIXTIME(O.finish_time, '%Y-%m') = DATE_FORMAT(m,'%Y-%m')

AND O.type='1'

insertdate = NOW()

WHERE statdate = DATE_FORMAT(m,'%Y-%m') AND time_unit='3' AND type='1';

-- */ -- 不更新旧数据则可以跳过

END IF;

COMMIT;

SET m = DATE_SUB(m, INTERVAL -1 MONTH);

END WHILE;

END; //

DELIMITER ;

CALL order_summary_month();

MSSQL 查询所有表结构

发表于: 2020-02-272021-05-10 by 裑軆ケ喥

SELECT
  表名       = CASE WHEN a.colorder = 1
                 THEN d.name
                 ELSE ''
               END,
  表描述     = CASE WHEN a.colorder = 1
                 THEN ISNULL(f.[value], '')
                 ELSE ''
               END,
  字段序号   = a.colorder,
  字段名称   = a.name,
  类型       = b.name,
  长度       = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),
  小数位数   = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'),0),
  允许为空   = CASE WHEN a.isnullable = 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,
  默认值     = ISNULL(e.text, ''),
  占用字节数 = a.length,
  标识       = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1
                 THEN '√'
                 ELSE ''
               END,
  字段描述   = 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 b.name IS NOT NULL          -- AND d.name='要查询的表'
                                  -- 如果只查询表,加上 AND 条件
ORDER BY   d.name, a.colorder;

-- 验证表数量，避免空表或表中没有ID为1的记录
SELECT name FROM sysobjects WHERE xtype='U';

SELECT

表名 = CASE WHEN a.colorder = 1

THEN d.name

ELSE ''

END,

表描述 = CASE WHEN a.colorder = 1

THEN ISNULL(f.[value], '')

ELSE ''

END,

字段序号 = a.colorder,

字段名称 = a.name,

类型 = b.name,

长度 = COLUMNPROPERTY(a.id, a.name, 'PRECISION'),

小数位数 = ISNULL(COLUMNPROPERTY(a.id, a.name, 'Scale'),0),

允许为空 = CASE WHEN a.isnullable = 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,

默认值 = ISNULL(e.text, ''),

占用字节数 = a.length,

标识 = CASE WHEN COLUMNPROPERTY(a.id, a.name, 'IsIdentity') = 1

THEN '√'

ELSE ''

END,

字段描述 = 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 b.name IS NOT NULL -- AND d.name='要查询的表'

-- 如果只查询表,加上 AND 条件

ORDER BY d.name, a.colorder;

-- 验证表数量，避免空表或表中没有ID为1的记录

SELECT name FROM sysobjects WHERE xtype='U';

Desen往事

简单生活，知足常乐

标签： SQL示例

查看 MSSQL 的审计日志

MySQL 存储过程示例

MSSQL 查询所有表结构