Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Get the Table Structure using a simple query Expand / Collapse
Author
Message
Posted Tuesday, September 25, 2007 2:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 8:55 AM
Points: 148, Visits: 70
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
Posted Thursday, May 27, 2010 7:15 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, March 28, 2014 1:46 PM
Points: 40, Visits: 154
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
Posted Sunday, September 12, 2010 11:49 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Monday, September 13, 2010 12:02 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 10, 2014 3:42 AM
Points: 890, Visits: 1,179
I am looking for this query. Thanks

Thanks
Post #984540
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse