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'[

tempdbがSHRINKFILEで圧縮できない

SQL Server2014 StandardEditionにて確認

SHRINKFILEでファイルを圧縮する際に、tempdbが圧縮できなかったケースがあったので覚書

temdbを圧縮するコマンドはこんな感じ。

DBCC SHRINKFILE（tempdb, 0）

ただ、今回は以下のようなエラーが出ました。

DBCC SHRINKFILE ページ N:Nは、作業テーブル　ページなので移動できませんでした。

このエラーメッセージで検索してもあまり情報がありません。
で、英文では以下のようなメッセージのようです。

Page N:N could not be moved because it is a work table page.

こちらのメッセージで調べると、クエリプランを再作成が必要だそうです。

DBCC FREEPROCCACHE

を行うことで、一時テーブルのキャッシュがクリアされて圧縮可能となりました。
ただ、FREEPROCCACHEはそれなりにコストがかかる作業かなと思うので、

DBCC FREESYSTEMCACHE (‘Temporary Tables & Table Variables’)




などの方が良いかもしれません。（未調査です）


ちなみに、今回のようにtempdbの圧縮が失敗すると、tempdbの初期サイズが書き換わってしまうそうです。
tempdbが肥大化していた場合、次回のSQLServerの起動に悪影響を与えることもあるそうなので、注意したほうが良さそうです。

【参考URL】
TempDB を圧縮する場合の注意点について

SQLServerのmdfファイルのサイズを取得する

SQL Server2014 StandardEditionにて確認

SqlServerで使用しているDBのファイルサイズを調べる場合

Select name, size From sys.database_files;

 
でmdfファイル、ldfファイルのサイズが取得できます。

tempdbについてもmdfファイルのサイズを取得したい場合は

Select name, size From sys.master_files;

 
を使いたくなりますが、なんだか結果が実際の値と合わない。。
Microsoftによるとsys.master_filesのサイズは、

データベース スナップショットの場合、size は、スナップショットがファイルに対して使用する中で最大の領域を表します。

とのこと。
でも、
USE tempdb
でDB変更すると
Select name, size From sys.database_files;
で実際のファイルサイズが取得できました。
でも、sys.database_filesの説明にも同じことしか書いてないのですが・・・