ms-sql client connection test

cmd

telnet servername 1433

umbraco 刪除版本紀錄

DECLARE @createdDate Datetime = '2012-03-01'

DELETE FROM cmsPropertyData WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
    contentNodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsPreviewXml WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
    nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsContentVersion WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
    ContentId  IN (SELECT DISTINCT nodeID FROM cmsDocument)
DELETE FROM cmsDocument WHERE
    versionId NOT IN (SELECT versionId FROM cmsDocument WHERE updateDate > @createdDate OR published = 1 OR newest = 1) AND
    nodeId IN (SELECT DISTINCT nodeID FROM cmsDocument)

sql 取得各表資料欄位和大小

SELECT
    t.NAME AS TableName,
    --s.Name AS SchemaName,
    p.rows AS RowCounts,
    SUM(a.total_pages) * 8 /1024 AS TotalSpaceMB,
    SUM(a.used_pages) * 8 /1024 AS UsedSpaceMB,
    (SUM(a.total_pages) - SUM(a.used_pages)) * 8 /1024 AS UnusedSpaceMB
FROM
    sys.tables t
INNER JOIN    
    sys.indexes i ON t.OBJECT_ID = i.object_id
INNER JOIN
    sys.partitions p ON i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
INNER JOIN
    sys.allocation_units a ON p.partition_id = a.container_id
LEFT OUTER JOIN
    sys.schemas s ON t.schema_id = s.schema_id
WHERE
    t.NAME NOT LIKE 'dt%'
    AND t.is_ms_shipped = 0
    AND i.OBJECT_ID > 255
GROUP BY
    t.Name, s.Name, p.Rows
ORDER BY
    t.Name

清除資料庫版本

USE [資料庫名稱]

GO

ALTER DATABASE [資料庫名稱] SET RECOVERY SIMPLE WITH NO_WAIT

DBCC SHRINKFILE(記錄檔邏輯名稱, 1)

ALTER DATABASE [資料庫名稱] SET RECOVERY FULL WITH NO_WAIT

GO

SQL 備份 command

PRINT 'kmdn backup database '
declare @path nvarchar(max)
set @path = CONCAT('E:\data\kmdn_', FORMAT(GETDATE(),'yyyyMMdd'), '.bak')

BACKUP DATABASE [kmdn] TO DISK = @path WITH NOFORMAT, INIT, NAME = N'kmdn', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO

sql split 分割字串

參考資料:http://mydiamond.pixnet.net/blog/post/22415645-sql-split

閱讀更多 »

sql table 迴圈

SET NOCOUNT ON
DECLARE @docId VARCHAR(10)
DECLARE MYCURSOR CURSOR FOR

--下面放你要跑迴圈計算筆數的SQL
select docId from t1

OPEN MYCURSOR
FETCH NEXT FROM MYCURSOR INTO @docId
WHILE @@FETCH_STATUS = 0
BEGIN

--這邊放你要跑迴圈的 SQL
print('a')


FETCH NEXT FROM MYCURSOR INTO @docId
END
CLOSE MYCURSOR
DEALLOCATE MYCURSOR

SQL 遞迴

declare @CategoryId int = 1;
 
WITH T AS
(
SELECT [CategoryId],ParentID
, 0 AS Level
FROM [Category]
WHERE [CategoryId] = @CategoryId
UNION ALL
SELECT c.[CategoryId],c.ParentID, Level +1
FROM [Category] c
inner JOIN T t1
ON t1.[CategoryId] in (c.ParentID)
)
SELECT * FROM T;

SQL 多筆資料合併為一個欄位

參考網址:http://www.dotblogs.com.tw/kevinya/archive/2012/06/01/72553.aspx?fid=60981

SELECT cast(ProductID AS NVARCHAR ) + ',' from [Order] 
where OrderID = '10248'
FOR XML PATH('')

SQL 從別的table匯入特定欄位

INSERT INTO newTable (col1, col2, col3)
SELECT column1, column2, column3
FROM oldTable

sql 特定比數區域的查詢

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (ORDER BY id) as row FROM page
 ) a WHERE row > 10 and row < 20

sql 建立臨時 Table

CREATE TABLE dbo.#News
(
News_id int NOT NULL,
NewsTitle nvarchar(50),
NewsContent nvarchar(50),
NewsDateTime datetime
)
INSERT INTO dbo.#News (News_id, NewsTitle, NewsContent)
VALUES (1,'BlueGreen', 'Austen')

SELECT * FROM dbo.#News
DROP TABLE dbo.[#News]   --釋放資源

sql 迴圈

DECLARE @_i INT
DECLARE @_MAX INT
 SET @_i = 0
 SET @_MAX = 10 -- 要產生幾筆資料

 WHILE (@_i < @_MAX)
 BEGIN

print @_i

--加1
Set @_i = @_i + 1
 END

sql 預存函數

DECLARE @Account AS VARCHAR(10)
DECLARE @Password AS VARCHAR(10)
DECLARE @Catagory AS VARCHAR(10)

SET @Account = 'admin'
SET @Password = 'admin'

/**將傳入結果指派給@Catagory變數**/
SET @Catagory = (
SELECT CASE Catagory
WHEN 'admin' THEN 'admin'
ELSE 'company'
END
FROM UserRole WHERE Account=@Account AND [Password]=@Password
)

/**if else**/
IF(@Catagory) like '%company%'
BEGIN
SELECT * FROM UserRole WHERE [Year] = 101 AND Account=@Account AND [Password]=@Password

END
ELSE
BEGIN
SELECT * FROM UserRole WHERE Account=@Account AND [Password]=@Password

END

/**輸出@Catagory變數字串**/
PRINT @Catagory

尋找上下一筆資料

SELECT TOP 20 * FROM dbo.Orders

DECLARE @OrderID int
SET @OrderID = 10252

--1.求出目前這筆資料的時間
DECLARE @dt DateTime
SELECT @dt = OrderDate
FROM Orders
WHERE OrderID = @OrderID
--2.使用時間來做比對，再透過排序的方式，來選出上一筆、下一筆資料
SELECT  * FROM
(
-- 找出上一筆資料
SELECT Top 1 OrderID,OrderDate FROM Orders 
WHERE OrderDate >= @dt 
  AND OrderID != @OrderID
ORDER BY OrderDate ASC
)[Previous]
UNION
--3.再將資料表連結起來(此步驟非必要步驟)  
SELECT  * FROM
(
-- 找出下一筆資料
SELECT Top 1 OrderID,OrderDate FROM Orders 
WHERE OrderDate <= @dt 
  AND OrderID != @OrderID
ORDER BY OrderDate DESC
)[Next]

參考資料:http://www.dotblogs.com.tw/dotjum/archive/2008/10/20/5729.aspx