sqlserver 查询 字段

SELECT * FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME='MenuInfo' 

select * from sysobjects where name='MenuInfo' --查询所有表
select * from sysobjects where 1=1  AND  xtype='U' --查询用户创建所有表
select * from syscolumns where  1=1 AND id='565577053'   --查询 指定id(也就是表)下的所有列名
select *  from systypes where 1=1  AND status=0 AND xtype ='231'  --查询 系统类型表

 

select t1.name,t1.id as t1id, t4.id as t4id from(
        select name,id,colid,xtype  FROM SYSCOLUMNS   where 1=1  
        AND id=(select id from sysobjects where 1=1  AND  xtype='U'  AND name='MenuInfo')
        AND (name='MenuName' or name='Id')
    )t1
    left join
    (
        select id,colid from SYSINDEXKEYS   --sys index keys   系统主键表
        where 1=1 and  id ='565577053'  --这个id 是表名id; 用户所创建的表中的id，
        -- 查询 出的数据 是 
        /*
            --sysindexkeys
            id             colid
            565577053     2           //这个colid=2 表示 这个是主键

            --syscolums             系统所有列的数据信息
            name            id        colid    xtype
            MenuName    565577053    1        231
            Id            565577053    2        56
        */
    )t4 
    on
    t4.id = t1.id
    and
    t1.colid = t4.colid 

          select name,id,colid,xtype, case when COLUMNPROPERTY(id,name,'IsIdentity') = 1 then 'true' else 'false' end as  自增 FROM SYSCOLUMNS   where 1=1  
        AND id=(select id from sysobjects where 1=1  AND  xtype='U'  AND name='MenuInfo')
        AND (name='MenuName' or name='Id')

          select name,id,colid,xtype,  COLUMNPROPERTY(id,name,'IsIdentity') as  自增 FROM SYSCOLUMNS   where 1=1  
        AND id=(select id from sysobjects where 1=1  AND  xtype='U'  AND name='MenuInfo')
        AND (name='MenuName' or name='Id')

 

 --转自 

https://blog.csdn.net/xiaozaq/article/details/58584970

//加以修改 

-------------最终版
        select t1.name,t3.name,t1.[length],t1.isnullable,t2.id as 主键,COLUMNPROPERTY(t1.id,t1.name,'IsIdentity') as  自增  from(
            select name,id,colid,xtype,[length],isnullable  FROM SYSCOLUMNS   where 1=1  
            AND id=(select id from sysobjects where 1=1  AND  xtype='U'  AND name='MenuInfo')
        )t1
        left join
        (
            select id,colid from SYSINDEXKEYS   --sys index keys   系统主键表
        )t2 
        on
        t2.id = t1.id
        and t1.colid = t2.colid 
        left join
        (
            select name,xtype  from systypes where 1=1  AND status=0   --AND xtype ='231'
        )t3
        on
        t1.xtype = t3.xtype

 

select
     c.name as [字段名],t.name as [字段类型]
     ,convert(bit,c.IsNullable)  as [可否为空]
     ,convert(bit,case when exists(select 1 from sysobjects where xtype='PK' and parent_obj=c.id and name in (
         select name from sysindexes where indid in(
             select indid from sysindexkeys where id = c.id and colid=c.colid))) then 1 else 0 end) 
                 as [是否主键]
    ,convert(bit,case when exists(select 1 from syscolumns col,sysforeignkeys f 
    where f.fkeyid=col.id and col.name=c.name and f.fkey=col.colid and f.constid in ( 
    select distinct(id)    from sysobjects   where OBJECT_NAME(parent_obj)='User' and xtype='F'
    )) then 1 else 0 end)  as [是否外键]
     ,convert(bit,COLUMNPROPERTY(c.id,c.name,'IsIdentity')) as [自动增长]
     ,c.Length as [占用字节] 
     ,COLUMNPROPERTY(c.id,c.name,'PRECISION') as [长度]
     ,isnull(COLUMNPROPERTY(c.id,c.name,'Scale'),0) as [小数位数]
     ,ISNULL(CM.text,'') as [默认值]
     ,isnull(ETP.value,'') AS [字段描述]
     --,ROW_NUMBER() OVER (ORDER BY C.name) AS [Row]
from syscolumns c
inner join systypes t on c.xusertype = t.xusertype 
left join sys.extended_properties ETP on ETP.major_id = c.id and ETP.minor_id = c.colid and ETP.name ='MS_Description' 
left join syscomments CM on c.cdefault=CM.id
where c.id = object_id('MenuInfo')

 --

select * from sys.columns where object_id=object_id('MenuInfo')

 

 

--查询注释

select 
a.name as table_name, 
b.name as column_name, 
c.value as remarks   
from sys.tables a left join sys.columns b on a.object_id=b.object_id  
    left join sys.extended_properties c on a.object_id=c.major_id  
    where a.name='db_table5' and c.minor_id<>0 and b.column_id=c.minor_id  
    and a.schema_id=(  
        select schema_id from sys.schemas where name='dbo'  
    )
--查询注释所需要的表
select * from sys.tables 
select * from sys.columns
select * from sys.extended_properties  --备注
select * from sys.schemas
---其他表
select * from sysobjects  
select * from syscolumns
SELECT * FROM INFORMATION_SCHEMA.columns
select * FROM SYSINDEXKEYS
select * FROM systypes

 

---

--查看表的所有字段注释
use FileManageDB;
SELECT [ColumnName] = [Columns].name ,
        [Description] = [Properties].value,
        [SystemTypeName] = [Types].name ,
        [Precision] = [Columns].precision ,
        [Scale] = [Columns].scale ,
        [MaxLength] = [Columns].max_length ,
        [IsNullable] = [Columns].is_nullable ,
        [IsRowGUIDCol] = [Columns].is_rowguidcol ,
        [IsIdentity] = [Columns].is_identity ,
        [IsComputed] = [Columns].is_computed ,
        [IsXmlDocument] = [Columns].is_xml_document 
FROM    sys.tables AS [Tables]
        INNER JOIN sys.columns AS [Columns] ON [Tables].object_id = [Columns].object_id
        INNER JOIN sys.types AS [Types] ON [Columns].system_type_id = [Types].system_type_id
                                           AND is_user_defined = 0
                                           AND [Types].name <> 'sysname'
        LEFT OUTER JOIN sys.extended_properties AS [Properties] ON [Properties].major_id = [Tables].object_id
                                                              AND [Properties].minor_id = [Columns].column_id
                                                              AND [Properties].name = 'MS_Description'
WHERE   [Tables].name ='T_Logs' -- and [Columns].name = '字段名'
ORDER BY [Columns].column_id

 ----

--增加字段注释
EXEC sp_addextendedproperty 
'MS_Description', '性别123',
 
 'user', dbo, 'table',T_Logs,  --表名 
  'column', LogType; --列名


---更新字段注释
EXEC sp_updateextendedproperty 'MS_Description',
 
'性别3',

 'user', dbo, 'table',T_Logs,  --表名 
  'column', LogType; --列名