473,320 Members | 2,111 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

data dictionary

Hello,

I am working on a project to reverse engineer requirements for a
database. The database is sitting on a MS SQL Server. How would I
get a listing of all tables and columns within each table to help
create a data dictionary.

I have developed a script in Oracle to do this, but I am just not
familiar with MS SQL Server synthax.

Thanks,

Hitesh
Jul 20 '05 #1
6 19785
Joe
Hi Hitesh

I'm an Oracle developer as well, so sorry for the vagueness...

I think you want to use some stored procedures. sp_tables and sp_columns
are system stored procedures that return the same info as the User_Tables
and User_Tab_Columns views in Oracle.

Do you have the SQL Server client tools installed? Books Online
explains the use of these procedures.

Joe
"Hitesh" <ze******@hotmail.com> wrote in message
news:30*************************@posting.google.co m...
Hello,

I am working on a project to reverse engineer requirements for a
database. The database is sitting on a MS SQL Server. How would I
get a listing of all tables and columns within each table to help
create a data dictionary.

I have developed a script in Oracle to do this, but I am just not
familiar with MS SQL Server synthax.

Thanks,

Hitesh

Jul 20 '05 #2
On 18 May 2004 10:20:24 -0700, Hitesh wrote:
Hello,

I am working on a project to reverse engineer requirements for a
database. The database is sitting on a MS SQL Server. How would I
get a listing of all tables and columns within each table to help
create a data dictionary.

I have developed a script in Oracle to do this, but I am just not
familiar with MS SQL Server synthax.

Thanks,

Hitesh


Hi Hitesh,

All information about all tables:

SELECT * FROM INFORMATION_SCHEMA.TABLES

All information about all columns:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #3
If you have Enterprise Manager, you can also create Database Diagrams
containing some or all of the tables.

"Hitesh" <ze******@hotmail.com> wrote in message
news:30*************************@posting.google.co m...
Hello,

I am working on a project to reverse engineer requirements for a
database. The database is sitting on a MS SQL Server. How would I
get a listing of all tables and columns within each table to help
create a data dictionary.

I have developed a script in Oracle to do this, but I am just not
familiar with MS SQL Server synthax.

Thanks,

Hitesh

Jul 20 '05 #4
thanks...someone also suggested using the following

/*P = Stored Procedure
U = Table
PK = Primary Key
V = View
TR = Trigger
the 'xtype' is the type of object it is */

select so.name as obj_name,
sc.name as colm_name,
so.xtype
from sysobjects so (nolock),
syscolumns sc (nolock)
where so.id *= sc.id
order by so.name, sc.name
the above command seems to be more comprehensive than using
information_schema. when run the above query, i get hidden system
tables and approximately 600 rows of data, where as the
information_schema comman queries 250+ rows. anyone know what is the
difference between these two? thanks

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS


Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<9e********************************@4ax.com>. ..
On 18 May 2004 10:20:24 -0700, Hitesh wrote:
Hello,

I am working on a project to reverse engineer requirements for a
database. The database is sitting on a MS SQL Server. How would I
get a listing of all tables and columns within each table to help
create a data dictionary.

I have developed a script in Oracle to do this, but I am just not
familiar with MS SQL Server synthax.

Thanks,

Hitesh


Hi Hitesh,

All information about all tables:

SELECT * FROM INFORMATION_SCHEMA.TABLES

All information about all columns:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
Best, Hugo

Jul 20 '05 #5
On 19 May 2004 09:16:57 -0700, Hitesh wrote:
thanks...someone also suggested using the following

/*P = Stored Procedure
U = Table
PK = Primary Key
V = View
TR = Trigger
the 'xtype' is the type of object it is */

select so.name as obj_name,
sc.name as colm_name,
so.xtype
from sysobjects so (nolock),
syscolumns sc (nolock)
where so.id *= sc.id
order by so.name, sc.name
the above command seems to be more comprehensive than using
information_schema. when run the above query, i get hidden system
tables and approximately 600 rows of data, where as the
information_schema comman queries 250+ rows. anyone know what is the
difference between these two? thanks

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS


Hi Hitesh,

The query you issued against the system tables will also include stored
procedures, constraints, triggers etc. That should explain the different
number of rows.

There is nothing wring with directly querying the system tables if you
really know what you're doing and if you're not bothered by upward
compatibility. The INFORMATIION_SCHEMA views conform to the ANSI standard
for SQL. If the structure of the system tables is changed in a future
version, the INFORMATION_SCHEMA views will be changed as well, to ensure
ANSI compliance. You'll have to modify your queries against the system
tables yourself.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #6
thanks for clarifying

Hugo Kornelis <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:<hg********************************@4ax.com>. ..
On 19 May 2004 09:16:57 -0700, Hitesh wrote:
thanks...someone also suggested using the following

/*P = Stored Procedure
U = Table
PK = Primary Key
V = View
TR = Trigger
the 'xtype' is the type of object it is */

select so.name as obj_name,
sc.name as colm_name,
so.xtype
from sysobjects so (nolock),
syscolumns sc (nolock)
where so.id *= sc.id
order by so.name, sc.name
the above command seems to be more comprehensive than using
information_schema. when run the above query, i get hidden system
tables and approximately 600 rows of data, where as the
information_schema comman queries 250+ rows. anyone know what is the
difference between these two? thanks

SELECT * FROM INFORMATION_SCHEMA.TABLES
SELECT * FROM INFORMATION_SCHEMA.COLUMNS


Hi Hitesh,

The query you issued against the system tables will also include stored
procedures, constraints, triggers etc. That should explain the different
number of rows.

There is nothing wring with directly querying the system tables if you
really know what you're doing and if you're not bothered by upward
compatibility. The INFORMATIION_SCHEMA views conform to the ANSI standard
for SQL. If the structure of the system tables is changed in a future
version, the INFORMATION_SCHEMA views will be changed as well, to ensure
ANSI compliance. You'll have to modify your queries against the system
tables yourself.

Best, Hugo

Jul 20 '05 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: ben moretti | last post by:
hi i'm learning python, and one area i'd use it for is data management in scientific computing. in the case i've tried i want to reformat a data file from a normalised list to a matrix with some...
3
by: kbass | last post by:
I have retrieved data from a database and I want to place this data into a dictionary or something like a HashMap (in Java). Can someone point me to an example? kbass
9
by: cppaddict | last post by:
Let's say you want to implement a Dictionary class, which contains a vector of DictionaryEntry. Assume each DictionaryEntry has two members, a word and a definition. Now assume your program...
7
by: David Bear | last post by:
I have a dictionary that contains a row of data intended for a data base. The dictionary keys are the field names. The values are the values to be inserted. I am looking for a good pythonic...
2
by: joe.kimbler | last post by:
What is the best way to handle updates in databases with each release of a software package? I used to work on an accounting package in FoxPro that had a "Data Dictionary" and as your code...
1
by: jkimbler | last post by:
I'm wondering if anyone has come across any good components, articles, or books about creating a data dictionary for an application. I need to be able to allow the data dictionary keep the...
4
dshimer
by: dshimer | last post by:
I have a file whose structure in strictly generic terms is similar to the following.keyname first keyword1 1.1 keyword2 1.2 keyword3 1.3 keyname second keyword1 2.1 keyword2 2.2 keyword3 2.3...
13
by: liujiaping | last post by:
Hi, all. I have a dictionary-like file which has the following format: first 4 column 7 is 9 a 23 word 134 .... Every line has two columns....
7
by: bahoo | last post by:
Hi, My data is organized like this, where A always goes from 1 to some number N: A 1 2 3 ... B 53,26 42,18 15,86 ... C 59 43 31 ......
1
by: Kami | last post by:
I spent ALL day playing with named parameters instead of positional parameters to functions, and ended up throwing it all away. C just doesn't make that easy or clean. But the basic gist of what...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.