Table Structure For 900+ tables

Question

Table Structure For 900+ tables

krishnakhanna

Valued Member

Points: 58
More actions
February 17, 2012 at 6:25 am

#268565

Hi,
I am working on a project and in that we have a requirement to extract the schema for 900+ tables in a database.
if I use SELECT <TABLE_NAME1> from dba_tables; I get only the <TABLE_NAME1>
I want this to be used for TABLE_NAME1...900 at once with a query
Is this possible using a command?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

What exactly are you looking for? Are you trying to get the full ddl of all your tables including indexes, constraints, defaults, triggers, etc??? You should look into some sql documenting tools for this type of thing. Redgate makes one called SQL Doc that works very well and there are others out there too.

If full documentation is not what you are trying to do then a little more explanation of what you want is needed.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 2

you have posted in "working with Oracle" forum

are you looking for the schema of an Oracle db...to create in SQL

or vice versa?

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

krishnakhanna Valued Member Points: 58 More actions · Answer 3

Hi,

Thanks for your response.

I have the following requirements

Currently working in a db which has 900 tables and each table has a schema attached to i

For example

Table Name: columns_priv

Schema: The info which I get from DESC columns_priv

Field Type Null Key Default Extra

Host char(60) PRI

Db char(64) PRI

User char(16) PRI

Select_priv enum('N','Y') N

Insert_priv enum('N','Y') N

Update_priv enum('N','Y') N

Delete_priv enum('N','Y') N

Create_priv enum('N','Y') N

Drop_priv enum('N','Y') N

Grant_priv enum('N','Y') N

References_priv enum('N','Y') N

Index_priv enum('N','Y') N

Alter_priv enum('N','Y') N

Create_tmp_table_priv enum('N','Y') N

Lock_tables_priv enum('N','Y') N

Table Name: stocks

Schema: extracted using DESC stocks

Field Type Collation Attributes Null Default Extra Action

ID int(11) No auto_increment

stock varchar(64) latin1_swedish_ci No

value varchar(16) latin1_swedish_ci No 0

changepoints varchar(16) latin1_swedish_ci No 0

open varchar(16) latin1_swedish_ci No

intra_top varchar(16) latin1_swedish_ci No

intra_down varchar(16) latin1_swedish_ci No

date varchar(10) latin1_swedish_ci No

time varchar(6) latin1_swedish_ci No

unixtime int(12) No 0

reallocaltime int(12) No 0

md5 varchar(32) latin1_swedish_ci No

Like this I have 900 different table and want to extract the table structure for each of them using Db query.

DESC <tablename> works only for one table but my requirement is to have data for all the tables

krishnakhanna Valued Member Points: 58 More actions · Answer 4

I need a particular ORACLE db table schema/structure, but I am more familiar with SQL so that's why I explained the query using it 🙂

Lowell SSC Guru Points: 323518 More actions · Answer 5

the views ALL_TAB_COLUMNS / USER_TAB_COLUMNS has the table name and the raw column info like column name, data type, column id , etc just like our sys.columns;

is that what you are after?

select * from all_tab_columns

select * from user_tab_columns --only the user tables? do you need all tables?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

krishnakhanna Valued Member Points: 58 More actions · Answer 6

Basically structure data like the following for all users and tables

Name Null? Type

----------------------------------------- -------- ----------------------------

valid_number NOT NULL VARCHAR2(20)

data1 NOT NULL TIMESTAMP(0)

Lowell SSC Guru Points: 323518 More actions · Answer 7

krishnakhanna (2/17/2012)
Basically structure data like the following for all users and tables
Name Null? Type
----------------------------------------- -------- ----------------------------
valid_number NOT NULL VARCHAR2(20)
data1 NOT NULL TIMESTAMP(0)

yep i pointed you to the right table, but it requires work on your part.

allt he data is in there, you need to extract it into the format you want...you need to use a bunch of case statemetns for various stuff;

here's an incomplete but working oracle example for you to expand on:

select

column_name

|| ' '

|| data_type

|| ' '

|| CASE

WHEN DATA_TYPE IN('NUMBER','VARCHAR2')

THEN '(' || DATA_LENGTH || ') '

ELSE ' '

END

|| CASE

WHEN NULLABLE = 'Y'

THEN ' NULL'

ELSE 'NOT NULL'

END

from user_tab_columns

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 8

...as an alternative:

SELECT OWNER, TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH, DATA_PRECISION, DECODE(NULLABLE, 'Y', 'NULL', 'NOT NULL')

FROM dba_tab_columns

WHERE OWNER = '???' --<< Plug Schema Name Here

ORDER BY OWNER, TABLE_NAME, COLUMN_ID

;

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Jo Pattyn SSC-Dedicated Points: 32347 More actions · Answer 9

You will probably need various all_... or dba... views (all_constraints, all_indexes, ...).

For full Oracle DDL you'll need a tool that can use DBMS_METADATA. (only used Toad dba-module sofar)

Steve Wales Newbie Points: 1 More actions · Answer 10

Expanding upon what Lowell and PaulB have said, I recently needed to get the same information myself.

I wrote this query:

select

table_name,

column_name,

decode(data_type,

'VARCHAR2',data_type||'('||data_length||')',

'CHAR' ,data_type||'('||data_length||')',

'NVARCHAR2' ,data_type||'('||data_length||')',

'NCHAR' ,data_type||'('||data_length||')',

'NUMBER' ,decode(data_precision,

null, data_type,

data_type||'('||data_precision||','||data_scale||')'),

data_type) as DATA_TYPE,

decode(nullable, 'Y', ' ', 'NOT NULL') as NULLABLE

from dba_tab_columns

where owner = 'MYSCHEMA'

order by table_name, column_id

I have an example of a sample table with about every data type I could think of here:

http://dba-in-exile.blogspot.com/2011/09/so-what-do-my-tables-look-like-anyway.html

Another excellent product for extracting the Oracle DBMS_METADATA is PL/SQL Developer by All Round Automations: http://www.allroundautomations.com/plsqldev.html

Steve