In Oracle, data dictionary views (catalog views) ALL_TABLES, DBA_TABLES and USER_TABLES contain information on tables:
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.
Oracle - ALL_TABLES | SQL Server - sys.tables | |||
Column | Data Type | Column | Data Type | Notes |
OWNER | VARCHAR2(30) | schema_id | INT | SCHEMA_NAME(schema_id) to get the name |
TABLE_NAME | VARCHAR2(30) | name | sysname |
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:
owner | table_name |
SALES | CITIES |
SALES | STATES |
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:
owner | name |
dbo | CITIES |
dbo | STATES |
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:
owner | table_name |
SALES | CITIES |
SALES | STATES |
HR | PEOPLE |
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:
db_name | name |
SALES | CITIES |
SALES | STATES |
HR | PEOPLE |
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.