Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 279
  • Last Modified:

Data Dictionary

Looking for a SELECT statement that will retreive the database Table Names, FieldNames ,Field  Descriptions, Feild Types, Field Sizes, Nullable, , etc.. for a database in MS SQL SERVER.
0
rickwinterkorn
Asked:
rickwinterkorn
  • 2
  • 2
1 Solution
 
zimmyCommented:
get positioned in the database in question 'USE dbname'

The following will get you the name and an id number for all the tables:
select id, name from sysobjects where type = 'U' order by name

Substituting each of the id numbers from the previous query into
the following will get you a bunch of stuff about each column in each table.
select * from syscolumns where id = <number>

You can probably figure out what some of the results of this query mean (length, for example). For other stuff you may have to set up a sample table and play with it to find out what 'offset' and 'status' mean.

See Appendix A in Microsoft's Transact SQL Reference for the relationships of all the system tables. Note that there is a system catalog and a database catalog. The system catalog has systemwide data. Each database has its own database catalog with
information specific to that database.

Good luck.

0
 
rickwinterkornAuthor Commented:
Here is an example of what I'm looking for; this is the select statement for Oracle, Sybase has a similar one,  and I'm looking for the similar syntax in MS SQL Server:

SELECT OWNER,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
 DATA_LENGTH,
DATA_PRECISION,
DATA_SCALE,
 NULLABLE,
COLUMN_ID
FROM ALL_TAB_COLUMNS
WHERE OWNER = dbname



0
 
odessaCommented:
I think zimmy has fully answered to your question if you want to do same as in your example just make a view form syscoloumns and sysobjects
0
 
rickwinterkornAuthor Commented:
Ok Thanks Zimmy and Odessa..I'll look into it.  Zimmy I'll credit your previous answer, just make a quick note so I can respond to accept it. Again thanks.  
0
 
zimmyCommented:
OK. Thanks. And thanks, Odessa.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now