MSSQL好用SQL

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

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

查詢Index

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

查詢Table的PK欄位

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

查TABLE筆數(非即時)

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

查DB定序

  1. SELECT DATABASEPROPERTYEX ('DBName' ,'Collation' )

lock data row

  1. BEGIN TRAN
  2. SELECT *
  3. FROM A_1 WITH(XLOCK)
  4. WHERE A = 1
  5. WAITFOR DELAY '00:00:10'
  6. ROLLBACK TRAN

XML PATH & STUFF

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

查詢耗時SQL

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

執行中SQL

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

 

 

留言

這個網誌中的熱門文章

ORA-12514: TNS: 監聽器目前不知道連線描述區中要求的服務

Oracle 工作排程 DBMS_JOB 筆記

Oracle 例外控制(Exception Control)