テクニック一覧
- テーブルをコピーするには
- テーブルをコピーするには2
- プロシージャの従属関係を表示するには
- エラーの詳細を表示するには
- SQLで指定したファイルを実行するには
- サーバー名を取得するには
- SQL Serverのバージョン情報を取得するには
- 指定したテーブルの項目名を取得するには
- 抽出結果をバイナリで取得するには
- セッションによって別名の仮表を作成するには
- SQLコマンドからデタッチ・アタッチするには
- NULLでない最初の項目を返すには
- 集計行を付加するには
- 制約を一時的に無効にするには
- セッションごとのユニークな値を得るには
- ユニークキーを削除するには
- テーブルのアイデンティティを外す方法
- 指定した範囲日付がテーブルの範囲日付と重複する期間があるか調べるには
1. テーブルをコピーするには
あるテーブルをそっくりそのままコピーするには、以下のSQLを使用します。
SELECT * INTO [コピー先テーブル名] FROM [コピー元テーブル名]
次の例では、[employees]テーブルのコピーを、[w_employees]というオブジェクト名で作成しています。
SELECT * INTO w_employees FROM employees
事前に、コピー先のテーブルを作成しておく必要はありません。
>>ページ上部へ
2. テーブルをコピーするには2
あるテーブルをそっくりそのままコピーするには、以下のSQLを使用します。
INSERT INTO [コピー先テーブル名] SELECT * FROM [コピー元テーブル名]
次の例では、[employees]テーブルのコピーを、[w_employees]というオブジェクト名で作成しています。
INSERT INTO w_employees SELECT * FROM employees
事前に、コピー先のテーブルを作成しておく必要があります。
>>ページ上部へ
3. プロシージャの従属関係を表示するには
SQLServerにおいて、プロシージャの従属関係は、[sysdepends]というシステムテーブルが保持しています。
例えば、プロシージャAがプロシージャBに従属している場合、[sysdepends]におけるプロシージャBのレコードのdepidには、プロシージャAのオブジェクトID(sysobjects.id)が登録されています。
SELECT DISTINCT
op.id AS [ParentObjectID],
op.name AS [ParentObjectName],
oc.id AS [ChildObjectID],
oc.name AS [ChildObjectName],
oc.type AS [ChildObjectType]
FROM
sysdepends AS d
INNER JOIN sysobjects AS op
ON d.id = op.id
INNER JOIN sysobjects AS oc
ON d.depid = oc.id
WHERE
d.id = [従属関係を表示したいオブジェクトのID]
ORDER BY
op.name,
oc.name
>>ページ上部へ
4. エラーの詳細を表示するには
SQLServerでは、SQLエラーが発生すると@@errorというシステム変数に発生したエラーのエラー番号が格納されます。
そのシステム変数の値を、メッセージを格納する[master].[sysmessages]というシステムテーブルのerror項目と紐付けします。
SELECT * FROM [master].[sysmessages] WHERE error = [エラー番号]
[master].[sysmessages]のdescription項目に、該当するエラーのメッセージ内容が格納されています。
また、msglangid項目に"1041"を指定すると日本語のエラーメッセージを、"1033"を指定すると英語のメッセージを表示できます。
SELECT description FROM [master].[sysmessages] WHERE error = [エラー番号] AND msglangid = 1041
>>ページ上部へ
5. SQLで指定したファイルを実行するには
SQLServerにて、SQLで指定したファイルを実行するには、以下のSQLを使用します。
EXEC xp_cmdshell 'C:\WINDOWS\NOTEPAD.EXE'
システムストアドプロシージャ[xp_cmdshell]を使用することで、外部アプリケーションを起動できます。
上の例では、SQLを実行するとメモ帳を起動します。
かなり危険なプロシージャで、データベースユーザーが実行可能なすべてのコマンドを実行できます。
>>ページ上部へ
6. サーバー名を取得するには
SQLServerにて、サーバー名を取得するには、以下のSQLを使用します。
SELECT @@servername
SQLServerでは、システム変数[@@servername]にサーバー名称が格納されています。
>>ページ上部へ
7. SQL Serverのバージョン情報を取得するには
バージョン名を取得するには、以下のSQLを使用します。
SELECT @@version
SQLServerでは、システム変数[@@version]にバージョン情報が格納されています。
>>ページ上部へ
8. 指定したテーブルの項目名を取得するには
SQLServerでは、項目名（フィールド名）は、syscolumnsというシステムテーブルが保持しています。
syscolumnsテーブルのnameフィールドに、項目名が入力されています。
SELECT
t2.name
FROM
sysobjects AS t1
INNER JOIN syscolumns AS t2
ON t1.id = t2.id
ORDER BY
t2.sortnum
>>ページ上部へ
9. 抽出結果をバイナリで取得するには
型変換を行う関数であるCONVERT関数は、バイナリ型に変換することもできます。
SELECT CONVERT(VARBINARY, [バイナリに変換したい値])
>>ページ上部へ
10. セッションによって別名の仮表を作成するには
SQLServerでは、セッションごとに一意となる値を@@spidというシステム変数が保持しています。
仮表に@@spidを文字列結合することで、セッションごとに一意となる仮表名を付けることができます。
DECLARE @sql VARCHAR(255)
SELECT @sql = 'CREATE TABLE TEST' + @@spid + ' (id INT, code VARCHAR(15), VARCHAR(40))'
EXECUTE @sql
>>ページ上部へ
11. SQLコマンドからデタッチ・アタッチするには
SQLServerでは、SQLコマンドからデタッチ・アタッチするには、以下のSQLを使用します。
-- デタッチ
sp_detach_db kaikei
-- アタッチ
sp_attach_db kaikei , 'C:\MSSQL7\Data\kaikei_data.mdf', 'C:\MSSQL7\Data\kaikei_log.ldf'
SQLコマンドからデタッチ・アタッチするには、システムプロシージャを実行します。
デタッチする場合は、システムプロシージャ[sp_detach_db]を、アタッチする場合は、システムプロシージャ[sp_attach_db]を実行します。
上の例では、[kaikei]というデータベースのデタッチおよびアタッチの例です。
>>ページ上部へ
12. NULLでない最初の項目を返すには
NULLでない最初の項目の値を返すには、COALESE関数を使用します。
SELECT COALESCE([項目1], [項目2], [項目3], ･･･)
左から順に項目値を参照し、NULLでない値を表示します。
つまり、上の例において、[項目1]がNULLでなければ[項目1]を返します。
[項目1]がNULLであれば[項目2]を参照し、[項目2]がNULLでなければ[項目2]を返します。
>>ページ上部へ
13. 集計行を付加するには
集計行を付加するには、GROUP BYに次のようなオプションを指定します。
GROUP BY [集計項目] WITH ROLLUP
GROUP BYにROLLUPオプションを付けると、集計行が付加されます。
GROUPING関数を次のように使用することで、集計行であるかどうかを判別することができます。
SELECT sex, SUM(age), GROUPING(sex) FROM employees GROUP BY ROLLUP(sex);
実行結果
| sex |
SUM(age) |
GROUPING(sex) |
| man |
200 |
0 |
| woman |
150 |
0 |
|
350 |
1 |
>>ページ上部へ
14. 制約を一時的に無効にするには
制約を一時的に無効にするには、以下のSQLを使用します。
-- 制約を一時的に無効にする
ALTER TABLE [テーブル名] NOCHECK CONSTRANT [制約名]
-- 無効にした制約を復活
ALTER TABLE [テーブル名] CHECK CONSTRANT [制約名]
[制約名]に"ALL"を指定すると、指定したテーブルのすべての制約を無効にすることができます。
>>ページ上部へ
15. セッションごとのユニークな値を得るには
SQLServerでは、セッションごとのユニークな値を得るには、@@spidを利用します。
@@spidは、現在のユーザープロセスのサーバープロセス識別子（ID）を返します。
-- セッションごとにユニークな値を得る
SELECT @@spid
>>ページ上部へ
16. ユニークキーを削除するには
SQLServerの場合、ユニークキーが設定されているフィールドに対し、重複する値を入力しようとすると、以下のエラーが発生します。
UNIQUE KEY 違反、制約'[ユニークキーの名前]' : オブジェクト '[テーブル名]'に重複したキーは挿入できません。
ステートメントは終了されました。
どうしても重複した値を挿入したい場合は、一時的にユニークキー制約を解除します。
ALTER TABLE [テーブル名] DROP CONTRAIN [ユニークキー名]
>>ページ上部へ
17. テーブルのアイデンティティを外す方法
テーブルの作成時、指定したフィールドのアイデンティティ（自動採番）を設定するときがあります。
ただ、メンテナンスのために一時的にアイデンティティを解除したい場合も出てくるでしょう。
そのような場合、SQLServerでは以下のSQLコマンドを実行します。
-- テーブルのアイデンティティを解除
SET identity_insert '[テーブル名]' OFF
また、以下のSQLコマンドで解除したアイデンティティを再開します。
-- テーブルのアイデンティティを再開
SET identity_insert '[テーブル名]' ON
>>ページ上部へ
18. 指定した範囲日付がテーブルの範囲日付と重複する期間があるか調べるには
指定した範囲日付がテーブルの範囲日付と重複する期間があるかどうかを調べるには、以下のようにします。
年度テーブル（tbl_nendo）
| ID |
STDATE |
EDDATE |
| 1 |
2001/04/01 |
2002/03/31 |
| 2 |
2002/04/01 |
2003/03/31 |
| 3 |
2003/04/01 |
2004/03/31 |
上のようなテーブルがあるとします。
SQLで指定した範囲日付がすでに使われているかどうかを調査します。
DECLARE @stdate DATETIME
DECLARE @eddate DATETIME
SELECT @stdate = '2004/04/01' -- ここで任意の開始日付を指定します
SELECT @eddate = '2005/03/31' -- ここで任意の終了日付を指定します
IF (EXISTS (SELECT *
FROM tbl_nendo
WHERE (STDATE <= @stdate and EDDATE >= @stdate)
OR (STDATE <= @eddate and EDDATE >= @eddate)
OR (STDATE >= @stdate and EDDATE <= @eddate)))
PRINT 'NG'
ELSE
PRINT 'OK'
DECLAREで指定した範囲日付が年度テーブルで使用されている場合は'NG'を、使用されていない場合は'OK'を返します。
>>ページ上部へ
|
