SQL維護計畫手動刪除錯誤

--找出 維護計畫的ID
use msdb;
DECLARE @ID VarChar(max)
SET @ID=(select id from sysmaintplan_plans WHERE name='MaintenancePlan');

--刪除維護計畫相關Table
delete from sysmaintplan_log where plan_id = @ID
delete from sysmaintplan_subplans where plan_id = @ID
delete from sysmaintplan_plans where id = @ID

SQL多來源Table+輸出必須排序

SELECT *
FROM
(
SELECT TOP (99.999999999) PERCENT
FLD1
FROM Table1
ORDER BY FLD1
) TT

SQL大量更新資料前停用索引

--停用索引
ALTER INDEX ALL ON Table1 Disable
ALTER INDEX PK_Tabl1 ON Table1 REBUILD

--大量更新
UPDATE Table1 SET FLD1=1

--啟用索引
ALTER INDEX ALL ON Table1 REBUILD

SQL隨機撈取十筆+產生未來一年內任一天

--隨機撈取十筆
SELECT TOP 10 FLD1
 FROM Table1
 ORDER BY NEWID()

--隨機產生未來一年內某一日
SELECT CONVERT(VarChar(10), DATEADD(d,CAST(RAND(DATEPART(ms, GETDATE()))*36500 AS INT)%365, GETDATE()),111)

--隨機產生未來半年內某一日
SELECT CONVERT(VarChar(10), DATEADD(d,CAST(RAND(DATEPART(ms, GETDATE()))*18000 AS INT)%180, GETDATE()),111)

SQL撈取Table欄位名稱

CREATE FUNCTION ufn_GetTableField(@TABLE VarChar(max))
RETURNS NVarChar(max)
AS
BEGIN
DECLARE @RESULT NVarChar(max)
SET @RESULT=
(
SELECT LEFT(NAMES,LEN(NAMES)-1) AS FLD_LIST
FROM
(
SELECT
(
select name+','
from syscolumns
where id=object_id(@TABLE)
--ORDER BY name
FOR XML PATH('')
) AS NAMES
) T
)
RETURN(@RESULT);
END

SQL 撈取PK欄位名稱

SELECT  i.name AS IndexName,
        OBJECT_NAME(ic.object_id) AS TableName,
        COL_NAME(ic.object_id,ic.column_id) AS ColumnName
FROM    sys.indexes AS i INNER JOIN 
        sys.index_columns AS ic ON  i.object_id = ic.object_id
                                AND i.index_id = ic.index_id
WHERE   i.is_primary_key = 1;