《CGW》資訊and程式書: T-SQL

顯示具有 T-SQL 標籤的文章。顯示所有文章

2012年11月8日星期四

Bat Bat執行TSQL指令

可以使用sqlcmd來執行裡面有很多設定方式
也可以用引入 .sql檔案來執行
以下是簡單的直接下法

sqlcmd -S ServerName -d DBName -U User -P Password -Q "UPDATE [DB].[dbo].[Admin] SET [Level] = 1 WHERE [UserName] = 'abc'"

2012年4月17日星期二

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

2011年4月25日星期一

SQL 更改DB名稱

exec sp_renamedb 'oldDBname','newDBname'

2011年4月11日星期一

SQL 更改SQL SERVER 名稱

更新SQL名稱


exec sp_dropserver '舊的SQL名稱'
go

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

2010年11月25日星期四

T-SQL Chinese_Taiwan_Stroke_CI_AS

剛剛發生這個錯誤
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Chinese_Taiwan_Stroke_CI_AS" in the equal to operation.

找了一下
說要再等於雙方加入這個就行了
collate Chinese_Taiwan_Stroke_CI_AS


--[PersonID] = [ID]    --舊的方式 改成下面這個
[PersonID] collate Chinese_Taiwan_Stroke_CI_AS = [ID] collate Chinese_Taiwan_Stroke_CI_AS

2010年11月21日星期日

T-Sql Pivot 樞紐

跳來跳去的 XDDD
換成T-SQL的方法啦


Select * from 
(
 SELECT CONVERT(DATE,[Main].[Date]) as xxx,COUNT([ProjectNo]) as ZZZ,[ProjectNo] ,COUNT(*) AS Cnt
  FROM [Main]
  group by CONVERT(DATE,[Main].[Date]) ,[ProjectNo]
  ) as S
PIVOT
(
    SUM(Cnt)
    FOR [ProjectNo] IN 
    ([1],[3],[5],[7],[9])
) AS PivotTable

雖然能跑
但是出來GROUPBY 有錯
群組有錯
導致現在嚴重卡點...
他的值是對的
但不是我要的
所以先紀錄一下 = =+

2010年11月3日星期三

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'

2010年10月19日星期二

C# Linq to Sql 寫T-Sql語法

雖然Linq很強大
但是有些特殊部份無法使用 T_T
所以還是要回歸到最基礎的SQL語法來寫
以下就是寫法


using (LinqDataContext db = new LinqDataContext(Context))
                {
                    db.Log = Console.Out;
                    var q = db.ExecuteQuery ＜Class＞(@"EXEC synonyms N'select * from [user]'");    //雙引號內為T-SQL語法
                }

像我語法內主要是用在Synonyms用在Linked Servers上
因為查了一下找不到怎樣在Linq上做Linked Server的查詢法
又在不能增加Stored Procedures的狀況下
所以就只好直接用T-Sql語法直接硬上!!! XDDD

2010年10月4日星期一

T-SQL With

因為接觸 T-SQL不久
所以有些用法不太認識
所以紀錄一下簡單的東西

With 用法在做一個虛擬TABLE
但是這個虛擬TABLE只能被使用一次就消失了

大概用法是


With TableName(ColumnName) as (Select XXX From OOO)

2010年10月3日星期日

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

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

2010年9月29日星期三

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了
成功 ^_^

參考網站

2010年9月27日星期一

TSQL INPUTBUFFER

暫記一下
列出目前執行的狀態


select * from sys.sysprocesses
dbcc INPUTBUFFER (17)   --17是編號

2010年8月30日星期一

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

參考網站

2010年8月24日星期二

SQL 刪除TABLE資料

紀錄一下

TRUNCATE TABLE "表格名"

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

2010年8月18日星期三

SQL 使用者查詢

在T-SQL內可以查詢使用者資訊
暫記


use [資料庫名稱]
exec sp_helpuser

use [資料庫名稱]
exec sp_helprolemember 'db_owner'

use [資料庫名稱]
select * from sys.sql_logins

use master
EXEC sp_helpuser

use master
exec sp_helprolemember 'db_owner'

use master
select * from sys.sql_logins

2010年7月1日星期四

SQL T-SQL 查詢連線人員

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

查詢語法 sp_who

2010年6月2日星期三

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]

訂閱：文章 (Atom)

《CGW》資訊and程式書

2012年11月8日星期四

Bat Bat執行TSQL指令

2012年4月17日星期二

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

2011年4月25日星期一

SQL 更改DB名稱

2011年4月11日星期一

SQL 更改SQL SERVER 名稱

2010年11月25日星期四

T-SQL Chinese_Taiwan_Stroke_CI_AS

2010年11月21日星期日

T-Sql Pivot 樞紐

2010年11月3日星期三

T-SQL Performance DeadLock 偵測+語法

2010年10月19日星期二

C# Linq to Sql 寫T-Sql語法

2010年10月4日星期一

T-SQL With

2010年10月3日星期日

T-SQL xp_cmdshell

2010年9月29日星期三

SQL PK重註

2010年9月27日星期一

TSQL INPUTBUFFER

2010年8月30日星期一

T-SQL 清除SQL 記憶體

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

2010年8月24日星期二

SQL 刪除TABLE資料

2010年8月18日星期三

SQL 使用者查詢

2010年7月1日星期四

SQL T-SQL 查詢連線人員

2010年6月2日星期三

SQL T-SQL 觸發即時同步Table

關於我自己

標籤

網誌存檔

總網頁瀏覽量

2012年11月8日 星期四

2012年4月17日 星期二

2011年4月25日 星期一

2011年4月11日 星期一

2010年11月25日 星期四

2010年11月21日 星期日

2010年11月3日 星期三

2010年10月19日 星期二

2010年10月4日 星期一

2010年10月3日 星期日

2010年9月29日 星期三

2010年9月27日 星期一

2010年8月30日 星期一

2010年8月24日 星期二

2010年8月18日 星期三

2010年7月1日 星期四

2010年6月2日 星期三

關於我自己

標籤

網誌存檔

總網頁瀏覽量

2012年11月8日星期四

2012年4月17日星期二

2011年4月25日星期一

2011年4月11日星期一

2010年11月25日星期四

2010年11月21日星期日

2010年11月3日星期三

2010年10月19日星期二

2010年10月4日星期一

2010年10月3日星期日

2010年9月29日星期三

2010年9月27日星期一

2010年8月30日星期一

2010年8月24日星期二

2010年8月18日星期三

2010年7月1日星期四

2010年6月2日星期三