MSSQL好用SQL

查詢MSSQL欄位名稱/欄位型別及長度/描述欄位

SELECT a.Table_schema +'.'+a.Table_name   as 表格名稱   
       ,b.COLUMN_NAME                     as 欄位名稱   
       ,b.DATA_TYPE                       as 資料型別   
       ,isnull(b.CHARACTER_MAXIMUM_LENGTH,'') as 長度   
       ,isnull(b.NUMERIC_PRECISION ,'') as 整數位數   
       ,isnull(b.NUMERIC_SCALE  ,'') as 小數位數   
       ,isnull(b.COLUMN_DEFAULT,'')           as 預設值   
       ,case when b.IS_NULLABLE='YES' then 'Y' else 'N' end as 是否允許空值   
       ,( SELECT value   
          FROM fn_listextendedproperty (NULL, 'schema', a.Table_schema, 'table', a.TABLE_NAME, 'column', default)   
          WHERE name='MS_Description' and objtype='COLUMN'    
          and objname Collate Chinese_Taiwan_Stroke_CI_AS = b.COLUMN_NAME   
        ) as 欄位描述   
FROM INFORMATION_SCHEMA.TABLES  a   
 LEFT JOIN INFORMATION_SCHEMA.COLUMNS b ON a.TABLE_NAME = b.TABLE_NAME   
WHERE TABLE_TYPE='BASE TABLE'
  and a.TABLE_NAME ='TableName'
ORDER BY a.TABLE_NAME , b.ORDINAL_POSITION

查詢Index

SELECT obj.name TableName, ind.name IndexName, col.name IndexColumn, incol.index_column_id IndexColumnSN
FROM sys.columns col
INNER JOIN sys.objects obj ON obj.object_id = col.object_id
INNER JOIN sys.index_columns incol ON incol.COLUMN_ID = col.column_id AND incol.object_id = obj.object_id
INNER JOIN sys.indexes ind on ind.index_id = incol.index_id AND obj.object_id = ind.object_id
WHERE obj.name = 'TableName'
order by ind.name, incol.index_column_id

查詢Table的PK欄位

SELECT COLUMN_NAME
     , ORDINAL_POSITION
     , COLUMNPROPERTY(OBJECT_ID(TABLE_NAME), COLUMN_NAME, 'IsIdentity') AS IsIdentity  --自動識別欄位(0:不是,1:是)
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE OBJECTPROPERTY(OBJECT_ID(CONSTRAINT_NAME), 'IsPrimaryKey') = 1
   AND TABLE_NAME = 'TableName'

查TABLE筆數(非即時)

select * 
from (
SELECT      SCHEMA_NAME(A.schema_id) + '.' + A.Name TableName, AVG(B.rows) AS 'RowCount1'
FROM        sys.objects A
INNER JOIN sys.partitions B ON A.object_id = B.object_id
WHERE       A.type = 'U'
GROUP BY    A.schema_id, A.Name
) AA
order by AA.RowCount1 desc

查DB定序

SELECT DATABASEPROPERTYEX ('DBName' ,'Collation' )

lock data row

BEGIN TRAN

SELECT * 
FROM A_1 WITH(XLOCK) 
WHERE A = 1

WAITFOR DELAY '00:00:10'

ROLLBACK TRAN

XML PATH & STUFF

SELECT DISTINCT busid , drivername , insurlistnum 
    , STUFF( (SELECT ', ' + insurchildname 
              from @T1 FOR XML PATH('')), 1, 1, '') AS [資料] 
FROM @T1;

查詢耗時SQL

SELECT TOP 2000 
       ST.text AS '執行的SQL語句',
       QS.execution_count AS '執行次數',
       QS.total_elapsed_time AS '耗時',
       QS.total_logical_reads AS '邏輯讀取次數',
       QS.total_logical_writes AS '邏輯寫入次數',
       QS.total_physical_reads AS '物理讀取次數',       
       QS.creation_time AS '執行時間' ,  
       QS.*
FROM   sys.dm_exec_query_stats QS
       CROSS APPLY 
sys.dm_exec_sql_text(QS.sql_handle) ST
WHERE  QS.creation_time BETWEEN '2022-12-17 00:00:00' AND '2022-12-17 23:00:00' 
ORDER BY
     QS.total_elapsed_time DESC

執行中SQL

SELECT      r.scheduler_id as 排程器識別碼,
            status         as 要求的狀態,
            r.session_id   as SPID,
            r.blocking_session_id as BlkBy,
            substring(
                ltrim(q.text),
                r.statement_start_offset/2+1,
                (CASE
                 WHEN r.statement_end_offset = -1
                 THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
                 ELSE r.statement_end_offset
                 END - r.statement_start_offset)/2)
                 AS [正在執行的 T-SQL 命令],
            q.text [完整的T-SQL指令碼], /* 完整的 T-SQL 指令碼 */
            r.cpu_time      as [CPU Time(ms)],
            r.start_time    as [開始時間],
            r.total_elapsed_time as [執行總時間],
            r.reads              as [讀取數],
            r.writes             as [寫入數],
            r.logical_reads      as [邏輯讀取數],
            d.name               as [資料庫名稱]
FROM        sys.dm_exec_requests r 
            CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
            LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE       r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY    r.total_elapsed_time desc