SQL Server スキーマ取得

全データベース取得

USE MASTER 
DECLARE @policy_enabled BIT
SET @policy_enabled = (
  SELECT CONVERT(BIT, current_value) 
  FROM msdb.dbo.syspolicy_configuration 
  WHERE name = 'Enabled'
)

SELECT
  db.name AS [DBName],
  'Server[@Name=' + QUOTENAME(CAST(SERVERPROPERTY(N'Servername') AS SYSNAME), '''') + ']' +
  '/Database[@Name=' + QUOTENAME(db.name, '''') + ']' AS [DBUrn],
  CASE 
    WHEN @policy_enabled = 1 AND EXISTS (
      SELECT * 
      FROM msdb.dbo.syspolicy_system_health_state 
      WHERE target_query_expression_with_id LIKE 'Server/Database[@ID=' + CAST(db.database_id AS NVARCHAR(20)) + ']%'
    ) THEN 1 
    ELSE 0 
  END AS [PolicyStatus],
  db.recovery_model AS [RecoveryModel],
  SUSER_SNAME(db.owner_sid) AS [Owner],
  db.compatibility_level AS [Compatibility]
FROM 
  sys.databases db
LEFT JOIN 
  sys.database_mirroring dm ON dm.database_id = db.database_id
WHERE 
  CAST(CASE 
    WHEN db.name IN ('master','model','msdb','tempdb') THEN 1 
    ELSE db.is_distributor 
  END AS BIT) = 0
ORDER BY 
  [DBName] ASC

テーブル一覧取得

USE [TargetDB]
DECLARE @policy_active BIT
SET @policy_active = (
  SELECT CONVERT(BIT, current_value) 
  FROM msdb.dbo.syspolicy_configuration 
  WHERE name = 'Enabled'
)

SELECT
  t.name AS [Table],
  SCHEMA_NAME(t.schema_id) AS [Schema],
  'Server[@Name=' + QUOTENAME(CAST(SERVERPROPERTY(N'Servername') AS SYSNAME),'''') + ']' +
  '/Database[@Name=' + QUOTENAME(DB_NAME(),'''') + ']' +
  '/Table[@Name=' + QUOTENAME(t.name,'''') + ' and @Schema=' + 
  QUOTENAME(SCHEMA_NAME(t.schema_id),'''') + ']' AS [TableUrn],
  t.create_date AS [Created]
FROM 
  sys.tables t
INNER JOIN 
  sys.databases db ON db.name = DB_NAME()
WHERE 
  CAST(CASE 
    WHEN t.is_ms_shipped = 1 THEN 1
    WHEN EXISTS (
      SELECT * 
      FROM sys.extended_properties 
      WHERE major_id = t.object_id 
        AND minor_id = 0 
        AND class = 1 
        AND name = N'microsoft_database_tools_support'
    ) THEN 1 
    ELSE 0 
  END AS BIT) = 0
ORDER BY 
  [Schema], [Table]

テーブル列情報取得

USE [TargetDB]
SELECT
  c.name AS [Column],
  'Server[@Name=' + QUOTENAME(CAST(SERVERPROPERTY(N'Servername') AS SYSNAME),'''') + ']' +
  '/Database[@Name=' + QUOTENAME(DB_NAME(),'''') + ']' +
  '/Table[@Name=' + QUOTENAME(t.name,'''') + ' and @Schema=' + 
  QUOTENAME(SCHEMA_NAME(t.schema_id),'''') + ']' +
  '/Column[@Name=' + QUOTENAME(c.name,'''') + ']' AS [ColumnUrn],
  ty.name AS [DataType],
  CAST(CASE 
    WHEN ty.name IN (N'nchar', N'nvarchar') AND c.max_length <> -1 
    THEN c.max_length/2 
    ELSE c.max_length 
  END AS INT) AS [Length],
  c.precision AS [Precision],
  c.scale AS [Scale],
  c.is_nullable AS [Nullable],
  c.is_identity AS [IsIdentity],
  OBJECT_DEFINITION(c.default_object_id) AS [DefaultValue],
  ep.value AS [Description]
FROM 
  sys.tables t
JOIN 
  sys.all_columns c ON c.object_id = t.object_id
JOIN 
  sys.types ty ON ty.user_type_id = c.user_type_id
LEFT JOIN 
  sys.extended_properties ep ON ep.major_id = c.object_id 
    AND ep.minor_id = c.column_id
WHERE 
  t.name = 'TargetTable'
  AND SCHEMA_NAME(t.schema_id) = 'dbo'
ORDER BY 
  c.column_id

Oracle スキーマ取得

接続文字列

string connectionString = 
  $"Data Source='{dbInstance}';User Id='{user}';Password='{password}';";

TNS名取得

public static string[] FetchTnsNames()
{
  string[] regPaths = { 
    @"ORACLE\HOME0", 
    @"ORACLE\KEY_OraClient11g_home1",
    @"ORACLE\KEY_OraDb11g_home1"
  };
  
  string oracleHome = null;
  using (RegistryKey root = Registry.LocalMachine.OpenSubKey("SOFTWARE"))
  {
    foreach (var path in regPaths)
    {
      var key = root.OpenSubKey(path);
      if (key != null)
      {
        oracleHome = key.GetValue("ORACLE_HOME") as string;
        if (!string.IsNullOrEmpty(oracleHome)) break;
      }
    }
  }
  
  if (oracleHome == null) return null;
  
  var tnsFile = Path.Combine(oracleHome, @"network\ADMIN\tnsnames.ora");
  var tnsEntries = new List<string>();
  
  foreach (var line in File.ReadLines(tnsFile))
  {
    var trimmed = line.Trim();
    if (trimmed.Length > 0 && char.IsLetter(trimmed[0]))
    {
      int eqIndex = trimmed.IndexOf('=');
      if (eqIndex > 0)
      {
        tnsEntries.Add(trimmed[..eqIndex].Trim());
      }
    }
  }
  return tnsEntries.ToArray();
}

テーブル一覧取得

SELECT table_name 
FROM user_tables 
ORDER BY table_name;

列情報取得

SELECT
  col.column_name AS "列名",
  col.data_type || '(' || col.data_length || ')' AS "データ型",
  com.comments AS "説明",
  CASE WHEN cons.column_name IS NOT NULL THEN 'PK' END AS "キー"
FROM user_tab_columns col
LEFT JOIN user_col_comments com 
  ON com.table_name = col.table_name 
  AND com.column_name = col.column_name
LEFT JOIN (
  SELECT c.table_name, d.column_name
  FROM user_constraints c
  JOIN user_cons_columns d 
    ON d.constraint_name = c.constraint_name
  WHERE c.constraint_type = 'P'
) cons ON cons.table_name = col.table_name 
  AND cons.column_name = col.column_name
WHERE col.table_name = '対象テーブル'
ORDER BY col.column_id