SQL Server queries all columns under all tables and tables

Source: Internet
Author: User

Tag:select    occupancy    identity   types   from    join   property   .class   case   

select  (case when a.colorder=1 then d.name else null end)   table name,     a.colorder  field ordinal,a.name  field name,   (Case when columnproperty (  a.id,a.name, ' isidentity ') =1 then  ' √ ' else  '  end '   logo,    (case  when  (Select count (*)  FROM sysobjects    WHERE  (name  in  (select name from sysindexes    where  (id = a.id)  AND  (indid in     (select indid from sysindexkeys     WHERE  (id = a.id)  AND  (colid in     (SELECT  colid FROM syscolumns WHERE  (id = a.id)  AND  (name =  (A.name))))))    AND  (xtype =  ' PK ')) >0 then  ' √ '  else  '  end)   PRIMARY key,b.name  type,a.length  occupies bytes,    columnproperty (a.id,a.name, ' PRECISION ')  as  length,    isnull (ColumnProperty (a.id,a.name, ' scale '), 0)  as  decimal place, (case when a.isnullable=1  then  ' √ ' else  '  end)   allow null,    isnull (E.text, ')   default, IsNull (g.[ value],  '   ')  as [description] from  syscolumns a  left join  systypes b on a.xtype=b.xusertype   inner join sysobjects d  on a.id=d.id and d.xtype= ' U '  and d.name<> ' dtproperties '   left  join syscomments e on a.cdefault=e.id   left join  sys.extended_properties g on a.id=g.major_id and a.colid=g.minor_id left  join sys.extended_properties f on d.id=f.class and f.minor_id=0 where  b.name is not null --where d.name= ' table to query '  --if you only query the specified table, add this condition  order by a.id,a.colorder


SQL Server queries all columns under all tables and tables

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.