WSUS DB位置

WSUS的DB需要打以下位置進入
可用SSMS開啟

\\.\pipe\MSSQL$MICROSOFT##SSEE\sql\query

MSSQL SQL2005 無法複製表頭

SQL 2005無法將表頭複製出來
就是要設定內打勾才行

1. SSMS Tools > Options > Query Results > SQL Server > Result to Grid
2. Check > Include column headers when copying or saving to result

TSQL 顯示DB所有表格欄位+屬性

這段語法可以列出資料庫中所有表格的欄位名稱+屬性 可以快速檢查欄位狀況 還蠻方便的 SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME in ( Select Name From SysObjects Where XType='U' and name <> 'sysdiagrams') order by TABLE_NAME

SQL 更改DB名稱

exec sp_renamedb 'oldDBname','newDBname'

SQL 更改SQL SERVER 名稱

更新SQL名稱

exec sp_dropserver '舊的SQL名稱'
go

exec sp_addserver '新的SQL名稱', local
go


T-SQL Performance DeadLock 偵測+語法

自己參考各方文件寫的偵測
可以抓造成死結的源頭+語法

IF EXISTS(SELECT * FROM master.sys.sysprocesses WHERE spid
IN (SELECT blocked FROM master.sys.sysprocesses))
BEGIN
DECLARE @SPID binary(20),@Handle binary(20), @retVal varchar(8000)
SELECT @SPID =SPID FROM sysprocesses WHERE blocked = 0 AND SPID IN (SELECT DISTINCT blocked FROM sysprocesses)
SELECT @Handle = sql_handle
FROM master..sysprocesses
WHERE SPID = @SPID
SELECT @retVal = convert(varchar(8000), [text])
FROM ::fn_get_sql(@Handle)

SELECT SPID,status 'Status','UserPcName'=SUBSTRING(hostname,1,12),'LogInID'=SUBSTRING(SUSER_SNAME(sid),1,30),'IsLock'=CONVERT(char(3),blocked),
'DBName' = SUBSTRING(DB_NAME(dbid),1,20),cmd 'cmd',waittype 'waittype', Program = program_name, LastCommand = @retVal
FROM sysprocesses
WHERE blocked = 0
AND SPID IN (SELECT DISTINCT blocked FROM sysprocesses)
END
ELSE
SELECT 'Processes No Lock'


T-SQL xp_cmdshell

因為xp_cmdshell
如它名稱說的
可以用cmd語法去執行
這就很恐怖了
所以預設是關閉的
但是有時候要用到
就先把它打開囉

開啟

exec sp_configure 'show adv',1
reconfigure
exec sp_configure 'xp_cmdshell',1
reconfigure


關閉

exec sp_configure 'show adv',1
reconfigure
exec sp_configure 'xp_cmdshell',0
reconfigure


請小心服用+做好權限管理!!!!

SQL PK重註

由於PK用太久
會導致資訊越來越分散
也會影響SQL速度
所以MS有分享了一個方式可以讓人檢查
也可以重註PK方式

檢查方式

SELECT a.index_id, name, avg_fragmentation_in_percent, *
FROM sys.dm_db_index_physical_stats (DB_ID(), OBJECT_ID('MG_Strategic_Plan_MeasureGroup_default_partition'),
NULL, NULL, NULL) AS a
JOIN sys.indexes AS b ON a.object_id = b.object_id AND a.index_id = b.index_id

在MS提供文件說 avg_fragmentation_in_percent 不能超過30
超過表示太分散囉
要重註

重註語法

ALTER INDEX [PK_TableName]
ON dbo.[TableName]
REBUILD WITH (ONLINE = ON);


我是這樣弄的
再重新檢查一次
發現降下來或是變成0了
成功 ^_^

參考網站

T-SQL 清除SQL 記憶體

網路上看到 暫記一下

DBCC FREESYSTEMCACHE ('ALL');
DBCC FREESESSIONCACHE;
DBCC FREEPROCCACHE;


連結網站

T-SQL 用TSQL查詢CPU 重的SQL語法

這可以用來查詢
常常耗費CPU的SQL語法
雖然沒有Profiler那麼多資訊
但可以不需多開Profiler耗費資源
也可以找出耗費CPU的語法加以優化


USE AdventureWorks2008R2; --改成自己的DB
GO
SELECT TOP 5 query_stats.query_hash AS "Query Hash",
SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
MIN(query_stats.statement_text) AS "Statement Text"
FROM
(SELECT QS.*,
SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
((CASE statement_end_offset
WHEN -1 THEN DATALENGTH(ST.text)
ELSE QS.statement_end_offset END
- QS.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS QS
CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
GROUP BY query_stats.query_hash
ORDER BY 2 DESC;
GO



參考網站

SQL 刪除TABLE資料

紀錄一下

TRUNCATE TABLE "表格名"

可以將TABLE內資料殺光光外
還有自動產生編號會歸零
但是如果有被設定FK表格不能殺
所以要先將FK線拿掉後再殺再建

SQL SSMS2008 無法更新TABLE設計問題

有阿飄!!有阿飄!!! 別纏著我!!!

結果是死白目的SSMS2008有阿飄...
他有防止一些特殊的TABLE設計問題...
所以導致某些設計不能變更 (下指令可以)

解決方案
請至 SSMS2008的TOOLS > OPTIONS > DESIGNERS > 的
Prevent saving changes that require table re-creation
取消打勾就行了

阿飄終於走了 ㄟ(一 o 一 ㄟ)~~

SQL T-SQL 查詢連線人員

T-SQL 可以查詢連線人員有誰

查詢語法 sp_who

SQL Profiler 分析(1)

上網查了一下資料
發現了這個TSQL的語法可以查Profiler的效能
所以貼上來


SELECT * into #Temp
FROM ::fn_trace_gettable('D:\CGW\SQL Profiler\testGameDB81_100603.trc', default) --檔案路徑及名稱

-- 追蹤所得的資料本來就在資料表內，直接透過T-SQL 分析
SELECT SUBSTRING(TextData, 1,330) AS '名稱',
COUNT(*) '數量',
AVG(Duration) AS '平均執行時間(ms)',
AVG(CPU) AS '平均CPU 時間(ms)',
AVG(Reads) AS '平均閱讀次數',
AVG(Writes) AS '平均寫入次數'
FROM #Temp
WHERE EventClass=12 -- 12 代表SQL:BatchCompleted 事件
GROUP BY SUBSTRING(TextData, 1,330)
ORDER BY SUM(Duration) DESC


出處:http://blog.xuite.net/tim0718/note/21899219

SQL Performance Profiler Application Name

由於最近在學SQL Proformance 的東西
所以稍為紀錄一下學習過的東西囉

首先是Profiler這個SQL Performance內建軟體
簡單說 它就是在紀錄DB幹了啥好事的軟體
就是將所有動作記錄下來
可是勒
在這個欄位!!!Application Name 總是在連接時候都空的
找了一下資料後 才知道連接字串要把這個加入才會顯示
就是

Data Source=(local);Initial Catalog=DB;Integrated Security=True
Data Source=(local);Initial Catalog=DB;Integrated Security=True;Application Name=AppName

這樣要做分析 就會比較好分析喔

SQL T-SQL 觸發即時同步Table

有時候會用到Table要一模一樣 但是卻要寫兩次
這樣又很麻煩
所以弄了個T-SQL指令 可以同步讓兩個Table同步化
只要在觸發增加下面指令就行了
當然裡面表格跟寫法要自己設定一下
新增修改刪除下面都有 ^^


CREATE TRIGGER [dbo].[datacopy]
ON [dbo].[data]
AFTER INSERT
AS

BEGIN
--SET @name = SELECT a FROM inserted
DECLARE @n01 nvarchar(50),
@n02 nvarchar(50),
@n03 nvarchar(50),
@n04 nvarchar(50),
@n05 nvarchar(50),
@n06 nvarchar(50),
@n07 nvarchar(50),
@n08 nvarchar(50),
@n09 nvarchar(50),
@n10 nvarchar(50),
@n11 nvarchar(50),
@n12 nvarchar(50),
@n13 nvarchar(50),
@n14 nvarchar(50),
@n15 nvarchar(50),
@n16 nvarchar(50),
@n17 nvarchar(50),
@n18 nvarchar(50),
@n19 nvarchar(50),
@n20 nvarchar(50),
@n21 nvarchar(50),
@n22 nvarchar(50),
@n23 nvarchar(50),
@n24 nvarchar(50),
@n25 nvarchar(50),
@n26 nvarchar(50),
@n27 nvarchar(50),
@n28 nvarchar(50),
@n29 nvarchar(50),
@n30 nvarchar(50),
@n31 nvarchar(50),
@n32 nvarchar(50),
@n33 nvarchar(50),
@n34 nvarchar(50),
@n35 nvarchar(50),
@n36 nvarchar(50),
@n37 nvarchar(50),
@n38 nvarchar(50),
@n39 nvarchar(50),
@n40 nvarchar(50),
@n41 nvarchar(50),
@n42 nvarchar(50),
@n43 nvarchar(50),
@n44 nvarchar(50),
@n45 nvarchar(50),
@n46 nvarchar(50),
@n47 nvarchar(50),
@n48 nvarchar(50),
@n49 nvarchar(50),
@n50 nvarchar(50)
select @n01 = i.[n01],
@n02 = i.[n02],
@n03 = i.[n03],
@n04 = i.[n04],
@n05 = i.[n05],
@n06 = i.[n06],
@n07 = i.[n07],
@n08 = i.[n08],
@n09 = i.[n09],
@n10 = i.[n10],
@n11 = i.[n11],
@n12 = i.[n12],
@n13 = i.[n13],
@n14 = i.[n14],
@n15 = i.[n15],
@n16 = i.[n16],
@n17 = i.[n17],
@n18 = i.[n18],
@n19 = i.[n19],
@n20 = i.[n20],
@n21 = i.[n21],
@n22 = i.[n22],
@n23 = i.[n23],
@n24 = i.[n24],
@n25 = i.[n25],
@n26 = i.[n26],
@n27 = i.[n27],
@n28 = i.[n28],
@n29 = i.[n29],
@n30 = i.[n30],
@n31 = i.[n31],
@n32 = i.[n32],
@n33 = i.[n33],
@n34 = i.[n34],
@n35 = i.[n35],
@n36 = i.[n36],
@n37 = i.[n37],
@n38 = i.[n38],
@n39 = i.[n39],
@n40 = i.[n40],
@n41 = i.[n41],
@n42 = i.[n42],
@n43 = i.[n43],
@n44 = i.[n44],
@n45 = i.[n45],
@n46 = i.[n46],
@n47 = i.[n47],
@n48 = i.[n48],
@n49 = i.[n49],
@n50 = i.[n50]

from inserted i
--SELECT a FROM inserted

INSERT INTO [test2].[dbo].[data3]([n01],[n02],[n03],[n04],[n05],[n06],[n07],[n08],[n09],[n10],[n11],[n12],[n13],[n14],[n15],[n16],[n17],[n18],[n19],[n20],[n21],[n22],[n23],[n24],[n25],[n26],[n27],[n28],[n29],[n30],[n31],[n32],[n33],[n34],[n35],[n36],[n37],[n38],[n39],[n40],[n41],[n42],[n43],[n44],[n45],[n46],[n47],[n48],[n49],[n50]) VALUES(@n01,@n02,@n03,@n04,@n05,@n06,@n07,@n08,@n09,@n10,@n11,@n12,@n13,@n14,@n15,@n16,@n17,@n18,@n19,@n20,@n21,@n22,@n23,@n24,@n25,@n26,@n27,@n28,@n29,@n30,@n31,@n32,@n33,@n34,@n35,@n36,@n37,@n38,@n39,@n40,@n41,@n42,@n43,@n44,@n45,@n46,@n47,@n48,@n49,@n50)
END


XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXxx
CREATE TRIGGER [dbo].[datacopydel]
ON [dbo].[data]
AFTER DELETE
AS

BEGIN
--SET @name = SELECT a FROM inserted
DECLARE @n01 nvarchar(50),
@n02 nvarchar(50),
@n03 nvarchar(50),
@n04 nvarchar(50),
@n05 nvarchar(50),
@n06 nvarchar(50),
@n07 nvarchar(50),
@n08 nvarchar(50),
@n09 nvarchar(50),
@n10 nvarchar(50),
@n11 nvarchar(50),
@n12 nvarchar(50),
@n13 nvarchar(50),
@n14 nvarchar(50),
@n15 nvarchar(50),
@n16 nvarchar(50),
@n17 nvarchar(50),
@n18 nvarchar(50),
@n19 nvarchar(50),
@n20 nvarchar(50),
@n21 nvarchar(50),
@n22 nvarchar(50),
@n23 nvarchar(50),
@n24 nvarchar(50),
@n25 nvarchar(50),
@n26 nvarchar(50),
@n27 nvarchar(50),
@n28 nvarchar(50),
@n29 nvarchar(50),
@n30 nvarchar(50),
@n31 nvarchar(50),
@n32 nvarchar(50),
@n33 nvarchar(50),
@n34 nvarchar(50),
@n35 nvarchar(50),
@n36 nvarchar(50),
@n37 nvarchar(50),
@n38 nvarchar(50),
@n39 nvarchar(50),
@n40 nvarchar(50),
@n41 nvarchar(50),
@n42 nvarchar(50),
@n43 nvarchar(50),
@n44 nvarchar(50),
@n45 nvarchar(50),
@n46 nvarchar(50),
@n47 nvarchar(50),
@n48 nvarchar(50),
@n49 nvarchar(50),
@n50 nvarchar(50)
select @n01 = d.[n01],
@n02 = d.[n02],
@n03 = d.[n03],
@n04 = d.[n04],
@n05 = d.[n05],
@n06 = d.[n06],
@n07 = d.[n07],
@n08 = d.[n08],
@n09 = d.[n09],
@n10 = d.[n10],
@n11 = d.[n11],
@n12 = d.[n12],
@n13 = d.[n13],
@n14 = d.[n14],
@n15 = d.[n15],
@n16 = d.[n16],
@n17 = d.[n17],
@n18 = d.[n18],
@n19 = d.[n19],
@n20 = d.[n20],
@n21 = d.[n21],
@n22 = d.[n22],
@n23 = d.[n23],
@n24 = d.[n24],
@n25 = d.[n25],
@n26 = d.[n26],
@n27 = d.[n27],
@n28 = d.[n28],
@n29 = d.[n29],
@n30 = d.[n30],
@n31 = d.[n31],
@n32 = d.[n32],
@n33 = d.[n33],
@n34 = d.[n34],
@n35 = d.[n35],
@n36 = d.[n36],
@n37 = d.[n37],
@n38 = d.[n38],
@n39 = d.[n39],
@n40 = d.[n40],
@n41 = d.[n41],
@n42 = d.[n42],
@n43 = d.[n43],
@n44 = d.[n44],
@n45 = d.[n45],
@n46 = d.[n46],
@n47 = d.[n47],
@n48 = d.[n48],
@n49 = d.[n49],
@n50 = d.[n50]

from deleted d
--SELECT a FROM inserted
DELETE [test2].[dbo].[data3] WHERE [n01] = @n01
END



XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXx
CREATE TRIGGER [dbo].[datacopyupdate]
ON [dbo].[data]
AFTER UPDATE
AS

BEGIN
--SET @name = SELECT a FROM inserted
DECLARE @n01 nvarchar(50),
@n02 nvarchar(50),
@n03 nvarchar(50),
@n04 nvarchar(50),
@n05 nvarchar(50),
@n06 nvarchar(50),
@n07 nvarchar(50),
@n08 nvarchar(50),
@n09 nvarchar(50),
@n10 nvarchar(50),
@n11 nvarchar(50),
@n12 nvarchar(50),
@n13 nvarchar(50),
@n14 nvarchar(50),
@n15 nvarchar(50),
@n16 nvarchar(50),
@n17 nvarchar(50),
@n18 nvarchar(50),
@n19 nvarchar(50),
@n20 nvarchar(50),
@n21 nvarchar(50),
@n22 nvarchar(50),
@n23 nvarchar(50),
@n24 nvarchar(50),
@n25 nvarchar(50),
@n26 nvarchar(50),
@n27 nvarchar(50),
@n28 nvarchar(50),
@n29 nvarchar(50),
@n30 nvarchar(50),
@n31 nvarchar(50),
@n32 nvarchar(50),
@n33 nvarchar(50),
@n34 nvarchar(50),
@n35 nvarchar(50),
@n36 nvarchar(50),
@n37 nvarchar(50),
@n38 nvarchar(50),
@n39 nvarchar(50),
@n40 nvarchar(50),
@n41 nvarchar(50),
@n42 nvarchar(50),
@n43 nvarchar(50),
@n44 nvarchar(50),
@n45 nvarchar(50),
@n46 nvarchar(50),
@n47 nvarchar(50),
@n48 nvarchar(50),
@n49 nvarchar(50),
@n50 nvarchar(50)
select @n01 = i.[n01],
@n02 = i.[n02],
@n03 = i.[n03],
@n04 = i.[n04],
@n05 = i.[n05],
@n06 = i.[n06],
@n07 = i.[n07],
@n08 = i.[n08],
@n09 = i.[n09],
@n10 = i.[n10],
@n11 = i.[n11],
@n12 = i.[n12],
@n13 = i.[n13],
@n14 = i.[n14],
@n15 = i.[n15],
@n16 = i.[n16],
@n17 = i.[n17],
@n18 = i.[n18],
@n19 = i.[n19],
@n20 = i.[n20],
@n21 = i.[n21],
@n22 = i.[n22],
@n23 = i.[n23],
@n24 = i.[n24],
@n25 = i.[n25],
@n26 = i.[n26],
@n27 = i.[n27],
@n28 = i.[n28],
@n29 = i.[n29],
@n30 = i.[n30],
@n31 = i.[n31],
@n32 = i.[n32],
@n33 = i.[n33],
@n34 = i.[n34],
@n35 = i.[n35],
@n36 = i.[n36],
@n37 = i.[n37],
@n38 = i.[n38],
@n39 = i.[n39],
@n40 = i.[n40],
@n41 = i.[n41],
@n42 = i.[n42],
@n43 = i.[n43],
@n44 = i.[n44],
@n45 = i.[n45],
@n46 = i.[n46],
@n47 = i.[n47],
@n48 = i.[n48],
@n49 = i.[n49],
@n50 = i.[n50]

from inserted i
--SELECT a FROM inserted
UPDATE [test2].[dbo].[data3] SET [n02] = @n02,[n03] = @n03,[n04] = @n04,[n05] = @n05,[n06] = @n06,[n07] = @n07,[n08] = @n08,[n09] = @n09,[n10] = @n10,[n11] = @n11,[n12] = @n12,[n13] = @n13,[n14] = @n14,[n15] = @n15,[n16] = @n16,[n17] = @n17,[n18] = @n18,[n19] = @n19,[n20] = @n20,[n21] = @n21,[n22] = @n22,[n23] = @n23,[n24] = @n24,[n25] = @n25,[n26] = @n26,[n27] = @n27,[n28] = @n28,[n29] = @n29,[n30] = @n30,[n31] = @n31,[n32] = @n32,[n33] = @n33,[n34] = @n34,[n35] = @n35,[n36] = @n36,[n37] = @n37,[n38] = @n38,[n39] = @n39,[n40] = @n40,[n41] = @n41,[n42] = @n42,[n43] = @n43,[n44] = @n44,[n45] = @n45,[n46] = @n46,[n47] = @n47,[n48] = @n48,[n49] = @n49,[n50] = @n50 WHERE [n01] = @n01
END


[N01_OBSYSTEM].[dbo].[TB_Product_D]



SQL IP排序


SELECT *
FROM [Ip_Information]
order by cast(parsename(IP,4) as int),cast(parsename(IP,3) as int) ,cast(parsename(IP,2) as int),cast(parsename(IP,1) as int)


SQL SUM


SELECT [IP],SUM(InOctets) InOctets,SUM(OutOctets) OutOctets,SUM(TotalOctets) TotalOctets
FROM [HR].[dbo].[LAN_Snmp_Value]
GROUP BY [IP] --針對IP去總和


SQL 排序

WHERE後面加上

ORDER BY [LAN_IP.IP] DESC //由大到小
ORDER BY [LAN_IP.IP] ASC //由小到大


C# SQL SELECT


string constr = "Data Source=*****;Initial Catalog=*****;Integrated Security=True";

string str_select = "SELECT * FROM LAN_IP";

SqlConnection con = new SqlConnection(constr);
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(str_select, con);

da.Fill(ds,"LAN_IP");
DataTable datable = ds.Tables["LAN_IP"];
textBox1.Text= datable.Rows[1][4].ToString();