patagonの日記： [コンピュータ]SQL Server 2000,2005 システムカタログを利用する

SQL Serverのシステムカタログを利用して列名、データ型、サイズ(長さ)、NULL許容かどうかを調べる。いつものSQLを使おうとしたら2005用で2000ではシステムカタログが異なって使えなかったのでメモ。

SQL Server 2005を利用するので日頃は意識しないが、SQL Serverではバージョンによりシステムカタログが異なる。SQL Server 2005と2000でも異なる。

SQL Server システム カタログに対するクエリに関してよく寄せられる質問(MSDN)
MSDNで「システム テーブル」で検索
MSDNで「システム カタログ」で検索

http://homepage1.nifty.com/MADIA/vc/vc_bbs/200401/200401_04010019.html
http://www.ikachi.org/sql/ref.html
http://www.kernel-net.ne.jp/tech/index.php?T-SQL%B4%D8%CF%A2%A5%E1%A5%E2
http://cafelounge.net/dev/?plugin=attach&refer=SQLServer%2F%E8%A7%A3%E6%9E%90&openfile=get_table_info.sql.txt
http://cafelounge.net/dev/?cmd=read&page=SQLServer%2F%E8%A7%A3%E6%9E%90
http://www.atmarkit.co.jp/bbs/phpBB/viewtopic.php?forum=26&topic=16052
http://csharp.yaminabe.info/

SQL Server 2005は select from sys.objects sys.columns で検索するといいだろう。
SQL Server 2000は select from sysobjects syscolumns で検索するといいだろう。
本当は sp_tables sp_columns sp_pkeys sp_fkeys 等のシステム ストアド プロシージャを利用したほうがいいｄろうけど。
ストアド プロシージャ カタログ (Transact-SQL)
SQL Server 2000 システム テーブルから SQL Server 2005 システム ビューへのマッピング

前置きが長くなった。

SQL Server 2005の例

-- SQL Server 2005
use SAMPLE_DB
 
create table #table (table_name nvarchar(50))
insert #table values ('%')
 
select
  テーブル名
, 列名
, データ型
, サイズ
, NULL許容
from
( select distinct
  テーブル名 = o.name
  , 列名  = c.name
  , データ型 = t.name
  , サイズ  = c.max_length
  , NULL許容 = case when c.is_nullable = 0 then '' else 'ﾚ' end
  , 列番号  = c.column_id
  from
  sys.objects o
  inner join
  sys.columns c
  on o.object_id = c.object_id
  inner join
  sys.types t
  on c.system_type_id = t.system_type_id
  and c.user_type_id  = t.user_type_id
  inner join
  #table
  on o.name like #table.table_name
  where
  o.type = 'U'
  and o.name <> 'dtproperties'
) tbl
order by
  テーブル名
, 列番号
 
drop table #table

SQL Server 2000の例

-- SQL Server 2000
-- 「like 操作での照合順序の競合を解決できません。」と表示されるのでテンポラリーは利用しない
-- 調べれば分かるんだろうけどとりあえずこれで。
use SAMPLE_DB
 
select distinct
  テーブル名 = o.name
  , 列名  = c.name
  , データ型 = t.name
  , サイズ  = c.length
  , NULL許容 = case when c.isnullable = 0 then '' else 'ﾚ' end
  , 列番号  = c.colid
-- ,o.type
  from
  sysobjects o
  inner join
  syscolumns c
  on o.id = c.id
  inner join
  systypes t
  on c.xtype = t.xtype
  and c.usertype = t.usertype
  where
  o.type = 'U'
  and o.name <> 'dtproperties'
order by
  テーブル名
, 列番号

＜

＞