Edited at

【SQL Server】便利SQL集

More than 1 year has passed since last update.

SQL Serverを利用する上で、便利なSQLを以下に記載してみます。


新たにテーブルを生成しながら、既存テーブルをバックアップ

以下の例では、backupTableFromテーブルからbackupTableToに全件バックアップをする。

/* テーブルバックアップ */

select
* into backupTableTo /* 新たに生成されるバックアップ先テーブル */
from
backupTableFrom /* バックアップ対象テーブル */


ダミーレコード(疑似行)を指定行数分生成する。

例えば1~1000までの連番を動的に生成したい場合などは、以下の方法で対応できる。

再帰クエリを用いて、upper_limit変数に指定された上限値までレコードを生成する。

/* 上限値を設定、この例では1~1000までのレコードが生成される */

declare @upper_limit int = 1000;

with seq_table (seq)
as
(
select
1
union all
select
1 + seq
from
seq_table
where
seq < @upper_limit /* 無限ループの原因になるので、この条件は消してはならない。 */
)
select
seq
from
seq_table
option (maxrecursion 0) /* 再起クエリーの上限値を解除する */


データベースにある全てviewの定義を取得

データベースに定義されたviewのddlを一括で取得したい場合に利用できる。

/* データベースにある全てviewの定義を取得 */

select
views.name as view_name,
sql_modules.definition as definition
from
sys.views
inner join sys.objects
on views.object_id = objects.object_id
inner join sys.schemas
on objects.schema_id = schemas.schema_id
inner join sys.sql_modules
on objects.object_id = sql_modules.object_id
order by
views.name

また、調査などで特定のテーブルやカラムを利用しているviewを特定したい場合は、以下のwhere句を追加すればよい。

/* データベースにある全てviewの定義を取得 */

select
views.name as view_name,
sql_modules.definition as definition
from
sys.views
inner join sys.objects
on views.object_id = objects.object_id
inner join sys.schemas
on objects.schema_id = schemas.schema_id
inner join sys.sql_modules
on objects.object_id = sql_modules.object_id
where
sql_modules.definition like '%abc%' /* 調査対象となるテーブルやカラムをlike検索 */
order by
views.name

なお、トリガーやストアドプロシージャを含めて調査したい場合は、以下のSQLを利用する。ただし、VIEWの定義も出てくるが。。

select

object_name(object_id) as object_name,
definition as definition
from
sys.sql_modules
order by
object_name


select結果に1から順に連番を付与する。(row_number()関数)

select結果に対して、連番を付与しないとならない場合に利用できます。(そのままですが。)

select

LoginCode,
row_number() over (order by LoginCode asc) RowNum /* LoginCodeを昇順に並べた結果にたいして、1から順に連番を付与していく。 */
from
UserInfo


selectする際に、抽出項目の改行コードを省く

例えばSQL ServerのデータをCSVファイルに出力したいとき、抽出項目の値に改行コードがある場合にレコードの途中で改行されてしまい想定の結果が得られません。

本来ほしい結果


"社員番号","社員名","備考"

"0001","山田 太郎","○年○月入社 かに座"


改行コードが含まれた場合の結果("○年○月入社"と"かに座"の間に改行コードがある)


"社員番号","社員名","備考"

"0001","山田 太郎","○年○月入社

かに座" <- 2行になってしまう。改行してほしくない。


対応策としては、select項目に改行が含まれる場合は半角スペースなどに置換する。

select

replace(Message, char(13) + char(10), ' ')
from
table

char(13) + char(10)が改行コード。

なのでchar(13) + char(10)をreplace関数で半角スペースに置換している。


cascadeオプション(連鎖更新・連鎖削除)付きの外部キーの作成

以下の例ではChildTableのParentIdに対して、ParentTableのIdを外部参照するキーを追加する。

なお、cascadeオプションによりParentTableのIdが更新された場合、紐付き関係のあるChildTableのParentIdが更新される。また、ParentTableの行が削除された場合、紐付き関係のあるChildTableの行が削除される。

alter table dbo.ChildTable

add constraint FK_ChildTable_To_ParentTable
foreign key (ParentId) References ParentTable (Id)
on update cascade
on delete cascade


現在実行中のクエリを確認する

長時間実行しているクエリなどを特定する時に使います。

select

sql.text sql_text,
req.session_id session_id,
req.status status,
req.command command,
req.total_elapsed_time / 1000 elapsed_time_seconds,
req.start_time start_time
from
sys.dm_exec_requests req
cross apply sys.dm_exec_sql_text(req.sql_handle) sql


'yyyymmddhhmmss'形式の文字列からdatetime型へ変換する

declare @s varchar(20)

select @s = '20090513024903'
select convert(datetime, stuff(stuff(stuff(@s, 9, 0, ' '), 12, 0, ':'), 15, 0, ':'))

■参考URL

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=147285


今後、追記していきます。