2020年4月21日火曜日

SQLServerでカラム一覧を取得する

通常のカラム取得は以下のような感じ。

１．テーブルに含まれるカラムの取得

SELECT 
    S.name
   ,T.name
   ,C.column_id
   ,C.name
FROM   sys.objects T
inner join sys.schemas S on S.schema_id = T.schema_id
inner join sys.all_columns C on t.object_id = AC.object_id 
WHERE  t.name = '@テーブル名'
AND s.name='dbo'

または
SELECT 
    T.name
   ,C.column_id
   ,C.name
FROM   sys.columns C
inner join sys.tables T on t.object_id = object_id(T.name)
WHERE  T.name = '@テーブル名'


２．シノニムに含まれるカラムの取得

シノニムはsys.tablesではなくsys.synonymsを使用します。

SELECT 
    T.name
   ,C.column_id
   ,C.name
FROM   sys.columns C
inner join sys.synonyms T on t.object_id = object_id(T.base_object_name)
WHERE  T.name = '@シノニム名'



で、ここで当たり前ですがハマった。。。
シノニムでリンクサーバや他のDBを参照している場合、ローカルにはシノニムの先の情報が無いので、カラムが取得できません。
こっちが正解か・・・
SELECT 
    S.name
   ,T.name
   ,C.column_id
   ,C.name
FROM   [DB名].sys.objects T
inner join [DB名].sys.schemas S on S.schema_id = T.schema_id
inner join DB名].sys.all_columns C on t.object_id = AC.object_id 
WHERE  t.name = '@テーブル名'
AND s.name='dbo'[
ラベル: