ALL_TABLES - Catalog Views - Oracle to SQL Server Migration

In Oracle, data dictionary views (catalog views) ALL_TABLES, DBA_TABLES and USER_TABLES contain information on tables:

• ALL_TABLES - All tables accessible to the current user
• DBA_TABLES - All tables in the database (SELECT ANY TABLE privilege or DBA role required)
• USER_TABLES - All tables tables owned by the current user

In SQL Server, you can query sys.tables catalog view to obtain information on tables. Note that sys.tables returns information for the current database only, and you can also specify db_name.sys.tables to query the specified database.

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

In Oracle, ALL_TABLES, DBA_TABLES and USER_TABLES have the same columns, except that USER_TABLES does not contain owner column.

In Oracle, you can use the following query to select all tables in the specified schema:

Oracle:

  -- Select all tables in SALES schema
  SELECT owner, table_name FROM all_tables WHERE owner = 'SALES';

Sample output:

Usually an Oracle schema is migrated to a separate database in SQL Server, so you can query sys.tables view from the specified database to get the tables.

sys.tables view contains schema_id, and use can use SCHEMA_NAME function to obtain the schema name.

SQL Server:

  -- Select all tables in SALES database
  SELECT SCHEMA_NAME(schema_id) AS owner, name FROM sales.sys.tables;

Sample output:

In Oracle, DBA_TABLES view contains tables in all schemas in the database, and ALL_TABLES view contains all tables in the databases accessible to the current user.

Usually you need to exclude tables located in system schemas such as SYS, SYSTEM, MDSYS, CTXSYS etc.

Oracle:

  -- Select all tables in all schemas (accessible to the current user) excluding system tables
  SELECT owner, table_name FROM all_tables WHERE owner NOT IN ('SYS', 'SYSTEM', 'MDSYS', 'CTXSYS');

Sample output:

If you migrate each Oracle schema to a standalone database in SQL Server, you may need to query sys.tables in each database to get the full list of tables.

You can use the following Transact-SQL script that automatically retrieves the list of available databases in SQL Server, and returns all tables available in them (excluding the system databases):

SQL Server:

  DECLARE @sql NVARCHAR(max) = '';
 
  -- Build the query to select all tables from all databases
  SELECT @sql = @sql + 'SELECT ''' + name + ''' AS db_name, name FROM ' + name + '.sys.tables UNION ALL '
  FROM sys.databases 
  WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb');
 
  -- Remove last UNION ALL
  SET @sql = SUBSTRING(@sql, 1, LEN(@sql) - 10);
 
  -- Execute the query (returns all tables in all databases as a single result set)
  EXEC sp_executesql @sql;
  GO

Sample output:

• Oracle to SQL Server Migration Knowledge Base

Dmitry Tolpeko, dmtolpeko@sqlines.com - July 2012

Please contact me if you are interested in Oracle to SQL Server migration services, consulting, tools or training.