November 22, 2005 at 10:07 am
I'm running Microsoft SQL Server 2000 and I am looking for a command similar to the DESCRIBE command in Oracle. Is there a Information_schema that does this? The Oracle command functions as follows:
Syntax: DESCRIBE <tablename or viewname>
Example:
SQL> DESC t_Movie
Name Null? Type
------------------------------- -------- ----
MOVIE_ID CHAR(6)
DIRECTOR_ID CHAR(5)
TITLE CHAR(20)
YEAR_REL CHAR(4)
RATING CHAR(5)
GENRE CHAR(10)
LENGTH CHAR(8)
Thanks for the help
Bryan
November 22, 2005 at 10:09 am
Try sp_help. Look up BOL (Books Online)
sp_help <Table-Name>
November 23, 2005 at 5:18 am
And there are a lot of information_schema views as well that will give you most of the information that you need about the meta-data. Search this site for information_schema and you will end up with some good links. Also, look up BOL for it.
November 23, 2005 at 11:59 am
Run the following query will give you a similar result as in Oracle DESC command:
SELECT c.colid 'Column Order',
c.name 'Column Name',
UPPER(t.name) 'Data Type',
CASE WHEN t.name = 'datetime' THEN ''
ELSE c.prec END 'Size',
CASE WHEN t.name = 'numeric' THEN c.scale
ELSE ' ' END 'Decimal Place',
CASE WHEN c.isnullable = 0 THEN ''
ELSE 'YES' END 'Nullable'
FROM syscolumns c, sysobjects o, systypes t
WHERE c.id = o.id
AND c.usertype = t.usertype
AND o.name = 'Your_Table_Name'
ORDER BY colid
I personally would prefer to create a sp. Each time I simply exec the sp and it gives me the result.
Hope this help!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply