Get the Table Structure using a simple query

Question

Post reply

Get the Table Structure using a simple query

Mohit Nayyar

Ten Centuries

Points: 1008
More actions
September 25, 2007 at 2:29 am

#178776

Comments posted to this topic are about the item Get the Table Structure using a simple query
Thanks
Mohit Nayyar
http://mohitnayyar.blogspot.com/
"If I am destined to fail, then I do have a purpose in my life, To fail my destiny"

Viewing 5 posts - 1 through 4 (of 4 total)

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

Ayyappan Thangaraj Old Hand Points: 364 More actions · Answer 1

Dear Mohit,

I have executed the SQL query. It is giving the exact output that you are saying.

I found one issue in the output information. if you look it the datatype, it is showing one strange datatype name sysdata in the table structure actually in should not come.

can you please look into it and change the query?

Thanks,

Ayyappan

Uttam Kr. Dhar Grasshopper Points: 11 More actions · Answer 2

U can get better information in the following query:

Select sysObj.id, sysObj.name, Col.name,

'Datatype'=Case When typ.name in ('char','varchar') Then typ.name+'('+Cast(Col.max_length as varchar(10))+')'

When typ.name = 'nvarchar' AND Col.max_length<>-1 Then typ.name+'('+Cast(Col.max_length/2 as varchar(10))+')'

When typ.name = 'nvarchar' AND Col.max_length=-1 Then typ.name+'(MAX)'

When typ.name = 'decimal' Then typ.name+'('+Cast(Col.precision as varchar(5))+','+Cast(Col.scale as varchar(5))+')'

Else typ.name

End, 'Length'=Col.max_length, 'isNullable'=Case When Col.is_nullable = 1 Then 'Yes' Else 'No' End,

'isIdentity'=Case When Col.is_identity = 1 Then 'Yes' Else 'No' End, Col.column_id

From sys.columns Col

Inner Join sys.sysobjects sysObj ON sysObj.id = Col.object_id

Inner Join sys.types typ ON typ.user_type_id = Col.user_type_id

Where sysObj.xtype = 'U'

and sysObj.name like 'ma_Parts%'

order by sysObj.name, Col.column_id;

Hardy21 SSCrazy Eights Points: 9708 More actions · Answer 3

Hardy21

SSCrazy Eights

Points: 9708

September 13, 2010 at 12:02 am

#1219856

I am looking for this query. Thanks

Thanks

mfazar Grasshopper Points: 11 More actions · Answer 4

SELECT o.name Table_Name, c.name Column_Name, t.name DataType, c.Length, CASE WHEN c.isnullable=1 THEN 'Yes' ELSE 'No' END AS 'Nullable'

FROM sysobjects o

INNER JOIN syscolumns c ON (o.id=c.id and o.type='U' and o.name not like 'dt%')

Inner Join systypes t ON (c.xtype=t.xtype)

WHERE t.name<>'sysname'

order by o.name, c.colorder