データベース初期化スクリプト (SQL Server)

SQL Server 上でデータベースを作成し、ログインを作成し、データベースのユーザーを作成するための一連の Transact-SQL について記述します。
テーブルやインデックスの作成などについては触れません。
SQL Server 認証と Windows 認証では若干異なるので、分割して記述します。
 
■ SQL Server 認証のユーザーを作成する場合
Database1 データベースを作成し、user1 ログインおよび user1 ユーザーを作成するには、以下のようなスクリプトを実行します。

use [master]
go
 
if exists (select name from sys.databases where name = N'Database1')
drop database [Database1]
go
 
create database [Database1]
go
 
if exists (select name from sys.server_principals where name = N'user1')
drop login [user1]
go
 
create login [user1] with password = N'P@ssw0rd', default_database = [Database1]
go
 
use [Database1]
go
 
create user [user1]
go
 
exec sp_addrolemember N'db_owner', N'user1'
go

 
注意点
(1) 同じ名前のデータベースが既に存在するかどうかを調べるには、次のように db_id を使用してもかまいません。
if db_id(N'Database1') is not null
drop database [Database1]
go
(2) exists の逆の判定をするには、次のように is null を使用します。
if (select name from sys.databases where name = N'Database1') is null
(3) 照合順序の指定を省略した場合、日本語版では Japanese_CI_AS が設定されます。
     明示的に指定するには、次のように collate を使用します。
create database [Database1] collate Japanese_CI_AS
go
(4) ユーザーを削除するには次のようにします。
     (今回の場合は、同じ名前のユーザーが既に存在していたとしてもデータベース自体を再作成しているので不要です。)
if exists (select name from sys.database_principals where name = N'user1')
drop user [user1]
go
(5) ログイン名とユーザー名が同じであれば、ユーザーを作成するときに for login を省略できます。
     明示的に指定するには次のようにします。
create user [user1] for login [user1]
go
 
■ Windows 認証のユーザーを作成する場合
Database1 データベースを作成し、XYZPC\ASPNET ログインおよび XYZPC\ASPNET ユーザーを作成するには、
以下のようなスクリプトを実行します。

use [master]
go
 
if exists (select name from sys.databases where name = N'Database1')
drop database [Database1]
go
 
create database [Database1]
go
 
if exists (select name from sys.server_principals where name = N'XYZPC\ASPNET')
drop login [XYZPC\ASPNET]
go
 
create login [XYZPC\ASPNET] from windows with default_database = [master]
go
 
use [Database1]
go
 
create user [XYZPC\ASPNET]
go
 
exec sp_addrolemember N'db_owner', N'XYZPC\ASPNET'
go

 
バージョン情報
SQL Server 2005, 2008, 2008 R2
 
広告

LIKE で検索するときの文字のエスケープ (SQL Server)

LIKE 演算子を使用して部分一致検索などを行う場合、パーセント記号 (%) およびアンダースコア (_) をワイルドカードとして利用できますが、
例えば "10%" や "(^_^)/" をキーワードとして検索するときなど、
検索キーワード自体にワイルドカードが含まれているときにはこれらをリテラルとして扱うためにエスケープしなければなりません。

Transact-SQL では、検索キーワードのエスケープは次の方針に従います。

  • = 演算子または <> 演算子を使用する場合、いずれの文字もエスケープしない。
  • LIKE 演算子を使用する場合、パーセント記号 (%)、アンダースコア (_) および左角かっこ ([) を 2 つの角かっこ ([ ]) で囲む。

つまり、LIKE 演算子を使用する場合に限り、次の置換が必要ということです。

  • % → [%]
  • _ → [_]
  • [ → [[]

単純に String.Replace メソッドを利用すれば、次のように記述できます。

string newText = text.Replace("[", "[[]").Replace("%", "[%]").Replace("_", "[_]");

正規表現を利用すれば、次のように記述できます。

string newText = Regex.Replace(text, @"[%_\[]", "[$0]");

第 3 引数の中の $0 は、第 2 引数のパターンに一致したものの全体を表します。
これは、Regex.Matches メソッドにより返される各 Match オブジェクトの Groups[0] に相当するものです。

具体例を以下に示します。


// 商品名に対する部分一致検索をします。
public static void FillProductByLikeName(AdventureWorksDataSet.ProductDataTable dataTable, string name)
{
    if (name == null)
    {
        throw new ArgumentNullException("name");
    }
 
    using (var ta = new ProductTableAdapter())
    {
        // ProductTableAdapter.FillByLikeName メソッドは次の SQL から自動生成したメソッドです。
        // SELECT * FROM Product WHERE Name LIKE @Name
        ta.FillByLikeName(dataTable, AppendWildcard(EscapeForLike(name)));
    }
}
 
private static string AppendWildcard(string text)
{
    return string.Format("%{0}%", text);
}
 
private static string EscapeForLike(string text)
{
    return Regex.Replace(text, @"[%_\[]", "[$0]");
}

バージョン情報
.NET Framework 2.0, 3.0, 3.5, 4
SQL Server 2005, 2008, 2008 R2

参照
LIKE (Transact-SQL)
置換
Regex.Replace メソッド