view structure of table in sql server2000 in Q.A.

Question

Post reply

view structure of table in sql server2000 in Q.A.

Papillon

Hall of Fame

Points: 3303
More actions
June 15, 2005 at 7:05 am

#61618

Hello friends,
As in Oracle Database we can check the structure of table by
DESC <Table_Name>
But Can we check the same in Sql Server2000?
Thanks In Advance
Shashank
Regards,
Papillon

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

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

Ramanuj SSC Veteran Points: 278 More actions · Answer 1

Ramanuj

SSC Veteran

Points: 278

June 15, 2005 at 7:13 am

#565673

sp_help <tableName>

🙂

sushila SSC-Dedicated Points: 35293 More actions · Answer 2

sp_help is pretty comprehensive...

You can also query the system tables with something like:

select * from syscolumns where id =

(select id from sysobjects where name = )

**ASCII stupid question, get a stupid ANSI !!!**

sushila SSC-Dedicated Points: 35293 More actions · Answer 3

that should read...

select * from syscolumns where id =

(select id from sysobjects where name = 'tablename')

**ASCII stupid question, get a stupid ANSI !!!**

Vishal Gamji-215308 Mr or Mrs. 500 Points: 599 More actions · Answer 4

Try this......this may also help as a base metadata view for generating other code.

SELECT

so.[name] AS 'TableName',

sc.[name] AS 'ColumnName',

st.[name] AS 'ColumnType',

sc.length AS 'ColumnSize',

CASE sc.isnullable

WHEN 0 THEN 'No' ELSE 'Yes' END AS 'IsNullable',

myTemp.RefColumn,

myTemp.RefTable

FROM

sysobjects so

INNER JOIN syscolumns sc ON

so.[id] = sc.[id]

INNER JOIN

systypes st ON

sc.xtype = st.xtype

LEFT OUTER JOIN(

SELECT

sfk.constid AS 'ConstraintID',

sfk.fkeyid AS 'TableID',

sfk.fkey AS 'ColumnID',

sc.[name] AS 'RefColumn',

so.[name] AS 'RefTable',

sfk.rkeyid AS 'TableID Ref',

sfk.rkey AS 'ColumnID Ref'

FROM

sysforeignkeys sfk

INNER JOIN syscolumns sc ON

sfk.rkeyid = sc.[id]

AND

sfk.rkey = sc.colid

INNER JOIN sysobjects so ON

sc.[id] = so.[id]

&nbsp myTemp ON

myTemp.TableID = sc.[id]

AND

myTemp.ColumnID = sc.colid

WHERE

so.type = 'U'

AND

so.[name] NOT LIKE 'dt%'

--SELECT * FROM syscolumns

--SELECT * FROM systypes

DSP-120308 Hall of Fame Points: 3959 More actions · Answer 5

You can also run the following:

select * from information_schema.columns

which will return all column information for each table in the current database

Greg Charles SSC-Forever Points: 45403 More actions · Answer 6

As long as you're using QA, why not just use the Object Browser and save some typing?

Greg

philcart SSC-Forever Points: 47794 More actions · Answer 7

Drag 'N' Drop is your friend.

If only it did the quoting properly when you drag the columns in

--------------------
Colt 45 - the original point and click interface

Papillon Hall of Fame Points: 3303 More actions · Answer 8

Hello friends,

Thanks for that!!!!!!!!!!!!!!!!!!!!!!

shashank

Regards,

Papillon

swede SSCommitted Points: 1773 More actions · Answer 9

swede

SSCommitted

Points: 1773

June 16, 2005 at 11:55 am

#566231

sp_columns tablename

sushila SSC-Dedicated Points: 35293 More actions · Answer 10

Many ways to skin this cat - <;-) - though object browser is the way to go for a quick dekko!!!

**ASCII stupid question, get a stupid ANSI !!!**