我就是我，這是我的世界！: 1月 2013

星期四, 1月 17, 2013

取得各個Table的列數等資料

最討厭的事情發生了，正式環境的資料庫版本比我們在使用的開發及測試環境還高，這時候就累人了，當要把正式環境的資料備份到測試環境時，又是一個浩大的工程，Google 後發現，降級還原是所有人的惡夢呀，產生 Script 之後來導入資料，SQL 檔都超過 1G，SSMS根本就開不起來，最後我決定只針對新增的資料進行匯入就好，於是需要下列這一段了，找出各個 Table 的列數後，我只需要比對後針對有新增的資料進行直接進行匯入即可，修改的資料就不管它了，哈哈～～～～

SET NOCOUNT ON 

--http://msdn.microsoft.com/zh-tw/library/ms188414.aspx
--更新目前資料庫中之所有物件的頁面及 (或) 資料列計數
DBCC UPDATEUSAGE(0) 

-- DB size.
EXEC sp_spaceused

-- Table row counts and sizes.
CREATE TABLE #t 
( 
    [name] NVARCHAR(128),
    [rows] CHAR(11),
    reserved VARCHAR(18), 
    data VARCHAR(18), 
    index_size VARCHAR(18),
    unused VARCHAR(18)
) 
 
--把每個Table使用的資訊存到#t之中
INSERT #t EXEC sys.sp_MSforeachtable 'EXEC sp_spaceused ''?''' 

--依使用空間較大的依序排列並顯示MB
SELECT *
, LTRIM(STR(CAST(LEFT(reserved,LEN(reserved)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB'

AS reservedSize_M
, LTRIM(STR(CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB'

AS dataSize_M
, LTRIM(STR(CAST(LEFT(index_size,LEN(index_size)-3) AS NUMERIC(18,0)) / 1024, 18)) + 'MB'

AS indexSize_M
FROM #t
ORDER BY CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0)) DESC

-- 顯示總共筆數及總共使用資訊
SELECT SUM(CAST([rows] AS int)) AS [rows]
, LTRIM(STR(SUM(CAST(LEFT(reserved,LEN(reserved)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB'

AS sumOfreservedSize_M
, LTRIM(STR(SUM(CAST(LEFT(data,LEN(data)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB'

AS sumOfdataSize_M
, LTRIM(STR(SUM(CAST(LEFT(index_size,LEN(index_size)-3) AS NUMERIC(18,0))) / 1024, 18)) + 'MB'

AS sumOfindexSize_M
FROM #t
 
DROP TABLE #t

出處: http://www.dotblogs.com.tw/rainmaker/archive/2012/02/02/67498.aspx

星期五, 1月 11, 2013

看看SQL Server在跑什麼SQL

最近常常發生sp_who2看到一堆連線，有些還在跑，這時候想要把它Kill掉前就一定要檢查一下它在執行什麼了，偏偏sp_who2就只能看到正在跑，看不到執行什麼指令，還好網路上真的是一個很方便的地方，馬上就找到指令了

SELECT r.scheduler_id as 排程器識別碼,
status as 要求的狀態,
r.session_id as SPID,
r.blocking_session_id as BlkBy,
substring(
ltrim(q.text),
r.statement_start_offset/2+1,
(CASE
WHEN r.statement_end_offset = -1
THEN LEN(CONVERT(nvarchar(MAX), q.text)) * 2
ELSE r.statement_end_offset
END - r.statement_start_offset)/2)
AS [正在執行的 T-SQL 命令],
r.cpu_time as [CPU Time(ms)],
r.start_time as [開始時間],
r.total_elapsed_time as [執行總時間],
r.reads as [讀取數],
r.writes as [寫入數],
r.logical_reads as [邏輯讀取數],
-- q.text, /* 完整的 T-SQL 指令碼 */
d.name as [資料庫名稱]
FROM sys.dm_exec_requests r
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS q
LEFT JOIN sys.databases d ON (r.database_id=d.database_id)
WHERE r.session_id > 50 AND r.session_id <> @@SPID
ORDER BY r.total_elapsed_time desc

結果在執行的時後會發生sql_handle錯誤，原來這指令只能在SQL Server 2005以上使用，但是我們的資料庫相容性設定為SQL Server 2000，所以將資料庫相容性設定為SQL Server 2005就可以執行了。

出處: http://blog.miniasp.com/post/2010/10/13/How-to-get-current-executing-statements-in-SQL-Server.aspx

星期三, 1月 09, 2013

找出資料庫中自動編號的欄位名稱與資料表名稱

最近在做資料庫的移轉，因為資料庫中的東綁西綁的，導致無法很簡單的完成，最後只好土法煉鋼，先將結構建立好後，然後用INSERT INTO去把資料轉過去，討厭的是還有很多自動編號的欄位會造成錯誤，這部份就只好特別挑出來處理，首先就要先找出有哪些Table中有自動編號的欄位了

SELECT QUOTENAME(t.TABLE_SCHEMA) + '.' + QUOTENAME(t.TABLE_NAME)  AS TableName,
  c.COLUMN_NAME AS ColumnName,
  c.DATA_TYPE AS 'DataType',
  IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) AS CurrentIdentityValue,
  CASE c.DATA_TYPE
   WHEN 'bigint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 9223372036854775807
   WHEN 'int' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 2147483647
   WHEN 'smallint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 32767
   WHEN 'tinyint' THEN (IDENT_CURRENT(t.TABLE_SCHEMA  + '.' + t.TABLE_NAME) * 100.) / 255
  END AS 'PercentageUsed'
 FROM INFORMATION_SCHEMA.COLUMNS AS c
  INNER JOIN
  INFORMATION_SCHEMA.TABLES AS t
  ON c.TABLE_SCHEMA = t.TABLE_SCHEMA AND c.TABLE_NAME = t.TABLE_NAME
 WHERE COLUMNPROPERTY(OBJECT_ID(t.TABLE_SCHEMA + '.' + t.TABLE_NAME), c.COLUMN_NAME, 'isIdentity') = 1

出處：http://wushinetlife.blogspot.tw/2012/03/blog-post_15.html