SQL查询表与列信息

--查询表名与表备注
select
ROW_NUMBER() OVER (ORDER BY a.object_id) AS No,
a.name AS TabelName,
g.[value] AS TableNotes
from
sys.tables a left join sys.extended_properties g
on (a.object_id = g.major_id AND g.minor_id = 0) ORDER BY a.[name]

--查询列名与列备注
SELECT A.name,b.value FROM (
Select name,id,colid FROM SysColumns Where id=Object_Id( 'TABLENAME')
) A LEFT JOIN (
SELECT major_id,minor_id,value FROM sys.extended_properties WHERE major_id = OBJECT_ID ( 'TABLENAME')
) B ON A.id=b.major_id and a.colid=B.minor_id