SQL Server, T-SQL, cmd, 常用指令, 查看連線數, 取出 procedure schema, tabled-valued function

checkout_conn.sql

-- 查看連線數
USE master
SELECT cntr_value AS User_Connections FROM sys.sysperfinfo AS sp
WHERE sp.object_name='SQLServer:General Statistics'
AND sp.counter_name='User Connections'
GO

-- 查看連線明細
USE master
SELECT c.session_id, c.connect_time, s.login_time, c.client_net_address, s.login_name, s.status
FROM sys.dm_exec_connections c left join sys.dm_exec_sessions s on c.session_id = s.session_id
GO

get_procedure_schema.sql

--- 列出 procedure 清單
SELECT *
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'PROCEDURE' 
   AND ROUTINE_NAME NOT IN (
'sp_upgraddiagrams',
'sp_helpdiagrams',
'sp_helpdiagramdefinition',
'sp_creatediagram',
'sp_renamediagram',
'sp_alterdiagram',
'sp_dropdiagram');

--- 列出 procedure 輸入參數
SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_NAME, DATA_TYPE
 FROM INFORMATION_SCHEMA.PARAMETERS
 WHERE SPECIFIC_NAME = '計算資產編號'
  AND SPECIFIC_SCHEMA = 'dbo'
  AND SPECIFIC_CATALOG = 'MineDB' 
 

--- 列出 procedure 執行結果第一筆的欄位型別，
--- 即可用於列出 procedure 輸出欄位
EXEC sp_describe_first_result_set N'計算資產編號'
EXEC sp_describe_first_result_set N'計算帳卡編號'

DECLARE @sp_describe_result Table
(
  column_ordinal INT,
  [name] NVARCHAR(500),
  is_nullable bit,
  system_type_Name NVARCHAR(500)
);

get_table_valued_function.sql

-- # 資料表質函式

SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE, *
  FROM INFORMATION_SCHEMA.ROUTINES
 WHERE ROUTINE_TYPE = 'FUNCTION'
  AND DATA_TYPE = 'TABLE'

--- 列出 procedure 輸入參數
SELECT SPECIFIC_CATALOG, SPECIFIC_SCHEMA, SPECIFIC_NAME, ORDINAL_POSITION, PARAMETER_NAME, DATA_TYPE
 FROM INFORMATION_SCHEMA.PARAMETERS
 WHERE SPECIFIC_NAME = '查詢電腦設備領用裝況'
  AND SPECIFIC_SCHEMA = 'dbo'
  AND SPECIFIC_CATALOG = 'MineDB'

SELECT COLUMN_NAME 
       ,ORDINAL_POSITION 
       ,TABLE_CATALOG
       ,TABLE_SCHEMA 
       ,TABLE_NAME 
       ,DATA_TYPE
       ,IS_NULLABLE 
 FROM INFORMATION_SCHEMA.ROUTINE_COLUMNS
 WHERE TABLE_NAME = '查詢電腦設備領用裝況'
  AND TABLE_SCHEMA = 'dbo'
  AND TABLE_CATALOG = 'MineDB'