| 注册
请输入搜索内容

热门搜索

Java Linux MySQL PHP JavaScript Hibernate jQuery Nginx
bbw3
10年前发布

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