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
今後、追記していきます。