都内で働くSEの技術的なひとりごと / Technical soliloquy of System Engineer working in Tokyo

都内でサラリーマンやってます。SQL Server を中心とした (2023年からは Azure も。) マイクロソフト系(たまに、OSS系などマイクロソフト以外の技術も...)の技術的なことについて書いています。日々の仕事の中で、気になったことを技術要素関係なく気まぐれに選んでいるので記事内容は開発言語、インフラ等ばらばらです。なお、当ブログで発信、発言は私個人のものであり、所属する組織、企業、団体等とは何のかかわりもございません。ブログの内容もきちんと検証して使用してください。英語の勉強のため、英語の

undocumented なものを見つけたので、調べてみる - その5 ( sys.system_sql_modules からあさってみる ) -

 今日は久しぶりにお休みをとって(とはいっても、午前中に電話会議があったり...午後から資料作成だったり...)、子供が春休みなので、早朝から子供の習い事に付き添いました。はて、子供の習い事に行くなんて、何か月いや何年?ぶりだろうか。最近、日曜日くらいしかまともに会ってない気がするし....もっと子供と色々な面で向き合わないといけませんね。

親と子どもの感情BOOK―感情ときちんと向き合う子どもが育つ

親と子どもの感情BOOK―感情ときちんと向き合う子どもが育つ

 けど、今は色々な面で自分がダメだったりする....

40歳の教科書NEXT──自分の人生を見つめなおす ドラゴン桜公式副読本『16歳の教科書』番外編

40歳の教科書NEXT──自分の人生を見つめなおす ドラゴン桜公式副読本『16歳の教科書』番外編

 仕事も考えないとなぁ....

好奇心を“天職

好奇心を“天職"に変える空想教室

 さて、今回は久しぶりに 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

f:id:koogucc11:20160330093820p:plain

 例えば、下記のクエリを実行すると、sys.sp_MSforeachtable が見つかります。

SELECT 
    * 
FROM 
    sys.system_sql_modules
WHERE
    definition LIKE '%MSFOREACH%'

f:id:koogucc11:20160330093944p:plain

 MS 系で探しみます。

SELECT 
    * 
FROM 
    sys.system_sql_modules
WHERE
    definition LIKE '%sp_MS%'

f:id:koogucc11:20160330094413p:plain

 sys.sp_MShelpindex とか見つかりました。sp_helpindex と何が違うんでしょう?早速両者を比較してみましょう。

 実行してみましょう。見慣れた結果ですね。

sp_helpindex 'Sales.SalesOrderHeader'

f:id:koogucc11:20160330094513p:plain

 実行してみましょう。FillFactor がわかるのはちょっといいかなと思います。

sp_MShelpindex 'Sales.SalesOrderHeader'

f:id:koogucc11:20160330094556p:plain

f:id:koogucc11:20160330095518p:plain

 ストアドの中身を覗いてみましょう。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

もう、シーズンは過ぎましたが...毎年この時期は何かと憂鬱...

決定盤!!::卒業ソング ベスト

決定盤!!::卒業ソング ベスト

色々と卒業したいものはあるけど、まずは無駄なお肉がついている自分の体から卒業したいー!!


健康体になるため、先週から合気道習ったりしてます。合気道の次の日には全身筋肉痛...