SQL Server2008中通过SQL获取表结构

淩亂°似流年 2022-08-07 06:44 150阅读 0赞

# SQL Server2008中通过SQL获取表结构 #

新增数据用户，角色为public，映射到待获取表结构的数据库上，授与用户在该数据库上的身份为db\_owner

执行如下SQL语句：

select 
    syscolumns.name as [Name] ,
    systypes.name as [Type],
    syscolumns.length AS [Size],
    syscolumns.xprec As [Precision], 
    ISNULL(syscolumns.scale, 0) AS [Scale],
    sys.extended_properties.value as [Mark],
    syscolumns.isnullable as [NULL],
    (Case when exists(
    select 1 from sysobjects
     inner join sysindexes on sysindexes.name = sysobjects.name  
     inner join sysindexkeys on sysindexes.id = sysindexkeys.id and  sysindexes.indid = sysindexkeys.indid 
     where xtype='PK' and parent_obj = syscolumns.id    
     and sysindexkeys.colid = syscolumns.colid) then 1 else 0 end) AS [PK],
    (Case syscolumns.status when 128 then 1 else 0 end) AS [Identity]
     from syscolumns  
     inner join systypes on (  
    syscolumns.xtype = systypes.xtype  
     and systypes.name <>'_default_'   
     and systypes.name<>'sysname')  
     left outer join sys.extended_properties on (  
    sys.extended_properties.major_id=syscolumns.id   
     and minor_id=syscolumns.colid)  
     where syscolumns.id = (select id from sysobjects where name='NDS_TFileForm') 
     order by syscolumns.colid

SQL Server2008中通过SQL获取表结构

发表评论取消回复

还没有评论，来说两句吧...

相关阅读