Listing space used information in each database files

Listing space used information in each database files

-- SQL Server 2000請將 sys.databases改成sysdatabases
-- 第欄是資料使用大小單位是KB

CREATE TABLE #db_space
(
[DBname] NVARCHAR(50),
[Fileid] NVARCHAR(10),
[Filegroup] NVARCHAR(10),
[TotalExtents] int,
[UsedExtents] int,
[Name] NVARCHAR(50),
[FileName] NVARCHAR(300),
);
GO

DECLARE @name sysname
DECLARE cur cursor for SELECT [name] FROM sys.databases WHERE [name] not in ('master','msdb','tempdb','model')
OPEN cur
FETCH cur INTO @name
WHILE @@fetch_status = 0
BEGIN
--Print @name
BEGIN TRAN
INSERT INTO #db_space([Fileid],[Filegroup],[TotalExtents],[UsedExtents],[Name],[FileName])
EXEC('USE '+@name+' ;DBCC SHOWFILESTATS;');
COMMIT TRAN

BEGIN TRAN
UPDATE #db_space SET [DBname] = @name WHERE [DBname] is NULL;
COMMIT TRAN

FETCH cur INTO @name
END
CLOSE cur
DEALLOCATE cur
go
SELECT
[UsedExtents]*64 as [Used KBytes]
,*
FROM #db_space ORDER BY [DBname]
GO
DROP TABLE #db_space
GO

Oracle Create Table ,Modify Table schema (CREAT TABLE,ALTER TABLE)

-- Create a table using Create AS
CREATE TABLE HR.EMPLOYEE_backup
AS SELECT * FROM HR.EMPLOYEE;


-- Add a column
ALTER TABLE HR.EMPLOYEE
ADD
(
givenName NVARCHAR2(50) ,
address NVARCHAR2(100) NOT NULL,
age number
);

-- Modify data type, add (NOT NULL or NULL)
ALTER TABLE HR.EMPLOYEE
MODIFY
(
givenName varchar2(100) NOT NULL,
address varchar2(20) NULL
);

-- Modify column name
ALTER TABLE HR.EMPLOYEE
rename column address to address2;

-- Drop a column
ALTER TABLE META.SYNCGRP
DROP
(
givenName
);

Pocket PC推薦軟體Skype

1.連結到Skype網站下載安裝檔CAB檔
http://www.skype.com/download/skype/windowsmobile/



2.傳輸到Pocket PC後執行安裝

Pocket PC推薦軟體MDict

安裝與下載請參考mobile01的教學
http://www.mobile01.com/print.php?f=129&t=140305&p=1&v=1023170

MDict網站
http://www.octopus-studio.com/download.en.htm

Pocket PC安裝Thirth party軟體

1.CAB安裝檔
將安裝檔傳輸到Pocket PC上直接安裝

2.EXE安裝檔
(1)安裝檔下載到PC上
(2)用USB傳輸線將Pocket PC連接到PC
(3)在PC上執行EXE安裝檔
(4)PC上執行後,Pocket PC若跳出安裝畫面,則再改到Pocket PC繼續安裝

推薦軟體 MDict :翻譯軟體

-------------------------

SQL 2000 Determine primary key from system table 列出所有Primary Key

SQL 2000 Determine primary key from system table 列出所有Primary Key

 SELECT object_name(a.[id]) as [TableName]
,object_name(a.constid) as [PK_name]
,b.[name] as [PK_Column]
FROM sysconstraints a inner join syscolumns b
on a.[colid]+1 = b.[colid] and a.[id] = b.[id]
WHERE a.status = 2593

PS. Status=2593還未找到確認的文件說明,另外覆合Key可能不適用

MySQL Privileges 快速權限設定

MySQL系統資料表
1.mysql.user 登入帳戶與全域設定權限
2.mysql.tables_priv 資料表權限

# 查詢現有的MySQL帳戶
select host,user from mysql.user;

# 新增帳戶指定來源IP,並給予資料表所有權限
grant all privileges on Database.TableName to 'NewUser'@'192.168.1.1' Identified by 'Password';

# 新增帳戶不指定來源IP,並給予資料表所有權限,任何IP都可以連線到這台MySQL
grant all privileges on Database.TableName to 'NewUser'@'%' Identified by 'Password';

# 已經有record在mysql.user裡則不用在指定密碼,直接給予權限
grant select, insert,update,delete on Database.TableName to 'NewUser'@'%';

# 移除權限,此命令無法刪除帳戶,欲刪除帳戶必須使用delete
revoke delete on Database.TableName from 'NewUser'@'%'

# 移除帳戶
delete from mysql.user where user='NewUser'@'%'

# 更新系統的權限設定,修改或新增任務權限設定都必須要下一次flush privileges才能讓設定生效
flush privileges;

Dedicated administrator connection(DAC)專用管理員連線

今晚翻書時剛好看到SQL 2005的DAC專用管理員連線功能,就順便測試一下這個功能

[DAC的作用]
當SQL Server已忙碌到無法回應連線請求時,仍然會保留一些資源留下一個給管理員的專用連線

[啟動DAC]
DAC預設是關閉的,以下説明如何開啟它
啟動SQL Server 介面區組態->功能的介面區組態->DAC->勾選[啟用遠端DAC]



[連線到DAC]
使用SQL Server Management Studio
1.啟動SQL Server Management Studio,跳出第一個連線視窗請關閉它
2.點一下[新增查詢]按鈕,會跳出第二個連線視窗
3.在 [伺服器名稱] 輸入 admin:後面接伺服器執行個體名稱,預設執行個體則輸入admin:電腦名稱



若使用sqlcmd則搭配-A參數,-S後面連接的是執行個體


參考資料

SQL Server 2005 線上叢書 (2007 年 9 月) 使用專用管理員連接

http://msdn.microsoft.com/zh-tw/library/ms189595.aspx

Visual Studio 2005 Trial and Free Edition

使用ISO檔燒成光碟可離線安裝

Express 免費版下載ISO:
Visual Basic 2005 Express ISO

Visual Web Developer 2005 Express ISO

SQL Server 2005 Express ISO

SP1 For Express
Microsoft® Visual Studio® 2005 Express Editions 的 Service Pack 1


Professional 90天試用版
Visual Studio 2005 Professional 90 天試用版

Team Suite 180天試用版
Visual Studio 2005 Team Suite 180 天試用版

Service Pack 1 For Standard , Professional, Team
Microsoft® Visual Studio® 2005 Standard, Professional, Team Editions 的 Service Pack 1

Microsoft Development Library文件庫

MSDN文件庫
繁體中文
http://msdn.microsoft.com/zh-tw/library/default.aspx
英文
http://msdn.microsoft.com/en-us/library/default.aspx


TechNet Library
http://technet.microsoft.com/zh-tw/library/default.aspx

http://technet.microsoft.com/en-us/library/default.aspx

Performance Tuning (IIS 6.0)

微軟官方文件
Technet Library
Performance Tuning (IIS 6.0)

http://www.microsoft.com/technet/prodtechnol/WindowsServer2003/Library/IIS/71490aae-f444-443c-8b2a-520c2961408e.mspx?mfr=true