「SQLServer」の編集履歴（バックアップ）一覧はこちら

「SQLServer」(2017/08/08 (火) 13:07:37) の最新版変更点

追加された行は緑色になります。

削除された行は赤色になります。

PageLastUpdate:&date()/today:&counter(today)/yesterday:&counter(yesterday)
#openclose(show=クリックすると見出し一覧を表示します){
#contents()
}

*&aname(セキュリティ接続時にWindowsのログインユーザーを返す関数){セキュリティ接続時にWindowsのログインユーザーを返す関数}
2010/11/24　環境:SQL Server 2008
データベースを2008に移行したのをきっかけに、可能な限りセキュリティ接続に切り替えたので、せっかくだからきちんと取得するようにしてみた。
#highlight(sql){CREATE FUNCTION [dbo].[NT_USER_NAME]()
RETURNS nvarchar(128)
AS
BEGIN
	DECLARE @nt_user_name nvarchar(128)
	SET @nt_user_name = ''

	SET @nt_user_name = (SELECT rtrim(convert(nvarchar(128), nt_user_name))
	FROM sys.dm_exec_sessions WHERE session_id = @@SPID);

	RETURN @nt_user_name
END}

*&aname(各ユーザーがどの固定サーバー ロールを持っているかを表形式で表示するSQL){各ユーザーがどの固定サーバー ロールを持っているかを表形式で表示するSQL}
2009/11/03　環境:SQL Server 2000
システム監査に対応するために作ってみましたその２。
#highlight(sql){select
 L.srvid
,S.srvname
,S.datasource
,S.catalog
,name
,password 
,xstatus
,CASE WHEN xstatus & 1 = 0 THEN '' ELSE '●' END AS denylogin
,CASE WHEN xstatus & 2 = 0 THEN '' ELSE '●' END AS hasaccess
,CASE WHEN xstatus & 4 = 0 THEN '' ELSE '●' END AS isntname
,CASE WHEN xstatus & 8 = 0 THEN '' ELSE '●' END AS isntuser
,CASE WHEN xstatus & 16 = 0 THEN '' ELSE '●' END AS sysadmin
,CASE WHEN xstatus & 32 = 0 THEN '' ELSE '●' END AS securityadmin
,CASE WHEN xstatus & 64 = 0 THEN '' ELSE '●' END AS serveradmin
,CASE WHEN xstatus & 128 = 0 THEN '' ELSE '●' END AS setupadmin
,CASE WHEN xstatus & 256 = 0 THEN '' ELSE '●' END AS processadmin
,CASE WHEN xstatus & 512 = 0 THEN '' ELSE '●' END AS diskadmin
,CASE WHEN xstatus & 1024 = 0 THEN '' ELSE '●' END AS dbcreator
FROM master.dbo.sysxlogins AS L
LEFT OUTER JOIN master.dbo.sysservers AS S
ON S.srvid = L.srvid}
結果はこんな感じ。
|srvid|srvname|datasource|catalog|name|password|xstatus|denylogin|hasaccess|isntname|isntuser|sysadmin|securityadmin|serveradmin|setupadmin|processadmin|diskadmin|dbcreator|h
|||||MyDomain\Domain Users|0x|6||●|●|||||||||
|||||MyDomain\Domain Admins|0x|22||●|●||●|||||||
|||||BUILTIN\Administrators|0x|22||●|●||●|||||||
|||||SERVER_A\administrator|0x|28|||●|●|●|||||||
|||||AppConnection|0xhogehoge|2||●||||||||||
|||||sa|0xmogemoge|18||●|||●|||||||
|0|SERVER_A|SERVER_A|||0x|224||||||●|●|●||||
|1|SERVER_B|SERVER_B||sa|0xhogemoge|64|||||||●|||||

*&aname(データベース オブジェクトの権限を見やすい形で取得する){データベース オブジェクトの権限を見やすい形で取得する}
2009/10/30　環境:SQL Server 2000
システム監査に対応するために作ってみました。
エンタープライズ マネージャみたいにSELECT/INSERT/UPDATE/DELETE/EXECUTEをオブジェクト別、ユーザー/ロール別で横並びに取得します。
SELECTやUPDATEで列で権限が設定されている場合は縦に並びます。
（手抜きなのでSELECTとUPDATEの両方に列ごとの設定があると掛け算になっちゃうので注意。時間のあるときに修正予定。）
|ServerName|DBName|Grantee|Owner|Object|ProtectType|SELECT|INSERT|UPDATE|DELETE|EXECUTE|h
|MyServer|MyDB|ユーザー/ロール|dbo|テーブルA|Grant|(All+New)|TRUE|(All+New)|TRUE||
|MyServer|MyDB|ユーザー/ロール|dbo|テーブルB|Grant|(All+New)|TRUE|列A|TRUE||
|MyServer|MyDB|ユーザー/ロール|dbo|テーブルB|Grant|(All+New)|TRUE|列B|TRUE||
|MyServer|MyDB|ユーザー/ロール|dbo|ストアド|Grant|||||TRUE|
表示される内容の詳細についてはsp_helprotectを確認してください。

sp_helprotectの「現在のデータベースしか使えない」という残念な仕様により、ストアド化して複数のDBの情報を一括で取得することはできませんでした。
まあクエリ アナライザからならUSEを使って複数DBを一気に取得するようにもできるんですが、まあたまにしか使わないからいいか、という感じで。

#highlight(sql){CREATE TABLE #sp_helprotect (
Owner sysname
,Object sysname
,Grantee sysname
,Grantor sysname
,ProtectType nvarchar(10)
,[Action] nvarchar(20)
,[Column] nvarchar(128)
)

INSERT INTO #sp_helprotect EXEC sp_helprotect @permissionarea='o'

SELECT @@Servername AS ServerName,db_name() AS DBName,PK.Grantee,PK.Owner,PK.Object,PK.ProtectType
,CASE WHEN S.Grantee Is Null THEN '' ELSE S.[SELECT] END AS [SELECT]
,CASE WHEN I.Grantee Is Null THEN '' ELSE 'TRUE' END AS [INSERT]
,CASE WHEN U.Grantee Is Null THEN '' ELSE U.[UPDATE] END AS [UPDATE]
,CASE WHEN D.Grantee Is Null THEN '' ELSE 'TRUE' END AS [DELETE]
,CASE WHEN E.Grantee Is Null THEN '' ELSE 'TRUE' END AS [EXECUTE]
FROM (
    SELECT Grantee,Owner,Object,ProtectType
    FROM #sp_helprotect
    GROUP BY Grantee,Owner,Object,ProtectType
) AS PK
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType,[Column] AS [SELECT]
    FROM #sp_helprotect
    WHERE [Action] = 'SELECT'
) AS S
ON PK.Grantee=S.Grantee
AND PK.Owner=S.Owner
AND PK.Object=S.Object
AND PK.ProtectType=S.ProtectType
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType
    FROM #sp_helprotect
    WHERE [Action] = 'INSERT'
) AS I
ON PK.Grantee=I.Grantee
AND PK.Owner=I.Owner
AND PK.Object=I.Object
AND PK.ProtectType=I.ProtectType
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType,[Column] AS [UPDATE]
    FROM #sp_helprotect
    WHERE [Action] = 'UPDATE'
) AS U
ON PK.Grantee=U.Grantee
AND PK.Owner=U.Owner
AND PK.Object=U.Object
AND PK.ProtectType=U.ProtectType
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType
    FROM #sp_helprotect
    WHERE [Action] = 'DELETE'
) AS D
ON PK.Grantee=D.Grantee
AND PK.Owner=D.Owner
AND PK.Object=D.Object
AND PK.ProtectType=D.ProtectType
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType
    FROM #sp_helprotect
    WHERE [Action] = 'EXECUTE'
) AS E
ON PK.Grantee=E.Grantee
AND PK.Owner=E.Owner
AND PK.Object=E.Object
AND PK.ProtectType=E.ProtectType
ORDER BY PK.Grantee,PK.Owner,PK.Object,PK.ProtectType

DROP TABLE #sp_helprotect}

*&aname(SQL Server 2008 Express Editionのインストール後、SQL Server Management Studioを立ち上げようとするとエラー){SQL Server 2008 Express Editionのインストール後、SQL Server Management Studioを立ち上げようとするとエラー}
2009/10/20
>ファイル C:\WINDOWS\Microsoft.NET\Framework\ⶐ粕\mscorlib.tlb を読み込むことができませんでした。この状況を修復しようとしましたが、ファイルが見つからなかったため失敗しました。
とエラーが表示される。
最初化けているところもあわせて検索をかけたけど見つからなかったのでmscorlib.tlb以降で検索したら下記のKBがヒット。
http://support.microsoft.com/kb/918685/ja
2005なんだけどおそらく2008でも同じと判断。ただし
>Meaningless_string の部分は、エラー メッセージ中に表示される意味のない文字です。これらは、v1.0.3705、v1.1.4870、v2.0.50727 などの Microsoft .NET Framework のバージョン番号に類似する文字です。 
ここがちょっと気になる。2008だと3.5かも。
ただ[%windir%\Microsoft.NET\Framework]以下を検索してもmscorlib.tlbは3.5の下にはないのでv2.0.50727でいいだろうと判断。
レジストリ値を[%windir%\Microsoft.NET\Framework\v2.0.50727\mscorlib.tlb]にしたらエラーは出なくなりました。

*&aname(レプリケート先のテーブルにupdateトリガを設定するときの注意){レプリケート先のテーブルにupdateトリガを設定するときの注意}
2008/08/12
レプリケート対象のテーブルは、データの更新とレプリケートの更新で2回udpateが走るので、updateトリガを貼る場合はトリガの先頭に
#highlight(sql){if update (msrepl_tran_version) return }
と入れるのが定石。

ところがテーブルA→Bと一方向のレプリケートをしたとき、テーブルBのupdateトリガが動かないことに気がついた。
レプリケートによる更新ではmsrepl_tran_versionもupdateされるようだ。

まあそもそも一方向のレプリケートなのにmsrepl_tran_versionがあるというのが問題なので、正しく運用していれば起こらないか。

*&aname(ログインアカウントの既定のデータベースを削除するとエンタープライズマネージャで接続できなくなる){ログインアカウントの既定のデータベースを削除するとエンタープライズマネージャで接続できなくなる}
2008/05/16　検索語：使えない、つながらない
突然エンタープライズマネージャから特定のサーバーへのアクセスができなくなった。
他のアカウントだと問題はない。なんで？と思ったら、接続情報のログインに指定された「既定のデータベース」を削除していた。
既定のデータベースを存在するものに変更したら接続できるようになった。
特に検証せず修正してしまったけど、たぶん接続先データベースを指定して接続すれば問題ないんだろうな。

*&aname(レプリケートしているテーブルでUPDATEトリガが動作しない){レプリケートしているテーブルでUPDATEトリガが動作しない}
2008/05/12　検索語：アクセス、DAO、ADO、動かない、実行されない
別にバグでもなんでもないですが、落とし穴としてメモ。
「Accessから更新するとトリガが動いてない！」という報告を受けてリンクテーブルでテストしたけど・・・動いてる。
Accessからだと動かないって理由はないだろう・・・としばらく考えていて、トリガの先頭の
#highlight(sql){if update(msrepl_tran_version) return}
以外にありえないと気がついた。
ソースを見てみるとご丁寧にmsrepl_tran_versionも含めて全フィールドに値を書き込んでくれてます。

確かにレプリケート自体レアな仕様だから、まあ気がつかないかもね。
でも書き込む必要のないフィールドに値を入れるのはそもそも冗長なのでダメですね。

こういうのを開発チームに徹底するのって大変。

*&aname(任意の文字列を含む列名を検索){任意の文字列を含む列名を検索}
2008/04/25
同じ列名のローマ字綴りがテーブルによってズレてるのを見つけてへこむ。
間違いやすい綴りについてチェックするために、列名検索を作成。
cyu→chuとかjyu→juとか、20個近くありましたorz
#highlight(sql){
SELECT sysobjects.name AS TableName , syscolumns.name AS ColumnName
FROM syscolumns
INNER JOIN sysobjects 
ON sysobjects.id=syscolumns.id
WHERE sysobjects.xtype = 'U'
AND syscolumns.name like '%tyo%'
}
*&aname(TRUNCATE TABLE を行うには ALTER TABLE 権限が必要){TRUNCATE TABLE を行うには ALTER TABLE 権限が必要}
2008/04/25
ユーザーが一時使用するテーブルに対してデータをDELETEではなくTRUNCATE TABLEで消したいとする。
(多量のデータを一括して消したいとき、TRUNCATE TABLEはDELETEより効率がよい。)
このときテーブルにDELETE権限を与えても、ストアドなどにEXECUTE権限を与えてTRUNCATE TABLEしようとしても成功しない。
テーブルをユーザーの所有にすればTRUNCATE TABLEが使えるようになる。

*&aname(Accessは所有者名に\を含むSQLServerのテーブルをリンクできない){Accessは所有者名に\を含むSQLServerのテーブルをリンクできない}
2008/04/25
SQLServerはユーザー名に\を含めることができる。
例えば別ドメインのユーザーとしてDomainName\UserNameのような表記があり得る。
しかしこのテーブルはAcesssからリンクできない。

*&aname(INSTEAD OFトリガ中でSELECTを書くとODBCが「カーソルの状態が正しくありません」とエラーを返す。){INSTEAD OFトリガ中でSELECTを書くとODBCが「カーソルの状態が正しくありません」とエラーを返す。}
2008/03/19
AccessからリンクしたテーブルにINSTEAD OFトリガを設定したが、テーブルを結合して更新をかけようとするとエラー。
特定条件に一致する件数のカウントのためにトリガの中でSELECTを書いたのが原因。
件数カウントは必須なので
#highlight(sql){
SELECT ... INTO #DUMMY
FROM ...

IF @@ROWCOUNT > 0
BEGIN ...
}
のようにしたら解決。えー。
*&aname(SQLServerの最大容量仕様){SQLServerの最大容量仕様}
2007/11/27　検索語：列名の長さ、仕様
検索をかけてみてぱっとひっかからなかった。
http://www.microsoft.com/japan/sql/prodinfo/previousversions/Specmax.mspx
*&aname(システムストアドプロシージャの結果をテーブルとして使用する。){システムストアドプロシージャの結果をテーブルとして使用する。}
2007/08/22
SQL Serverがロックしているときってエンタープライズマネージャからは蹴られることが多い。
システムストアドプロシージャのsp_lockとsp_whoを使えばいいんですが、プロセスがどのPCのものかを判断したいときに二つを見比べないといけません。
システムストアドプロシージャ同士が連結できないので、テーブルにできないかなと探したら、
http://blogs.wankuma.com/naka/archive/2004/03/07/1607.aspx
↑この記事を発見。
sp_lockとsp_whoを結合した結果を戻すストアドを作成してみました。
#highlight(sql){
CREATE PROCEDURE [dbo].[Select_LockStates] AS

CREATE TABLE #sp_lock (
	spid nvarchar(64)
	,dbid nvarchar(64)
	,PbjID nvarchar(64)
	,IndID nvarchar(64)
	,Type nvarchar(64)
	,Resource nvarchar(64)
	,Mode nvarchar(64)
	,Status nvarchar(64)
	)

INSERT INTO #sp_lock EXEC sp_lock 

CREATE TABLE #sp_who (
	spid nvarchar(64)
	,ecid nvarchar(64)
	,status nvarchar(64)
	,loginname nvarchar(64)
	,hostname nvarchar(64)
	,blk nvarchar(64)
	,dbname nvarchar(64)
	,cmd nvarchar(64)
)

INSERT INTO #sp_who EXEC sp_who 

SELECT *
FROM #sp_who
INNER JOIN #sp_lock
ON #sp_who.spid=#sp_lock.spid
GO}
*&aname(文字をバイト数で切る){文字をバイト数で切る}
2007/08/09
http://natchan.seesaa.net/article/943978.html
#highlight(sql){
LEFT CONVERT(VARCHAR(2), moji)
RIGHT REVERSE(CONVERT(VARCHAR(4), REVERSE(moji)))}
2バイト文字列が分割されるとどうなるのかなぁ。要検証。
**検証してみた
#highlight(sql){
DECLARE @moji varchar(30)
DECLARE @one varchar(1)
DECLARE @three varchar(3)
SET @moji  ='綺麗'
SELECT @one = CONVERT(VARCHAR(1), @moji)
SELECT @three = CONVERT(VARCHAR(3), @moji)

SELECT '基本文字列' AS TestCase , @moji AS String , DATALENGTH(CONVERT(VARCHAR(30), @moji)) AS Byte
UNION
SELECT '左から1バイト取得',@one,datalength(@one)
UNION
SELECT '左から3バイト取得',@three,datalength(@three)}
文字の途中で切れることもなく、綺麗にとってくれます。
|TestCase|String|Byte|
|基本文字列|綺麗|4|
|左から1バイト取得||0|
|左から3バイト取得|綺|2|
*&aname(レプリケート対象のテーブルに対するトリガの設定){レプリケート対象のテーブルに対するトリガの設定}
2007/06/12
レプリケート対象となったテーブルは、自分自身に対してmsrepl_tran_versionへのアップデートをかける。 
したがって、トリガを作成する場合は 
 if update (msrepl_tran_version) return 
を入れないと、トリガが複数回起動されるので注意。 
ただし、INSTEAD OF トリガに関してはこの行は不要。 
逆に設定すると動作しなくなるので注意。 

*&aname(テーブルサイズの一覧を返すSQL){テーブルサイズの一覧を返すSQL}
2007/06/07
#highlight(sql){
CREATE TABLE #temp ( 
TABLE_NAME sysname, 
rows sysname, 
reserved sysname, 
data varchar(32), 
index_size varchar(254), 
unused varchar(100) 
) 
INSERT INTO #temp EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'" 
SELECT * FROM #temp order by table_name}
ここで使われている sp_MSforeachtable はヘルプに乗っていない。
「[[アンドキュメンテッド ストアドプロシージャ>http://www.google.co.jp/search?hl=ja&q=%E3%82%A2%E3%83%B3%E3%83%89%E3%82%AD%E3%83%A5%E3%83%A1%E3%83%B3%E3%83%86%E3%83%83%E3%83%89+%E3%82%B9%E3%83%88%E3%82%A2%E3%83%89%E3%83%97%E3%83%AD%E3%82%B7%E3%83%BC%E3%82%B8%E3%83%A3&lr=lang_ja]]」で検索をかけるといろいろ出てきます。
他には sp_MSforeachdb とか便利そう。
別回：件数だけならこれでもいい
#highlight(sql){
SELECT o.name, i.rows
FROM sysindexes AS i, sysobjects AS o
WHERE o.xtype = 'U' and o.id = i.id and i.indid < 2;}
*&aname(ストアドプロシージャから任意のエラーを返す){ストアドプロシージャから任意のエラーを返す}
まあ一番シンプルに書くとこんな感じ。引数はこれ以上は省略できない。
 RAISERROR  ('ここにメッセージ',0,1)
 RETURN
*&aname(値を返すストアドプロシージャ){値を返すストアドプロシージャ}
2007/05/22
#highlight(sql){
--こんな風に定義して
CREATE PROCEDURE [dbo].[GetNextID] (@KEY int,@NEXTID int OUTPUT) AS
UPDATE T_COUNTER SET F_ID = F_ID +1 WHERE F_KEY = @KEY
SELECT @NEXTID = F_ID FROM T_COUNTER WHERE F_KEY = @KEY
--こんな風に使う。
DECLARE @NEXTID int
EXEC dbo.GetNextID 22,@NEXTID OUTPUT
PRINT @NEXTID}
レコードに対する処理をしない場合はユーザー定義関数のほうが適切。
*&aname(@@ERRORと@@ROWCOUNTを同時に使う){@@ERRORと@@ROWCOUNTを同時に使う}
2007/05/18　検索語：うまくいかない/値がとれない
どちらも間にIFとかPRINTを挟むとクリアされてしまうので注意。
#highlight(sql){

BEGIN TRANSACTION

DECLARE @RC int
DECLARE @ERR int

UPDATE ...
SELECT @RC=@@ROWCOUNT,@ERR=@@ERROR --必ず実行した直後に

IF @ERR<>0 		
BEGIN
 ROLLBACK TRANSACTION		
 RETURN @ERR
END

IF @RC=0
BEGIN
 INSERT ... 
 SELECT @RC=@@ROWCOUNT,@ERR=@@ERROR --必ず実行した直後に
 IF @ERR<>0 		
 BEGIN
  ROLLBACK TRANSACTION		
  RETURN @ERR
 END
END
 
COMMIT TRANSACTION}
*&aname(SQLからスカラ値を取り出す){SQLからスカラ値を取り出す}
#highlight(sql){
DECLARE @Result int
SELECT @Result = myField FROM myTable
PRINT @Result}
のようにして取り出せる。複数レコードだとどうなるんだろう。あとでテストしよう。

*&aname(動的なSQLの戻り値を取得する){動的なSQLの戻り値を取得する}
#highlight(sql){
DECLARE @Result nvarchar(1024)
EXECUTE sp_executesql N'SELECT @Result = ...', N'@Result nvarchar(1024) OUTPUT', @Result OUTPUT
PRINT @Result}
**ユーザー定義関数を文字列で与えて結果を返せるか？
上のsp_executeを利用して、fn_getresult('FunctionName')みたいに、ユーザー定義関数を文字で指定して結果を返すユーザー定義関数がつくれるかもと思ったけど、ユーザー定義関数の中でsp_executeがそもそも利用できませんでした。
もしできるなら誰か教えてください。
*&aname(CURSORの基本構文){CURSORの基本構文}
#highlight(sql){
DECLARE myCursor CURSOR FOR 
SELECT myField FROM myTable

OPEN myCursor
FETCH NEXT FROM myCursor　　--←　これを知らなくてハマった。@@FETCH_STATUSはOPEN直後が0であるとは限らないので、かならず1回実行してからLOOPに入ること。

WHILE @@FETCH_STATUS = 0
BEGIN
　--処理
　FETCH NEXT FROM myCursor
END

CLOSE myCursor
DEALLOCATE myCursor}
*&aname(実行権限があるストアドが実行できない){実行権限があるストアドが実行できない}
2007/05/02
ストアドの中でExec(SQL文)とした場合、SQL文の中にSELECT権限のないテーブルが含まれるとコケる。
平文で書いた場合はストアドの実行権限があればよい。どうせいと。
http://fukkey.dyndns.org/pins/sqls/020121/16348.html
>所有権の継承の使用

>EXECUTE
>...権限
>ストアド プロシージャの EXECUTE 権限は、特に指定のない限りストアド プロシージャの所有者に与えられます。EXECUTE 文字列内でステートメントを使用する権限は、そのステートメントがストアド プロシージャ内に含まれている場合でも、EXECUTE の実行直前にチェックされます。文字列を実行するストアド プロシージャが実行されるとき、権限は、プロシージャを作成したユーザーのコンテキストではなく、プロシージャを実行しているユーザーのコンテキストでチェックされます。しかし、ユーザーが 2 つのストアド プロシージャを所有しており、最初のプロシージャが 2 番目のプロシージャを呼び出すような場合、2 番目のストアド プロシージャに対して EXECUTE 権限がチェックされることはありません。
*&aname(CREATE TABLBEでNULL可/不可は常に明示したほうがよい){CREATE TABLBEでNULL可/不可は常に明示したほうがよい}
2007/05/16
トリガ中のCREATEA TABLEで「NULL可」を明示的に指定しなかったことでエラー。 
規定ではNULL可で作成されるはずだが、明示したほうがよい。
*&aname(よく使うサイト){よく使うサイト}
2007/04/25
pin's Laboratory　http://www7.big.or.jp/~pinball/discus/sqls/index.html
2007/04/27
SQLを速くするぞ―お手軽パフォーマンス・チューニング http://www.geocities.jp/mickindex/database/db_optimize.html
----
*comment
このページの記述で聞きたいこととか間違ってることとかありましたらコメントを。
#comment_num2
----

PageLastUpdate:&date()/today:&counter(today)/yesterday:&counter(yesterday)
#openclose(show=クリックすると見出し一覧を表示します){
#contents()
}

*&aname(Management Studio のインテリセンスに作成したオブジェクトを即反映させる}
2017/08/08　環境:SQL Server 2016
[メニュ]-[編集]-[IntelliSense]-[ローカルキャッシュの更新] 

参考：Management Studio のクエリウィンドウで表示される赤い波線を消す方法
http://www.projectgroup.info/tips/SQLServer/MSSQL_00000012.html

*&aname(セキュリティ接続時にWindowsのログインユーザーを返す関数){セキュリティ接続時にWindowsのログインユーザーを返す関数}
2010/11/24　環境:SQL Server 2008
データベースを2008に移行したのをきっかけに、可能な限りセキュリティ接続に切り替えたので、せっかくだからきちんと取得するようにしてみた。
#highlight(sql){CREATE FUNCTION [dbo].[NT_USER_NAME]()
RETURNS nvarchar(128)
AS
BEGIN
	DECLARE @nt_user_name nvarchar(128)
	SET @nt_user_name = ''

	SET @nt_user_name = (SELECT rtrim(convert(nvarchar(128), nt_user_name))
	FROM sys.dm_exec_sessions WHERE session_id = @@SPID);

	RETURN @nt_user_name
END}

*&aname(各ユーザーがどの固定サーバー ロールを持っているかを表形式で表示するSQL){各ユーザーがどの固定サーバー ロールを持っているかを表形式で表示するSQL}
2009/11/03　環境:SQL Server 2000
システム監査に対応するために作ってみましたその２。
#highlight(sql){select
 L.srvid
,S.srvname
,S.datasource
,S.catalog
,name
,password 
,xstatus
,CASE WHEN xstatus & 1 = 0 THEN '' ELSE '●' END AS denylogin
,CASE WHEN xstatus & 2 = 0 THEN '' ELSE '●' END AS hasaccess
,CASE WHEN xstatus & 4 = 0 THEN '' ELSE '●' END AS isntname
,CASE WHEN xstatus & 8 = 0 THEN '' ELSE '●' END AS isntuser
,CASE WHEN xstatus & 16 = 0 THEN '' ELSE '●' END AS sysadmin
,CASE WHEN xstatus & 32 = 0 THEN '' ELSE '●' END AS securityadmin
,CASE WHEN xstatus & 64 = 0 THEN '' ELSE '●' END AS serveradmin
,CASE WHEN xstatus & 128 = 0 THEN '' ELSE '●' END AS setupadmin
,CASE WHEN xstatus & 256 = 0 THEN '' ELSE '●' END AS processadmin
,CASE WHEN xstatus & 512 = 0 THEN '' ELSE '●' END AS diskadmin
,CASE WHEN xstatus & 1024 = 0 THEN '' ELSE '●' END AS dbcreator
FROM master.dbo.sysxlogins AS L
LEFT OUTER JOIN master.dbo.sysservers AS S
ON S.srvid = L.srvid}
結果はこんな感じ。
|srvid|srvname|datasource|catalog|name|password|xstatus|denylogin|hasaccess|isntname|isntuser|sysadmin|securityadmin|serveradmin|setupadmin|processadmin|diskadmin|dbcreator|h
|||||MyDomain\Domain Users|0x|6||●|●|||||||||
|||||MyDomain\Domain Admins|0x|22||●|●||●|||||||
|||||BUILTIN\Administrators|0x|22||●|●||●|||||||
|||||SERVER_A\administrator|0x|28|||●|●|●|||||||
|||||AppConnection|0xhogehoge|2||●||||||||||
|||||sa|0xmogemoge|18||●|||●|||||||
|0|SERVER_A|SERVER_A|||0x|224||||||●|●|●||||
|1|SERVER_B|SERVER_B||sa|0xhogemoge|64|||||||●|||||

*&aname(データベース オブジェクトの権限を見やすい形で取得する){データベース オブジェクトの権限を見やすい形で取得する}
2009/10/30　環境:SQL Server 2000
システム監査に対応するために作ってみました。
エンタープライズ マネージャみたいにSELECT/INSERT/UPDATE/DELETE/EXECUTEをオブジェクト別、ユーザー/ロール別で横並びに取得します。
SELECTやUPDATEで列で権限が設定されている場合は縦に並びます。
（手抜きなのでSELECTとUPDATEの両方に列ごとの設定があると掛け算になっちゃうので注意。時間のあるときに修正予定。）
|ServerName|DBName|Grantee|Owner|Object|ProtectType|SELECT|INSERT|UPDATE|DELETE|EXECUTE|h
|MyServer|MyDB|ユーザー/ロール|dbo|テーブルA|Grant|(All+New)|TRUE|(All+New)|TRUE||
|MyServer|MyDB|ユーザー/ロール|dbo|テーブルB|Grant|(All+New)|TRUE|列A|TRUE||
|MyServer|MyDB|ユーザー/ロール|dbo|テーブルB|Grant|(All+New)|TRUE|列B|TRUE||
|MyServer|MyDB|ユーザー/ロール|dbo|ストアド|Grant|||||TRUE|
表示される内容の詳細についてはsp_helprotectを確認してください。

sp_helprotectの「現在のデータベースしか使えない」という残念な仕様により、ストアド化して複数のDBの情報を一括で取得することはできませんでした。
まあクエリ アナライザからならUSEを使って複数DBを一気に取得するようにもできるんですが、まあたまにしか使わないからいいか、という感じで。

#highlight(sql){CREATE TABLE #sp_helprotect (
Owner sysname
,Object sysname
,Grantee sysname
,Grantor sysname
,ProtectType nvarchar(10)
,[Action] nvarchar(20)
,[Column] nvarchar(128)
)

INSERT INTO #sp_helprotect EXEC sp_helprotect @permissionarea='o'

SELECT @@Servername AS ServerName,db_name() AS DBName,PK.Grantee,PK.Owner,PK.Object,PK.ProtectType
,CASE WHEN S.Grantee Is Null THEN '' ELSE S.[SELECT] END AS [SELECT]
,CASE WHEN I.Grantee Is Null THEN '' ELSE 'TRUE' END AS [INSERT]
,CASE WHEN U.Grantee Is Null THEN '' ELSE U.[UPDATE] END AS [UPDATE]
,CASE WHEN D.Grantee Is Null THEN '' ELSE 'TRUE' END AS [DELETE]
,CASE WHEN E.Grantee Is Null THEN '' ELSE 'TRUE' END AS [EXECUTE]
FROM (
    SELECT Grantee,Owner,Object,ProtectType
    FROM #sp_helprotect
    GROUP BY Grantee,Owner,Object,ProtectType
) AS PK
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType,[Column] AS [SELECT]
    FROM #sp_helprotect
    WHERE [Action] = 'SELECT'
) AS S
ON PK.Grantee=S.Grantee
AND PK.Owner=S.Owner
AND PK.Object=S.Object
AND PK.ProtectType=S.ProtectType
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType
    FROM #sp_helprotect
    WHERE [Action] = 'INSERT'
) AS I
ON PK.Grantee=I.Grantee
AND PK.Owner=I.Owner
AND PK.Object=I.Object
AND PK.ProtectType=I.ProtectType
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType,[Column] AS [UPDATE]
    FROM #sp_helprotect
    WHERE [Action] = 'UPDATE'
) AS U
ON PK.Grantee=U.Grantee
AND PK.Owner=U.Owner
AND PK.Object=U.Object
AND PK.ProtectType=U.ProtectType
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType
    FROM #sp_helprotect
    WHERE [Action] = 'DELETE'
) AS D
ON PK.Grantee=D.Grantee
AND PK.Owner=D.Owner
AND PK.Object=D.Object
AND PK.ProtectType=D.ProtectType
LEFT OUTER JOIN (
    SELECT Grantee,Owner,Object,ProtectType
    FROM #sp_helprotect
    WHERE [Action] = 'EXECUTE'
) AS E
ON PK.Grantee=E.Grantee
AND PK.Owner=E.Owner
AND PK.Object=E.Object
AND PK.ProtectType=E.ProtectType
ORDER BY PK.Grantee,PK.Owner,PK.Object,PK.ProtectType

DROP TABLE #sp_helprotect}

*&aname(SQL Server 2008 Express Editionのインストール後、SQL Server Management Studioを立ち上げようとするとエラー){SQL Server 2008 Express Editionのインストール後、SQL Server Management Studioを立ち上げようとするとエラー}
2009/10/20
>ファイル C:\WINDOWS\Microsoft.NET\Framework\ⶐ粕\mscorlib.tlb を読み込むことができませんでした。この状況を修復しようとしましたが、ファイルが見つからなかったため失敗しました。
とエラーが表示される。
最初化けているところもあわせて検索をかけたけど見つからなかったのでmscorlib.tlb以降で検索したら下記のKBがヒット。
http://support.microsoft.com/kb/918685/ja
2005なんだけどおそらく2008でも同じと判断。ただし
>Meaningless_string の部分は、エラー メッセージ中に表示される意味のない文字です。これらは、v1.0.3705、v1.1.4870、v2.0.50727 などの Microsoft .NET Framework のバージョン番号に類似する文字です。 
ここがちょっと気になる。2008だと3.5かも。
ただ[%windir%\Microsoft.NET\Framework]以下を検索してもmscorlib.tlbは3.5の下にはないのでv2.0.50727でいいだろうと判断。
レジストリ値を[%windir%\Microsoft.NET\Framework\v2.0.50727\mscorlib.tlb]にしたらエラーは出なくなりました。

*&aname(レプリケート先のテーブルにupdateトリガを設定するときの注意){レプリケート先のテーブルにupdateトリガを設定するときの注意}
2008/08/12
レプリケート対象のテーブルは、データの更新とレプリケートの更新で2回udpateが走るので、updateトリガを貼る場合はトリガの先頭に
#highlight(sql){if update (msrepl_tran_version) return }
と入れるのが定石。

ところがテーブルA→Bと一方向のレプリケートをしたとき、テーブルBのupdateトリガが動かないことに気がついた。
レプリケートによる更新ではmsrepl_tran_versionもupdateされるようだ。

まあそもそも一方向のレプリケートなのにmsrepl_tran_versionがあるというのが問題なので、正しく運用していれば起こらないか。

*&aname(ログインアカウントの既定のデータベースを削除するとエンタープライズマネージャで接続できなくなる){ログインアカウントの既定のデータベースを削除するとエンタープライズマネージャで接続できなくなる}
2008/05/16　検索語：使えない、つながらない
突然エンタープライズマネージャから特定のサーバーへのアクセスができなくなった。
他のアカウントだと問題はない。なんで？と思ったら、接続情報のログインに指定された「既定のデータベース」を削除していた。
既定のデータベースを存在するものに変更したら接続できるようになった。
特に検証せず修正してしまったけど、たぶん接続先データベースを指定して接続すれば問題ないんだろうな。

*&aname(レプリケートしているテーブルでUPDATEトリガが動作しない){レプリケートしているテーブルでUPDATEトリガが動作しない}
2008/05/12　検索語：アクセス、DAO、ADO、動かない、実行されない
別にバグでもなんでもないですが、落とし穴としてメモ。
「Accessから更新するとトリガが動いてない！」という報告を受けてリンクテーブルでテストしたけど・・・動いてる。
Accessからだと動かないって理由はないだろう・・・としばらく考えていて、トリガの先頭の
#highlight(sql){if update(msrepl_tran_version) return}
以外にありえないと気がついた。
ソースを見てみるとご丁寧にmsrepl_tran_versionも含めて全フィールドに値を書き込んでくれてます。

確かにレプリケート自体レアな仕様だから、まあ気がつかないかもね。
でも書き込む必要のないフィールドに値を入れるのはそもそも冗長なのでダメですね。

こういうのを開発チームに徹底するのって大変。

*&aname(任意の文字列を含む列名を検索){任意の文字列を含む列名を検索}
2008/04/25
同じ列名のローマ字綴りがテーブルによってズレてるのを見つけてへこむ。
間違いやすい綴りについてチェックするために、列名検索を作成。
cyu→chuとかjyu→juとか、20個近くありましたorz
#highlight(sql){
SELECT sysobjects.name AS TableName , syscolumns.name AS ColumnName
FROM syscolumns
INNER JOIN sysobjects 
ON sysobjects.id=syscolumns.id
WHERE sysobjects.xtype = 'U'
AND syscolumns.name like '%tyo%'
}
*&aname(TRUNCATE TABLE を行うには ALTER TABLE 権限が必要){TRUNCATE TABLE を行うには ALTER TABLE 権限が必要}
2008/04/25
ユーザーが一時使用するテーブルに対してデータをDELETEではなくTRUNCATE TABLEで消したいとする。
(多量のデータを一括して消したいとき、TRUNCATE TABLEはDELETEより効率がよい。)
このときテーブルにDELETE権限を与えても、ストアドなどにEXECUTE権限を与えてTRUNCATE TABLEしようとしても成功しない。
テーブルをユーザーの所有にすればTRUNCATE TABLEが使えるようになる。

*&aname(Accessは所有者名に\を含むSQLServerのテーブルをリンクできない){Accessは所有者名に\を含むSQLServerのテーブルをリンクできない}
2008/04/25
SQLServerはユーザー名に\を含めることができる。
例えば別ドメインのユーザーとしてDomainName\UserNameのような表記があり得る。
しかしこのテーブルはAcesssからリンクできない。

*&aname(INSTEAD OFトリガ中でSELECTを書くとODBCが「カーソルの状態が正しくありません」とエラーを返す。){INSTEAD OFトリガ中でSELECTを書くとODBCが「カーソルの状態が正しくありません」とエラーを返す。}
2008/03/19
AccessからリンクしたテーブルにINSTEAD OFトリガを設定したが、テーブルを結合して更新をかけようとするとエラー。
特定条件に一致する件数のカウントのためにトリガの中でSELECTを書いたのが原因。
件数カウントは必須なので
#highlight(sql){
SELECT ... INTO #DUMMY
FROM ...

IF @@ROWCOUNT > 0
BEGIN ...
}
のようにしたら解決。えー。
*&aname(SQLServerの最大容量仕様){SQLServerの最大容量仕様}
2007/11/27　検索語：列名の長さ、仕様
検索をかけてみてぱっとひっかからなかった。
http://www.microsoft.com/japan/sql/prodinfo/previousversions/Specmax.mspx
*&aname(システムストアドプロシージャの結果をテーブルとして使用する。){システムストアドプロシージャの結果をテーブルとして使用する。}
2007/08/22
SQL Serverがロックしているときってエンタープライズマネージャからは蹴られることが多い。
システムストアドプロシージャのsp_lockとsp_whoを使えばいいんですが、プロセスがどのPCのものかを判断したいときに二つを見比べないといけません。
システムストアドプロシージャ同士が連結できないので、テーブルにできないかなと探したら、
http://blogs.wankuma.com/naka/archive/2004/03/07/1607.aspx
↑この記事を発見。
sp_lockとsp_whoを結合した結果を戻すストアドを作成してみました。
#highlight(sql){
CREATE PROCEDURE [dbo].[Select_LockStates] AS

CREATE TABLE #sp_lock (
	spid nvarchar(64)
	,dbid nvarchar(64)
	,PbjID nvarchar(64)
	,IndID nvarchar(64)
	,Type nvarchar(64)
	,Resource nvarchar(64)
	,Mode nvarchar(64)
	,Status nvarchar(64)
	)

INSERT INTO #sp_lock EXEC sp_lock 

CREATE TABLE #sp_who (
	spid nvarchar(64)
	,ecid nvarchar(64)
	,status nvarchar(64)
	,loginname nvarchar(64)
	,hostname nvarchar(64)
	,blk nvarchar(64)
	,dbname nvarchar(64)
	,cmd nvarchar(64)
)

INSERT INTO #sp_who EXEC sp_who 

SELECT *
FROM #sp_who
INNER JOIN #sp_lock
ON #sp_who.spid=#sp_lock.spid
GO}
*&aname(文字をバイト数で切る){文字をバイト数で切る}
2007/08/09
http://natchan.seesaa.net/article/943978.html
#highlight(sql){
LEFT CONVERT(VARCHAR(2), moji)
RIGHT REVERSE(CONVERT(VARCHAR(4), REVERSE(moji)))}
2バイト文字列が分割されるとどうなるのかなぁ。要検証。
**検証してみた
#highlight(sql){
DECLARE @moji varchar(30)
DECLARE @one varchar(1)
DECLARE @three varchar(3)
SET @moji  ='綺麗'
SELECT @one = CONVERT(VARCHAR(1), @moji)
SELECT @three = CONVERT(VARCHAR(3), @moji)

SELECT '基本文字列' AS TestCase , @moji AS String , DATALENGTH(CONVERT(VARCHAR(30), @moji)) AS Byte
UNION
SELECT '左から1バイト取得',@one,datalength(@one)
UNION
SELECT '左から3バイト取得',@three,datalength(@three)}
文字の途中で切れることもなく、綺麗にとってくれます。
|TestCase|String|Byte|
|基本文字列|綺麗|4|
|左から1バイト取得||0|
|左から3バイト取得|綺|2|
*&aname(レプリケート対象のテーブルに対するトリガの設定){レプリケート対象のテーブルに対するトリガの設定}
2007/06/12
レプリケート対象となったテーブルは、自分自身に対してmsrepl_tran_versionへのアップデートをかける。 
したがって、トリガを作成する場合は 
 if update (msrepl_tran_version) return 
を入れないと、トリガが複数回起動されるので注意。 
ただし、INSTEAD OF トリガに関してはこの行は不要。 
逆に設定すると動作しなくなるので注意。 

*&aname(テーブルサイズの一覧を返すSQL){テーブルサイズの一覧を返すSQL}
2007/06/07
#highlight(sql){
CREATE TABLE #temp ( 
TABLE_NAME sysname, 
rows sysname, 
reserved sysname, 
data varchar(32), 
index_size varchar(254), 
unused varchar(100) 
) 
INSERT INTO #temp EXEC sp_MSforeachtable @command1 = "sp_spaceused '?'" 
SELECT * FROM #temp order by table_name}
ここで使われている sp_MSforeachtable はヘルプに乗っていない。
「[[アンドキュメンテッド ストアドプロシージャ>http://www.google.co.jp/search?hl=ja&q=%E3%82%A2%E3%83%B3%E3%83%89%E3%82%AD%E3%83%A5%E3%83%A1%E3%83%B3%E3%83%86%E3%83%83%E3%83%89+%E3%82%B9%E3%83%88%E3%82%A2%E3%83%89%E3%83%97%E3%83%AD%E3%82%B7%E3%83%BC%E3%82%B8%E3%83%A3&lr=lang_ja]]」で検索をかけるといろいろ出てきます。
他には sp_MSforeachdb とか便利そう。
別回：件数だけならこれでもいい
#highlight(sql){
SELECT o.name, i.rows
FROM sysindexes AS i, sysobjects AS o
WHERE o.xtype = 'U' and o.id = i.id and i.indid < 2;}
*&aname(ストアドプロシージャから任意のエラーを返す){ストアドプロシージャから任意のエラーを返す}
まあ一番シンプルに書くとこんな感じ。引数はこれ以上は省略できない。
 RAISERROR  ('ここにメッセージ',0,1)
 RETURN
*&aname(値を返すストアドプロシージャ){値を返すストアドプロシージャ}
2007/05/22
#highlight(sql){
--こんな風に定義して
CREATE PROCEDURE [dbo].[GetNextID] (@KEY int,@NEXTID int OUTPUT) AS
UPDATE T_COUNTER SET F_ID = F_ID +1 WHERE F_KEY = @KEY
SELECT @NEXTID = F_ID FROM T_COUNTER WHERE F_KEY = @KEY
--こんな風に使う。
DECLARE @NEXTID int
EXEC dbo.GetNextID 22,@NEXTID OUTPUT
PRINT @NEXTID}
レコードに対する処理をしない場合はユーザー定義関数のほうが適切。
*&aname(@@ERRORと@@ROWCOUNTを同時に使う){@@ERRORと@@ROWCOUNTを同時に使う}
2007/05/18　検索語：うまくいかない/値がとれない
どちらも間にIFとかPRINTを挟むとクリアされてしまうので注意。
#highlight(sql){

BEGIN TRANSACTION

DECLARE @RC int
DECLARE @ERR int

UPDATE ...
SELECT @RC=@@ROWCOUNT,@ERR=@@ERROR --必ず実行した直後に

IF @ERR<>0 		
BEGIN
 ROLLBACK TRANSACTION		
 RETURN @ERR
END

IF @RC=0
BEGIN
 INSERT ... 
 SELECT @RC=@@ROWCOUNT,@ERR=@@ERROR --必ず実行した直後に
 IF @ERR<>0 		
 BEGIN
  ROLLBACK TRANSACTION		
  RETURN @ERR
 END
END
 
COMMIT TRANSACTION}
*&aname(SQLからスカラ値を取り出す){SQLからスカラ値を取り出す}
#highlight(sql){
DECLARE @Result int
SELECT @Result = myField FROM myTable
PRINT @Result}
のようにして取り出せる。複数レコードだとどうなるんだろう。あとでテストしよう。

*&aname(動的なSQLの戻り値を取得する){動的なSQLの戻り値を取得する}
#highlight(sql){
DECLARE @Result nvarchar(1024)
EXECUTE sp_executesql N'SELECT @Result = ...', N'@Result nvarchar(1024) OUTPUT', @Result OUTPUT
PRINT @Result}
**ユーザー定義関数を文字列で与えて結果を返せるか？
上のsp_executeを利用して、fn_getresult('FunctionName')みたいに、ユーザー定義関数を文字で指定して結果を返すユーザー定義関数がつくれるかもと思ったけど、ユーザー定義関数の中でsp_executeがそもそも利用できませんでした。
もしできるなら誰か教えてください。
*&aname(CURSORの基本構文){CURSORの基本構文}
#highlight(sql){
DECLARE myCursor CURSOR FOR 
SELECT myField FROM myTable

OPEN myCursor
FETCH NEXT FROM myCursor　　--←　これを知らなくてハマった。@@FETCH_STATUSはOPEN直後が0であるとは限らないので、かならず1回実行してからLOOPに入ること。

WHILE @@FETCH_STATUS = 0
BEGIN
　--処理
　FETCH NEXT FROM myCursor
END

CLOSE myCursor
DEALLOCATE myCursor}
*&aname(実行権限があるストアドが実行できない){実行権限があるストアドが実行できない}
2007/05/02
ストアドの中でExec(SQL文)とした場合、SQL文の中にSELECT権限のないテーブルが含まれるとコケる。
平文で書いた場合はストアドの実行権限があればよい。どうせいと。
http://fukkey.dyndns.org/pins/sqls/020121/16348.html
>所有権の継承の使用

>EXECUTE
>...権限
>ストアド プロシージャの EXECUTE 権限は、特に指定のない限りストアド プロシージャの所有者に与えられます。EXECUTE 文字列内でステートメントを使用する権限は、そのステートメントがストアド プロシージャ内に含まれている場合でも、EXECUTE の実行直前にチェックされます。文字列を実行するストアド プロシージャが実行されるとき、権限は、プロシージャを作成したユーザーのコンテキストではなく、プロシージャを実行しているユーザーのコンテキストでチェックされます。しかし、ユーザーが 2 つのストアド プロシージャを所有しており、最初のプロシージャが 2 番目のプロシージャを呼び出すような場合、2 番目のストアド プロシージャに対して EXECUTE 権限がチェックされることはありません。
*&aname(CREATE TABLBEでNULL可/不可は常に明示したほうがよい){CREATE TABLBEでNULL可/不可は常に明示したほうがよい}
2007/05/16
トリガ中のCREATEA TABLEで「NULL可」を明示的に指定しなかったことでエラー。 
規定ではNULL可で作成されるはずだが、明示したほうがよい。
*&aname(よく使うサイト){よく使うサイト}
2007/04/25
pin's Laboratory　http://www7.big.or.jp/~pinball/discus/sqls/index.html
2007/04/27
SQLを速くするぞ―お手軽パフォーマンス・チューニング http://www.geocities.jp/mickindex/database/db_optimize.html
----
*comment
このページの記述で聞きたいこととか間違ってることとかありましたらコメントを。
#comment_num2
----