Get the Table Structure using a simple query

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Mohit Nayyar » Get the Table Structure using a simple query

Get the Table Structure using a simple query

Rate Topic

Display Mode

Topic Options

Author

Message

Mohit Nayyar

Posted Tuesday, September 25, 2007 2:29 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 30, 2012 7:36 PM
Points: 148, Visits: 69

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"

Post #402431

Ayyappan Thangaraj

Posted Thursday, May 27, 2010 7:15 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, April 27, 2013 11:14 AM
Points: 40, Visits: 151

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

Post #928960

Uttam Kr. Dhar

Posted Sunday, September 12, 2010 11:49 PM

Forum Newbie

Group: General Forum Members
Last Login: Sunday, September 12, 2010 11:47 PM
Points: 1, Visits: 7

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;

Post #984539

Hardy21

Posted Monday, September 13, 2010 12:02 AM

SSC Eights!

Group: General Forum Members
Last Login: Today @ 7:11 AM
Points: 877, Visits: 1,159

I am looking for this query. Thanks

Thanks

Post #984540

« Prev Topic | Next Topic »

Permissions