SQLServerCentral.com / Discuss content posted by Mohit Nayyar / Article Discussions by Author / Article Discussions / Get the Table Structure using a simple query / Latest PostsInstantForum.NET v99.99.99SQLServerCentral.comhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 09 Dec 2016 18:46:43 GMT20RE: Get the Table Structure using a simple queryhttp://www.sqlservercentral.com/Forums/Topic402431-613-1.aspxSELECT 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 oINNER 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&lt;&gt;'sysname'order by o.name, c.colorderWed, 24 Aug 2016 11:40:44 GMTmfazarRE: Get the Table Structure using a simple queryhttp://www.sqlservercentral.com/Forums/Topic402431-613-1.aspxI am looking for this query. ThanksMon, 13 Sep 2010 00:02:19 GMTHardy21RE: Get the Table Structure using a simple queryhttp://www.sqlservercentral.com/Forums/Topic402431-613-1.aspxU 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&lt;&gt;-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.nameEnd, '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_idFrom sys.columns Col Inner Join sys.sysobjects sysObj ON sysObj.id = Col.object_idInner Join sys.types typ ON typ.user_type_id = Col.user_type_idWhere sysObj.xtype = 'U'and sysObj.name like 'ma_Parts%'order by sysObj.name, Col.column_id;Sun, 12 Sep 2010 23:49:06 GMTUttam Kr. DharRE: Get the Table Structure using a simple queryhttp://www.sqlservercentral.com/Forums/Topic402431-613-1.aspxDear 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,AyyappanThu, 27 May 2010 07:15:26 GMTAyyappan ThangarajGet the Table Structure using a simple queryhttp://www.sqlservercentral.com/Forums/Topic402431-613-1.aspxComments posted to this topic are about the item [B]<A HREF="/scripts/Miscellaneous/30738/">Get the Table Structure using a simple query</A>[/B]Tue, 25 Sep 2007 02:29:52 GMTMohit Nayyar