MSSQL – 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;

MSSQL 查询之 NOT IN & LEFT JOIN

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

NOT IN 方式查询

SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T2.points IS NULL OR T2.points = 0 )     -- 条件记录 3870358 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '')                         -- 条件记录 6286655 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.create_date <= '2020-03-01 00:00:00'  -- 条件记录 6731569 条
  -- 查询语句一耗时 7s, 返回记录 150681 条
UNION 
SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '' )                        -- 条件记录 6286656 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.uid NOT IN
    (
      SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 225 条
      SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 271 条
      SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 221 条
      SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 388 条
      SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 373 条
      SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 5986 条
      SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id =1 UNION  -- 子查询记录 23079 条
      SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id =1        -- 子查询记录 24095 条
       -- 子查询总耗时 1m14s, 返回记录 28411 条
    )  -- 条件记录 6711840 条
  -- 查询语句二耗时 1h13m52s, 返回记录 191067 条
ORDER BY
    T1.region,
    T1.streets,
    T1.village,
    T1.organize;
-- 总耗时 1h15m35s, 返回记录 191067 条

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T2.points IS NULL OR T2.points = 0 ) -- 条件记录 3870358 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '') -- 条件记录 6286655 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.create_date <= '2020-03-01 00:00:00' -- 条件记录 6731569 条

-- 查询语句一耗时 7s, 返回记录 150681 条

UNION

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '' ) -- 条件记录 6286656 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.uid NOT IN

(

SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 225 条

SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 271 条

SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 221 条

SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 388 条

SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 373 条

SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 5986 条

SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id =1 UNION -- 子查询记录 23079 条

SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id =1 -- 子查询记录 24095 条

-- 子查询总耗时 1m14s, 返回记录 28411 条

) -- 条件记录 6711840 条

-- 查询语句二耗时 1h13m52s, 返回记录 191067 条

ORDER BY

T1.region,

T1.streets,

T1.village,

T1.organize;

-- 总耗时 1h15m35s, 返回记录 191067 条

LEFT JOIN 方式查询

SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T2.points IS NULL OR T2.points = 0 )     -- 条件记录 3870358 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '')                         -- 条件记录 6286655 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T1.create_date <= '2020-03-01 00:00:00'  -- 条件记录 6731569 条
  -- 查询语句一耗时 7s, 返回记录 150681 条
UNION 
SELECT
    T1.region,
    T1.streets,
    T1.village,
    T1.organize,
    T1.uid,
    T2.point_id
  FROM member T1
  INNER JOIN member_info T2 ON T2.account_code = T1.uid
  LEFT JOIN
    (
      SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 225 条
      SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 271 条
      SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 221 条
      SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 388 条
      SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 373 条
      SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 5986 条
      SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id=1 UNION  -- 子查询记录 23079 条
      SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id=1        -- 子查询记录 24095 条
        -- 子查询耗时 1m14s, 返回记录 28411 条
    ) AS T3 ON T3.account_code=T1.uid
  WHERE
          T1.region = '华东'                       -- 条件记录 229873 条
    AND ( T1.user_name IS NULL
      OR  T1.user_name = ''
      OR  T1.card_id IS NULL
      OR  T1.card_id = '' )                        -- 条件记录 6286656 条
    AND   T1.user_status = '01'                    -- 条件记录 6634104 条
    AND   T3.id IS NULL                            -- 条件记录 6711856 条
ORDER BY
    T1.region,
    T1.streets,
    T1.village,
    T1.organize;
-- 总耗时 1m46s, 返回记录 191067 条

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T2.points IS NULL OR T2.points = 0 ) -- 条件记录 3870358 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '') -- 条件记录 6286655 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T1.create_date <= '2020-03-01 00:00:00' -- 条件记录 6731569 条

-- 查询语句一耗时 7s, 返回记录 150681 条

UNION

SELECT

T1.region,

T1.streets,

T1.village,

T1.organize,

T1.uid,

T2.point_id

FROM member T1

INNER JOIN member_info T2 ON T2.account_code = T1.uid

LEFT JOIN

(

SELECT DISTINCT(account_code) FROM point_list_202002 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 225 条

SELECT DISTINCT(account_code) FROM point_list_202001 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 271 条

SELECT DISTINCT(account_code) FROM point_list_201912 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 221 条

SELECT DISTINCT(account_code) FROM point_list_201911 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 388 条

SELECT DISTINCT(account_code) FROM point_list_201910 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 373 条

SELECT DISTINCT(account_code) FROM point_list_201909 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 5986 条

SELECT DISTINCT(account_code) FROM point_list_201908 WHERE region='华东' AND operate_id=1 UNION -- 子查询记录 23079 条

SELECT DISTINCT(account_code) FROM point_list_201907 WHERE region='华东' AND operate_id=1 -- 子查询记录 24095 条

-- 子查询耗时 1m14s, 返回记录 28411 条

) AS T3 ON T3.account_code=T1.uid

WHERE

T1.region = '华东' -- 条件记录 229873 条

AND ( T1.user_name IS NULL

OR T1.user_name = ''

OR T1.card_id IS NULL

OR T1.card_id = '' ) -- 条件记录 6286656 条

AND T1.user_status = '01' -- 条件记录 6634104 条

AND T3.id IS NULL -- 条件记录 6711856 条

ORDER BY

T1.region,

T1.streets,

T1.village,

T1.organize;

-- 总耗时 1m46s, 返回记录 191067 条

总结

UNION 与 WHERE 语句应用将返回记录小的条件最先进行匹配，以小表的记录去遍历大表耗时更短；
本次查询中 NOT IN 与 NOT EXISTS 耗时基本相同，由于查询字段存在唯一值、故无法通过索引优化；所以 NOT EXISTS 方式没有明显优势。

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往事

简单生活，知足常乐

标签： MSSQL

查看 MSSQL 的审计日志

MSSQL 查询之 NOT IN & LEFT JOIN

NOT IN 方式查询

LEFT JOIN 方式查询

总结

MSSQL 查询所有表结构