undocumented なものを見つけたので、調べてみる - その5 ( sys.system_sql_modules からあさってみる ) -
今日は久しぶりにお休みをとって(とはいっても、午前中に電話会議があったり...午後から資料作成だったり...)、子供が春休みなので、早朝から子供の習い事に付き添いました。はて、子供の習い事に行くなんて、何か月いや何年?ぶりだろうか。最近、日曜日くらいしかまともに会ってない気がするし....もっと子供と色々な面で向き合わないといけませんね。
親と子どもの感情BOOK―感情ときちんと向き合う子どもが育つ
- 作者: エリザベスクレアリー,Elizabeth Crary,田上時子,本田敏子
- 出版社/メーカー: 築地書館
- 発売日: 2002/10
- メディア: 単行本
- 購入: 2人 クリック: 1回
- この商品を含むブログを見る
けど、今は色々な面で自分がダメだったりする....
40歳の教科書NEXT──自分の人生を見つめなおす ドラゴン桜公式副読本『16歳の教科書』番外編
- 作者: モーニング編集部,朝日新聞社
- 出版社/メーカー: 講談社
- 発売日: 2011/04/22
- メディア: 単行本(ソフトカバー)
- クリック: 11回
- この商品を含むブログ (7件) を見る
仕事も考えないとなぁ....
- 作者: 植松努
- 出版社/メーカー: サンクチュアリ出版
- 発売日: 2015/10/26
- メディア: 単行本(ソフトカバー)
- この商品を含むブログ (3件) を見る
さて、今回は久しぶりに UNDOCUMENTED なものを見つけてみたいと思います。過去にもいくつか紹介しています。
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ryuchan.hatenablog.com
ストアドプロシージャ系で UNDOCUMENTED なものを見つける場合には、sys.system_sql_modules から見つけるのが手っ取り早いです。下記のクエリを SQL Server Management Studio で実行してみましょう。
SELECT * FROM sys.system_sql_modules
例えば、下記のクエリを実行すると、sys.sp_MSforeachtable が見つかります。
SELECT * FROM sys.system_sql_modules WHERE definition LIKE '%MSFOREACH%'
MS 系で探しみます。
SELECT * FROM sys.system_sql_modules WHERE definition LIKE '%sp_MS%'
sys.sp_MShelpindex とか見つかりました。sp_helpindex と何が違うんでしょう?早速両者を比較してみましょう。
実行してみましょう。見慣れた結果ですね。
sp_helpindex 'Sales.SalesOrderHeader'
実行してみましょう。FillFactor がわかるのはちょっといいかなと思います。
sp_MShelpindex 'Sales.SalesOrderHeader'
ストアドの中身を覗いてみましょう。dbo.sysindexes, dbo.sysfilegroups および 対象テーブルと列のプロパティ情報を取得し、結果として返しています。
create procedure sys.sp_MShelpindex @tablename nvarchar(517), @indexname nvarchar(258) = null, @flags int = null as /*** @flags added for DaVinci uses. If the bit isn't set, use 6.5 ***/ /*** sp_MShelpindex '%s', null, 1 ***/ create table #tempID(cName nvarchar(132) COLLATE database_default NOT NULL, /* Index name */ cInx1 int NULL, cInx2 int NULL, cInx3 int NULL, cInx4 int NULL, cInx5 int NULL, cInx6 int NULL, cInx7 int NULL, cInx8 int NULL, cInx9 int NULL, cInx10 int NULL, cInx11 int NULL, cInx12 int NULL, cInx13 int NULL, cInx14 int NULL, cInx15 int NULL, cInx16 int NULL, /* 1 if DESC */ cC1 int NULL, cC2 int NULL, cC3 int NULL, cC4 int NULL, cC5 int NULL, cC6 int NULL, cC7 int NULL, cC8 int NULL, cC9 int NULL, cC10 int NULL, cC11 int NULL, cC12 int NULL, cC13 int NULL, cC14 int NULL, cC15 int NULL, cC16 int NULL /* 1 if Computed column */ ) create table #tempID2(cName nvarchar(132) COLLATE database_default NOT NULL, /* Index name */ cInx int NULL, /* Combined info */ cComputed int NULL /* 1 if on computed column(s) */ ) /* @flags is for daVinci */ if(@flags is null) select @flags = 0 set nocount on insert #tempID select i.name, indexkey_property(object_id(@tablename), i.indid, 1, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 2, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 3, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 4, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 5, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 6, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 7, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 8, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 9, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 10, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 11, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 12, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 13, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 14, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 15, N'isdescending'), indexkey_property(object_id(@tablename), i.indid, 16, N'isdescending'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 1), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 2), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 3), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 4), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 5), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 6), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 7), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 8), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 9), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 10), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 11), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 12), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 13), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 14), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 15), N'IsComputed'), columnproperty(object_id(@tablename), index_col(@tablename, i.indid, 16), N'IsComputed') from dbo.sysindexes i where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and ( @indexname is null or i.name = @indexname ) and (indexkey_property(object_id(@tablename), i.indid, 1, N'isdescending') is not null) and (i.name is not null) order by i.indid /* Construct the bit */ declare @idx int, @isComputed int declare @Name nvarchar(132) declare @Inx_1 int, @Inx_2 int, @Inx_3 int, @Inx_4 int, @Inx_5 int, @Inx_6 int, @Inx_7 int, @Inx_8 int declare @Inx_9 int, @Inx_10 int, @Inx_11 int, @Inx_12 int, @Inx_13 int, @Inx_14 int, @Inx_15 int, @Inx_16 int declare @C_1 int, @C_2 int, @C_3 int, @C_4 int, @C_5 int, @C_6 int, @C_7 int, @C_8 int declare @C_9 int, @C_10 int, @C_11 int, @C_12 int, @C_13 int, @C_14 int, @C_15 int, @C_16 int declare hC cursor global for select * from #tempID open hC fetch next from hC into @Name, @Inx_1, @Inx_2, @Inx_3, @Inx_4, @Inx_5, @Inx_6, @Inx_7, @Inx_8, @Inx_9, @Inx_10, @Inx_11, @Inx_12, @Inx_13, @Inx_14, @Inx_15, @Inx_16, @C_1, @C_2, @C_3, @C_4, @C_5, @C_6, @C_7, @C_8, @C_9, @C_10, @C_11, @C_12, @C_13, @C_14, @C_15, @C_16 while( @@FETCH_STATUS = 0 ) begin /* descending? */ select @idx = 0x0000 select @idx = (case when( @Inx_1 = 1 ) then @idx | 0x0001 else @idx end), @idx = (case when( @Inx_2 = 1 ) then @idx | 0x0002 else @idx end), @idx = (case when( @Inx_3 = 1 ) then @idx | 0x0004 else @idx end), @idx = (case when( @Inx_4 = 1 ) then @idx | 0x0008 else @idx end), @idx = (case when( @Inx_5 = 1 ) then @idx | 0x0010 else @idx end), @idx = (case when( @Inx_6 = 1 ) then @idx | 0x0020 else @idx end), @idx = (case when( @Inx_7 = 1 ) then @idx | 0x0040 else @idx end), @idx = (case when( @Inx_8 = 1 ) then @idx | 0x0080 else @idx end), @idx = (case when( @Inx_9 = 1 ) then @idx | 0x0100 else @idx end), @idx = (case when( @Inx_10 = 1 ) then @idx | 0x0200 else @idx end), @idx = (case when( @Inx_11 = 1 ) then @idx | 0x0400 else @idx end), @idx = (case when( @Inx_12 = 1 ) then @idx | 0x0800 else @idx end), @idx = (case when( @Inx_13 = 1 ) then @idx | 0x1000 else @idx end), @idx = (case when( @Inx_14 = 1 ) then @idx | 0x2000 else @idx end), @idx = (case when( @Inx_15 = 1 ) then @idx | 0x4000 else @idx end), @idx = (case when( @Inx_16 = 1 ) then @idx | 0x8000 else @idx end) select @isComputed = 0 select @isComputed = (case when( @C_1 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_2 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_3 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_4 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_5 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_6 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_7 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_8 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_9 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_10 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_11 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_12 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_13 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_14 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_15 = 1 ) then @isComputed | 1 else @isComputed end), @isComputed = (case when( @C_16 = 1 ) then @isComputed | 1 else @isComputed end) insert #tempID2 select @Name, @idx, @isComputed fetch next from hC into @Name, @Inx_1, @Inx_2, @Inx_3, @Inx_4, @Inx_5, @Inx_6, @Inx_7, @Inx_8, @Inx_9, @Inx_10, @Inx_11, @Inx_12, @Inx_13, @Inx_14, @Inx_15, @Inx_16, @C_1, @C_2, @C_3, @C_4, @C_5, @C_6, @C_7, @C_8, @C_9, @C_10, @C_11, @C_12, @C_13, @C_14, @C_15, @C_16 end close hC deallocate hC set nocount off if( @flags <> 0 ) begin /* daVinci is calling */ select i.name, i.status, i.indid, i.OrigFillFactor, IndCol1 = index_col(@tablename, i.indid, 1), IndCol2 = index_col(@tablename, i.indid, 2), IndCol3 = index_col(@tablename, i.indid, 3), IndCol4 = index_col(@tablename, i.indid, 4), IndCol5 = index_col(@tablename, i.indid, 5), IndCol6 = index_col(@tablename, i.indid, 6), IndCol7 = index_col(@tablename, i.indid, 7), IndCol8 = index_col(@tablename, i.indid, 8), IndCol9 = index_col(@tablename, i.indid, 9), IndCol10 = index_col(@tablename, i.indid, 10), IndCol11 = index_col(@tablename, i.indid, 11), IndCol12 = index_col(@tablename, i.indid, 12), IndCol13 = index_col(@tablename, i.indid, 13), IndCol14 = index_col(@tablename, i.indid, 14), IndCol15 = index_col(@tablename, i.indid, 15), IndCol16 = index_col(@tablename, i.indid, 16), SegName = s.groupname, FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey'), Descending = t.cInx, Computed = t.cComputed, IsTable = OBJECTPROPERTY(object_id(@tablename), N'IsTable') from ( dbo.sysindexes i inner join dbo.sysfilegroups s on i.groupid = s.groupid ), #tempID2 t where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and ( @indexname is null or i.name = @indexname ) and ( INDEXPROPERTY(object_id(@tablename), i.name, N'IsStatistics') <> 1 ) and ( INDEXPROPERTY(object_id(@tablename), i.name, N'IsAutoStatistics') <> 1 ) and ( INDEXPROPERTY(object_id(@tablename), i.name, N'IsHypothetical') <> 1 ) and i.name = t.cName order by i.indid end else begin /* select (case when (i.status & 0x0040) != 0 then substring(i.name, 9, (datalength(i.name)/2)-17) else i.name end), i.status, i.indid, i.OrigFillFactor, */ select i.name, i.status, i.indid, i.OrigFillFactor, IndCol1 = index_col(@tablename, i.indid, 1), IndCol2 = index_col(@tablename, i.indid, 2), IndCol3 = index_col(@tablename, i.indid, 3), IndCol4 = index_col(@tablename, i.indid, 4), IndCol5 = index_col(@tablename, i.indid, 5), IndCol6 = index_col(@tablename, i.indid, 6), IndCol7 = index_col(@tablename, i.indid, 7), IndCol8 = index_col(@tablename, i.indid, 8), IndCol9 = index_col(@tablename, i.indid, 9), IndCol10 = index_col(@tablename, i.indid, 10), IndCol11 = index_col(@tablename, i.indid, 11), IndCol12 = index_col(@tablename, i.indid, 12), IndCol13 = index_col(@tablename, i.indid, 13), IndCol14 = index_col(@tablename, i.indid, 14), IndCol15 = index_col(@tablename, i.indid, 15), IndCol16 = index_col(@tablename, i.indid, 16), SegName = s.groupname, FullTextKey = IndexProperty(object_id(@tablename), i.name, N'IsFulltextKey'), Descending = t.cInx, Computed = t.cComputed, IsTable = OBJECTPROPERTY(object_id(@tablename), N'IsTable') from ( dbo.sysindexes i inner join dbo.sysfilegroups s on i.groupid = s.groupid ), #tempID2 t where id = object_id(@tablename) and i.indid > 0 and i.indid < 255 and ( @indexname is null or i.name = @indexname ) and i.name = t.cName order by i.indid /* order by i.name */ end
もう、シーズンは過ぎましたが...毎年この時期は何かと憂鬱...
- アーティスト: VARIOUS ARTISTS
- 出版社/メーカー: ポニーキャニオン
- 発売日: 2011/10/05
- メディア: CD
- クリック: 31回
- この商品を含むブログを見る
色々と卒業したいものはあるけど、まずは無駄なお肉がついている自分の体から卒業したいー!!
健康体になるため、先週から合気道習ったりしてます。合気道の次の日には全身筋肉痛...
はじめての合気道: 自分を守る力が身につく。心と体を鍛えて健康になる
- 作者: 千野進
- 出版社/メーカー: 誠文堂新光社
- 発売日: 2015/05/13
- メディア: 単行本
- この商品を含むブログを見る