スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

テーブル情報の取得

SQL Serverに関する話です。

テーブル構造に関する情報(変数名や型、Primary Keyなど)を取得する方法です。

ココを参考にさせていただきました。

 

DB情報を取得するマクロ

-- マクロ登録 ---------------
create procedure DBInfo
as
BEGIN
    /* テーブル定義情報を取得 */
    SELECT              DB_NAME()                       AS db_name
             	      , SCHEMA_NAME(tables.schema_id)   AS schema_name
            	      , tables.name                     AS table_name
            	      , tables.type_desc                AS table_type
            	      , extProp.value                   AS table_comment
        FROM            sys.tables tables
	    /* コメントデータは拡張プロパティシステムビューに存在する */
        LEFT OUTER JOIN sys.extended_properties extProp
                    ON  extProp.class    = 1                /* テーブルの場合、classの値は常に1 (OBJECT_OR_COLUMN) */
    			    AND tables.object_id = extProp.major_id /* テーブルのobject_idはmajor_idと対応する */
    			    AND extProp.minor_id = 0                /* テーブルの場合、minor_idの値は常に0 */
        ORDER BY        db_name, schema_name, table_name
    ;
END
go

-- 使い方 --;
DBInfo

 

テーブル情報を取得するマクロ

-- マクロ登録 -----------------------------
create procedure TableInfo
    @tblname nvarchar(255)
as
BEGIN
    SELECT              DB_NAME()                         AS db_name
                      , SCHEMA_NAME(tables.schema_id)     AS schema_name
                      , tables.name                       AS table_name
                      , columns.name                      AS column_name
                      , columns.column_id                 AS ordinal_position
                        /* PKか否かの判別フラグ */
                      , CAST(CASE WHEN pk_cols.key_ordinal IS NOT NULL THEN 1 ELSE 0 END AS bit) AS is_primary_key
                      , pk_cols.constraint_name           AS pk_constraint_name
                      , pk_cols.key_ordinal               AS pk_key_ordinal
                        /* ユニークキー(UQ)か否かの判別フラグ */
                      , CAST(CASE WHEN uq_cols.key_ordinal IS NOT NULL THEN 1 ELSE 0 END AS bit) AS is_unique_key
                      , uq_cols.constraint_name           AS uq_constraint_name
                      , uq_cols.key_ordinal               AS uq_key_ordinal
                      , TYPE_NAME(columns.system_type_id) AS column_data_type
                      , defaultConstraints.definition     AS column_default
                      , columns.is_nullable               AS is_nullable
                      , columns.max_length                AS max_length
                      , columns.precision                 AS precision
                      , columns.scale                     AS scale
                      , extProp.value                     AS column_comment

        FROM            sys.tables                                      tables
        INNER JOIN      sys.columns                                     columns
                    ON  tables.object_id = columns.object_id
        /* DEFAULT制約定義を取得する為に以下の二つのテーブルを外部結合 */
        LEFT OUTER JOIN sys.sysconstraints                              constraints
                    ON  columns.object_id = constraints.id
                    AND columns.column_id = constraints.colid
                    /* DEFAULT制約を表す疑似ビットマスク値 (マスクしていないと取得できない場合があるとのこと. (thanks murasukeさん) */
                    AND (constraints.status & 2069) = 2069               
        LEFT OUTER JOIN sys.default_constraints                         defaultConstraints
                    ON  constraints.constid = defaultConstraints.object_id
                    AND tables.schema_id    = defaultConstraints.schema_id
        /* コメントデータは拡張プロパティシステムビューに存在する */
        LEFT OUTER JOIN sys.extended_properties                         extProp
                    ON  extProp.class = 1  /* カラムの場合、classの値は常に1 (OBJECT_OR_COLUMN) */
                    AND columns.object_id = extProp.major_id
                    AND columns.column_id = extProp.minor_id
        /* プライマリーキーの情報を取得する為に以下の情報を外部結合 */
        LEFT OUTER JOIN (
            SELECT          key_const.name       AS constraint_name
                          , idx_cols.key_ordinal AS key_ordinal
                          , cols.name            AS col_name
                          , cols.object_id       AS col_object_id
                          , cols.column_id       AS col_column_id
            FROM            sys.tables                                  tbls
            /* PKの情報を結合 */
            INNER JOIN      sys.key_constraints                         key_const
                        ON  tbls.object_id = key_const.parent_object_id
                        AND key_const.type = 'PK'
            /* 対応するインデックス情報からカラムと特定 */
            INNER JOIN      sys.index_columns                           idx_cols
                        ON  key_const.parent_object_id = idx_cols.object_id
                        AND key_const.unique_index_id  = idx_cols.index_id
            INNER JOIN      sys.columns                                 cols
                        ON  idx_cols.object_id = cols.object_id
                        AND idx_cols.column_id = cols.column_id
        )                                                               pk_cols
                    ON  columns.object_id = pk_cols.col_object_id
                    AND columns.column_id = pk_cols.col_column_id
        /* ユニークキーの情報を取得する為に以下の情報を外部結合 */
        LEFT OUTER JOIN (
            SELECT          key_const.name       AS constraint_name
                          , idx_cols.key_ordinal AS key_ordinal
                          , cols.name            AS col_name
                          , cols.object_id       AS col_object_id
                          , cols.column_id       AS col_column_id
            FROM            sys.tables                                  tbls
            /* UQ(ユニークキー)の情報を結合 */
            INNER JOIN      sys.key_constraints                         key_const
                        ON  tbls.object_id = key_const.parent_object_id
                        AND key_const.type = 'UQ'
            /* 対応するインデックス情報からカラムと特定 */
            INNER JOIN      sys.index_columns                           idx_cols
                        ON  key_const.parent_object_id = idx_cols.object_id
                        AND key_const.unique_index_id  = idx_cols.index_id
            INNER JOIN      sys.columns                                 cols
                        ON  idx_cols.object_id = cols.object_id
                        AND idx_cols.column_id = cols.column_id
        )                                                               uq_cols
                    ON  columns.object_id = uq_cols.col_object_id
                    AND columns.column_id = uq_cols.col_column_id

        WHERE           tables.name = @tblname
        ORDER BY        db_name
                      , schema_name
                      , table_name
                      , columns.column_id
    ;
END
GO

-- 使い方 -------------------------
TableInfo 'tbl_test';

 

以上です

スポンサーサイト

SQL ServerでBULK INSERT (BCP)する時の終端文字トラブル(1A)

SQL Serverで、テキストファイルをBULK Insertで取り込もうとしたら、
「」
というエラーが出て取り込めなかった。

ファイルをバイナリエディタで見ると、[1A]というのが最後についていた。
これは、UNIXでテキストファイルを作った際の終端文字

これがあることで、BULKINSERTできない。

解決策は、「そのファイルをコピーして[1A]をなくす」といったベタなものしか見当たらなかった。

なので、次のようなちょっとカッコ悪い解決策。

--- バッチコマンド(コピー)をSQL内で使うため、sp_cmdshellを利用可能にする ---
EXEC sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
EXEC sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

--- 元データの終端文字(1A)を取り除くため、コピーコマンドを実行 ---
xp_cmdshell 'copy /Y ***元ファイル名***.csv /a ***新ファイル名*** /b'''

--- BULK INSERTで取り込み ----------------------------
BULK INSERT     テーブル名
    FROM        '***新ファイル名***'
    WITH (
        FIELDTERMINATOR  = '',''
      , ROWTERMINATOR    = ''\n''
    )
;

テーブルの項目名を取得

[テーブルの項目名を取得]の続きを読む

FETCHの使い方(ファイルを次々読み込む)

ファイル一覧リストがあり、それぞれのファイルを取り込むための処理サンプル。
以前はVBでfor文を回していたが、そのfor文をSQLで書くとこんな感じ。
FETCHを使っています。

以下のサンプルは、『「List.txt」に書かれてあるファイルをすべてテーブルに取り込む』というもの。
ちなみに、List.txtにはフルパスで書かれていてる前提です。
また、バルクで取り込んでいるので、そのための「フォーマットファイル」もある前提です。





print CONVERT(char(12), GETDATE(), 14)

/*--- 取込対象リスト ----------------------------*/
CREATE TABLE #T_temp_TargetFile (
[fn] char(255) not null primary key
);

BULK INSERT #T_temp_TargetFile
FROM 'C:\temp\List.txt'
WITH (
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
;

/*--- 取込開始 -----------------------------------*/
DECLARE FNLIST CURSOR FOR
SELECT fn from #T_temp_TargetFile
;
DECLARE @fn nvarchar(255);

OPEN FNLIST;

FETCH NEXT FROM FNLIST INTO @fn
WHILE @@FETCH_STATUS = 0
BEGIN

declare @charind int ;
set @charind = 1 ;

print '--- 取込 -------------------------------'
exec('
BULK Insert T_temp
From ''' + @fn + '''
With (Formatfile=''C:\temp\formatfile.txt'')
;
') ;

print cast(@@rowcount as nvarchar) + '件取込' ;

-- 次へループ
FETCH NEXT FROM FNLIST INTO @fn ;
END

CLOSE FNLIST ;

-- 後処理 -----;
deallocate FNLIST
drop table #T_temp_TargetFile


print CONVERT(char(12), GETDATE(), 14)

InStrRevのSQL版

VBではInStrRevという、特定文字列を逆から検索する関数があるが、SQL SERVERでは見当たらなかったので、自作。
CharIndexを使ってます。

以下のサンプルでは、「\」を逆から検索するもの。

declare @charind int ;
set @charind = 1 ;

-- \より右側を取得する --
WHILE @charind > 0
BEGIN
set @charind = charindex('\',@fn) ;
IF @charind > 0
set @fn = rtrim(substring(@fn,@charind+1,len(@fn))) ;
END


上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。