SQL SERVER 批量查询指定表的表结构

来源：互联网发布：文学知乎编辑：程序博客网时间：2024/05/16 11:45

查询sql server数据库中表的结构

--字段数据类型：C:定长字符型(CHAR);VC:可变字符型(VARCHAR);N:数字或者浮点型;D:日期型;

--可以为空字段：Y表示可以为空，N表示不可以为空

最终按如下规则展现：

select obj.name c_tbname,UPPER(col.name) c_colname,sep.value c_coldesc,       ( select case when d.name in('varchar','nvarchar','text') then 'VC'                     when d.name in('char','nchar')       then 'C'                     when d.name in('numeric','real','int','smallint','bigint','float','money','decimal') then 'N'                     when d.name in('datetime','smalldatetime') then 'D'                     else '#'                 end c_stype         from systypes c,systypes d         where c.xtype = d.xtype and d.xtype = d.xusertype and col.xtype = c.xusertype         ) c_systype,       case when col.xscale > 0 then col.xprec            when col.xscale = 0 then col.length            else null       end l_length,       case when col.xscale>0 then col.xscale            else null       end  l_xscale,       case when col.isnullable=0 then 'N'            when col.isnullable=1 then 'Y'            else '#'            end c_isnullfrom sysobjects obj,     syscolumns col left join sys.extended_properties sep on col.id = sep.major_id and col.colid = sep.minor_idwhere obj.id = col.id   and obj.xtype='U'   and  obj.name in('表名'，'.....' )order by obj.name,col.colorder;

0 0