Sql Server 数据字典,查询所有的表以及结构

1.查询所有的表

----1.查询所有的表
SELECT name FROM SysObjects Where XType='U' ORDER BY Name

   2. 查询所有的表以及结构

-----2.查询所有的表以及结构
SELECT     
    表名       = case when a.colorder=1 then d.name else '' end,    
    表说明     = case when a.colorder=1 then isnull(f.value,'') else '' end,    
    字段序号   = a.colorder,    
    字段名     = a.name,    
    标识       = case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end,    
    主键       = case when exists(SELECT 1 FROM sysobjects where xtype='PK' and parent_obj=a.id and name in (    
                     SELECT name FROM sysindexes WHERE indid in( SELECT indid FROM sysindexkeys WHERE id = a.id AND colid=a.colid))) then '√' else '' end,    
    类型       = b.name,    
    占用字节数 = a.length,    
    长度       = COLUMNPROPERTY(a.id,a.name,'PRECISION'),    
    小数位数   = isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0),    
    允许空     = case when a.isnullable=1 then '√'else '' end,    
    默认值     = isnull(e.text,''),    
    字段说明   = isnull(g.[value],'')    
FROM syscolumns a left join systypes b     
on a.xusertype=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.major_id and f.minor_id=0    
--where d.name= 'tbl_Ecu'    --如果只查询指定表,加上此条件    
order by a.id,a.colorder 

• 单表： 

SELECT    a.colorder '序号-Colorder',a.name '列名-ColumnName' ,b.name '数据类型-TypeName', 
isnull(convert(varchar(5),
    CASE When b.name ='uniqueidentifier' Then 36  
         WHEN (charindex('int',b.name)>0) OR (charindex('time',b.name)>0) THEN NULL 
    ELSE  COLUMNPROPERTY(a.id,a.name,'PRECISION') end),'') as '长度-Length',
(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 '1' else '' end
) '主键-IsPK', 
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '1' else '' end) '自增-IsIdentity', 
(case when a.isnullable=1 then '1' else '' end) '允许空-CanNull', 
Replace(Replace(IsNull(e.text,''),'(',''),')','') '默认值-DefaultVal', 
isnull(g.[value], ' ') AS '列说明-DeText'
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  And g.class=1 left join sys.extended_properties f 
on d.id=f.class and f.minor_id=0 
where b.name is not NULL and d.name=表名 
order by a.id,a.colorder

结果：  

       

3.  主键类型和列情况

;with tb as(  
SELECT tbl.name AS TableName,i.name AS IndexName,clmns.name AS ColumName,i.is_primary_key AS isPrimaryKey,i.type_desc  
FROM sys.tables AS tbl    
INNER JOIN sys.indexes AS i ON (i.index_id > 0 and i.is_hypothetical = 0) AND (i.object_id=tbl.object_id)    
INNER JOIN sys.index_columns AS ic ON (ic.column_id > 0     
    AND (ic.key_ordinal > 0 or ic.partition_ordinal = 0 or ic.is_included_column != 0))     
    AND (ic.index_id=CAST(i.index_id AS int) AND ic.object_id=i.object_id)    
INNER JOIN sys.columns AS clmns ON clmns.object_id = ic.object_id and clmns.column_id = ic.column_id    
WHERE SCHEMA_NAME(tbl.schema_id) = N'dbo'  
)  
SELECT DISTINCT TableName,IndexName,isPrimaryKey,type_desc  
,STUFF((SELECT ','+ColumName FROM tb B WHERE A.TableName=B.TableName AND A.IndexName=B.IndexName FOR XML PATH('')),1,1,'') AS ColumName      
FROM tb A ORDER BY TableName,IndexName,isPrimaryKey,type_desc  

 

转自：(19条消息) sql server 数据库系统表及表结构查询_蝈蝈(GuoGuo)的博客-CSDN博客_sql查询数据库表结构