Monday, February 15, 2010

MS SQL / Oracle : Get all Table / Column names from database

MS SQL

Get all table names of particular database
Query: select NAME from SYSOBJECTS where TYPE = 'U'

Get all column names of a table
Query: SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.Columns where TABLE_NAME = '<table name>'

Oracle

Get all table names of particular database
Query: select TABLE_NAME from DBA_TABLES where OWNER like '<database name>'

Get all column names of a table
Query: select COLUMN_NAME from ALL_TAB_COLS where TABLE_NAME ='<table name>'