473,836 Members | 1,553 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 19806
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_Column s views in Oracle.

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

Joe
"Hitesh" <ze******@hotma il.com> wrote in message
news:30******** *************** **@posting.goog le.com...
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_SCH EMA.TABLES

All information about all columns:

SELECT * FROM INFORMATION_SCH EMA.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******@hotma il.com> wrote in message
news:30******** *************** **@posting.goog le.com...
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...someon e 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_sch ema. when run the above query, i get hidden system
tables and approximately 600 rows of data, where as the
information_sch ema comman queries 250+ rows. anyone know what is the
difference between these two? thanks

SELECT * FROM INFORMATION_SCH EMA.TABLES
SELECT * FROM INFORMATION_SCH EMA.COLUMNS


Hugo Kornelis <hugo@pe_NO_rFa ct.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_SCH EMA.TABLES

All information about all columns:

SELECT * FROM INFORMATION_SCH EMA.COLUMNS
Best, Hugo

Jul 20 '05 #5
On 19 May 2004 09:16:57 -0700, Hitesh wrote:
thanks...someo ne 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_sc hema. when run the above query, i get hidden system
tables and approximately 600 rows of data, where as the
information_sc hema comman queries 250+ rows. anyone know what is the
difference between these two? thanks

SELECT * FROM INFORMATION_SCH EMA.TABLES
SELECT * FROM INFORMATION_SCH EMA.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_SC HEMA views conform to the ANSI standard
for SQL. If the structure of the system tables is changed in a future
version, the INFORMATION_SCH EMA 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_rFa ct.in_SPAM_fo> wrote in message news:<hg******* *************** **********@4ax. com>...
On 19 May 2004 09:16:57 -0700, Hitesh wrote:
thanks...someo ne 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_sc hema. when run the above query, i get hidden system
tables and approximately 600 rows of data, where as the
information_sc hema comman queries 250+ rows. anyone know what is the
difference between these two? thanks

SELECT * FROM INFORMATION_SCH EMA.TABLES
SELECT * FROM INFORMATION_SCH EMA.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_SC HEMA views conform to the ANSI standard
for SQL. If the structure of the system tables is changed in a future
version, the INFORMATION_SCH EMA 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
4447
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 sorted columns. to do this at the moment i am using perl, which is very easy to do, and i want to see if python is as easy. so, the data i am using is some epiphyte population abundance data for particular sites, and it looks like this:
3
1594
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
2418
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 needs to create a Dictionary *object* to be populated with values that come from a text file with a format like this: <dict.txt>
7
21150
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 way of expressing this, but I have a problem with the way lists are represented when converted to strings. Lets say my dictionary is
2
2116
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 required changes to the database per release, you'd update the Data Dictionary. When you wanted to create a new database, it would create it completely from the data dictionary. If you already had a functional database and the new release required...
1
1904
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 database schema up-to- date with changes in the program. I found ADDA (Advanced Data Dictionary Architect) but have been unable to download it becaue of an error on the web site. It creates an XML file that can be packaged with the application. When...
4
3525
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 keyname third keyword1 3.1
13
3424
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. The first column is always an English
7
1634
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 ... I often need to look up in this way:
1
2851
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 I want, is this: I would like to create a 'data dictionary' that stores the data name and type. Whenever I need a new function parameter or local variable, and it's manipulating an item that is described in the data dictionary, I want to be able...
0
9814
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
10838
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10544
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
10250
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9369
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7788
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5645
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5821
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4447
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

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.