sqlserverでカラムが自動採番やキーかどうかをSQLで知りたい

Question

###前提・実現したいこと
SQLserverのバックアップファイルのみがあり、開発者も経緯も不明のデータベースがあります。
接続して使用だけは出来ますので、MySQLに移して、WEBアプリ化などしたいと思っています。

###発生している問題・エラーメッセージ
Googleなどで調べ、次のSQLを発行しました。

###発行したSQL

SELECT
 o.xtype AS TableType ,
 o.name AS TableName ,
 c.name AS ColumnName ,
 t.name AS ColumnTypeName ,
 c.length AS ColumnLength ,
 c.isnullable AS ColumnNullOK
 FROM
 syscolumns as c
 INNER JOIN (
    SELECT
      id ,
      xtype ,
      name
    FROM
      sysobjects
    WHERE
      xtype IN ('U','V')
      AND
      name = '[テーブルの名前]'
  ) AS o
  ON
    c.id = o.id
 INNER JOIN (
  SELECT xtype,xusertype,type,usertype,name
  FROM systypes
  ) AS t
  ON
    c.xtype = t.xtype
  AND
    c.xusertype = t.xusertype
 ORDER BY
  o.name ,
  c.colid
;

###足りない情報
MySQLで言うところの、auto_increment が設定されているかどうか。 primary key指定がされているかどうかが知りたいです。

Googleで調べた限りでは、SQLServerではIDENTITYの指定がauto_increment にあたるようですが、このIDENTITYの指定がカラムに対して行われているか。またKey指定になっているかをSQLで取り出す方法がありましたら、教えてください。

###補足情報
VB.NETからADO経由でSQLSERVERに接続し、上記のSQLを送り、カラム名やタイプはデータセットとして受け取る事に成功しています。
後は、自動採番が採用されているかや、どのテーブルがキーなのかをSQLで拾い出せれば、それを元にCREATE TABLEをMysqlに対して発行するようにして、データベース構造とデータを丸ごと移植する事を考えております。
なにぶん、資料などが全く無く、データベース本体のみという有様ですので、一つ一つカラムを見ながら移すのでは膨大な時間がかかってしまいますので、よろしくお願いいたします。

Answer 1

以下は私がテーブル構造を知りたい時にべろっと投げるSQLです。
（SQLServer 2005, 2014 では動いてます）

use <DBName> とかして、以下をそのままコピペすれば動くと思います。

WITH TBLS AS (
  SELECT
    tbl.object_id AS TableId
  , tbl.name AS TableName
  , tbl.tv
  , col.column_id AS ColumnId
  , col.name AS ColumnName

    -- データ型/長さ
  , SUBSTRING(
      dtp.name
      + CASE WHEN dtp.name IN ('datetime', 'int', 'bit', 'decimal', 'bigint', 'tinyint', 'money', 'text', 'ntext') THEN ''
             ELSE '(' + 
               CASE WHEN dtp.name IN ('varchar', 'nvarchar', 'varbinary')
                     AND col.max_length = -1 THEN 'max'
               WHEN dtp.name IN ('nchar', 'nvarchar') THEN
                    CONVERT(varchar, (col.max_length / 2))
               ELSE CONVERT(varchar, col.max_length) END
             + ')'
        END
    , 1, 20) AS ColumnDataType

    -- PKEY
  , CASE WHEN
      (SELECT TOP 1 1 FROM information_schema.key_column_usage x
       WHERE x.table_name = tbl.name AND x.column_name = col.name)
    = 1 THEN '○' ELSE '' END AS PKey

    -- NULL許可
  , CASE WHEN col.is_nullable = 1 THEN '' ELSE '○' END AS NotNull

    -- IDENTITY
  , CASE WHEN idt.object_id IS NULL THEN ''
         ELSE 'IDENTITY(' +CONVERT(varchar,idt.seed_value)+ ', ' 
                          +CONVERT(varchar,idt.increment_value)+ ')' END AS ColumnIdentity
    -- COLLATE
  , CASE WHEN col.collation_name IS NULL THEN ''
         ELSE 'COLLATE ' +col.collation_name END AS Collation
    -- コメント
  , ep.value AS ColumnComment
  , tep.value AS TableComment

--FROM sys.tables tbl
  FROM (
    SELECT 'T' AS [tv], object_id, name FROM sys.tables UNION ALL
    SELECT 'V' AS [tv], object_id, name FROM sys.views
  ) tbl

  LEFT JOIN sys.columns col ON (col.object_id = tbl.object_id)
  LEFT JOIN sys.types dtp ON (dtp.system_type_id = col.system_type_id AND dtp.user_type_id = col.user_type_id)
  LEFT JOIN sys.identity_columns idt ON (idt.object_id = col.object_id AND idt.column_id = col.column_id)
  LEFT JOIN sys.extended_properties ep ON (ep.major_id = col.object_id AND ep.minor_id = col.column_id AND ep.name = 'MS_Description')
  LEFT JOIN sys.extended_properties tep ON (tep.major_id = tbl.object_id AND tep.minor_id = 0 AND tep.name = 'MS_Description')
--  ORDER BY tbl.name, col.column_id
)
, DOCS AS (
  SELECT * FROM TBLS
  UNION ALL
  SELECT DISTINCT TableId, TableName, tv, -9 AS ColumnId, '', '', '', '', '', '', '', TableComment FROM TBLS --テーブル名行
  UNION ALL
  SELECT DISTINCT TableId, TableName, tv, -8 AS ColumnId, '項目名', 'データ型', 'PKEY', 'NOT NULL', 'IDENTITY', '照合順序', 'コメント', TableComment FROM TBLS --見出し行
  UNION ALL
  SELECT DISTINCT TableId, TableName, tv, 9999 AS ColumnId, '', '', '', '', '', '', '', '' FROM TBLS --空行用
)
SELECT
  CASE WHEN ColumnId = -9 THEN TableName ELSE '' END [テーブル名]
, CASE WHEN ColumnId = -9 THEN tv ELSE '' END [tv]
, ColumnName     AS [項目名]
, ColumnDataType AS [データ型]
, PKey           AS [PKEY]
, NotNull        AS [NOT NULL]
, ColumnIdentity AS [IDENTITY]
, Collation      AS [照合順序]
, CASE WHEN ColumnId BETWEEN 1 AND 9998 THEN
      CASE WHEN ColumnIdentity <> '' THEN ColumnIdentity + '; ' ELSE '' END
    + CONVERT(varchar, ISNULL(ColumnComment, ''))
  ELSE ColumnComment END AS [コメント]
FROM DOCS
WHERE TableName LIKE '%%'  -- ★★★テーブル名
ORDER BY TableName, ColumnId

私も結構前に色々ぐぐって見つけたSQLを切り貼りして作ったものなので
もしどこか間違えていたらごめんなさい…

これは私が見やすいように色々整形していますが、
必要な情報的には最初の WITH 句 <TBLS> の SELECT だけで事足りる筈です。

Comments

[sk_3122]

【追記１】
「データ型/長さ」のあたりは
私が触っているシステムで使ってる型しか網羅していないので
必要に応じて修正が必要かもしれません

【追記２】
私のSQLの中に 『9999 AS ColumnId』 という記述があるのですが、
テーブルのカラムが 9999 以上あると表示がおかしくなります。
（これはテーブル同士の間に空行を入れたくて用意した行です）

その場合は 99999 とかにすれば多分大丈夫です。

[sk_3122]

私のSQL は sys.tables しか見ていなかったので、sys.views も見るよう修正してみました。
合ってそう、に見えます… どうかな…

[ky_46]

ありがとうございます。
PKEYの○がKeyカラム、IDENTITY(1,1)があるのが自動採番ですね。

このリザルトをもとに、MySQLへのCREATE TABLE文を作成するプログラムを作成してみます。

ありがとうございました。