Kimxinfo Obsidian

    SqlServer

    基本資訊

    • 清理Log
    --https://itorz324.blogspot.com/2019/09/sql-server-logbig-ldf-file.html
USE MTG_Financial_DEV;
GO
-- changing the database recovery model to simple.
ALTER DATABASE MTG_Financial_DEV
SET RECOVERY SIMPLE;
GO
-- Shrink UserDB_log file to 20 MB.
DBCC SHRINKFILE (MTG_Financial_log, 20);
GO
-- changing the database recovery model to FULL.
ALTER DATABASE MTG_Financial_DEV
SET RECOVERY FULL;
GO

    查詢預存的資訊

    --查詢預存程序的參數（Parameters）
SELECT
    p.name AS ParameterName,
    TYPE_NAME(p.user_type_id) AS DataType,
    p.max_length,
    p.is_output
FROM sys.parameters p
INNER JOIN sys.objects o ON p.object_id = o.object_id
WHERE o.type = 'P'  -- 'P' 表示 Stored Procedure
  AND o.name = 'VCALENDAR_Query'
  AND SCHEMA_NAME(o.schema_id) = 'dbo'

--查詢預存程序的回傳欄位資訊（結果集欄位）
--ps: 動態的SQL無法使用
SELECT 
    name AS ColumnName,
    system_type_name AS DataType,
    is_nullable
FROM sys.dm_exec_describe_first_result_set(N'dbo.VCALENDAR_Query', NULL, 0);
    • 針對預存沒權限處理 @George
      • EXEC dev.DBA_Grant_Permissions 'ERP_DataAccess', 1

    資料庫加入使用者

    USE [GuruERP_Center] GO CREATE USER [CorexERP_WebAppUser] FOR LOGIN [CorexERP_WebAppUser] GO USE [GuruERP_Center] GO ALTER ROLE [db_datareader] ADD MEMBER [CorexERP_WebAppUser] GO USE [GuruERP_Center] GO ALTER ROLE [db_datawriter] ADD MEMBER [CorexERP_WebAppUser] GO

    效能