sql擷取表結構

標籤：for   dtp   orm   else   char   sql   extend   column   mat   

SELECT a.name 欄位名,            (case when COLUMNPROPERTY( a.id,a.name,‘IsIdentity‘)=1 then 1 ELSE 0 end) 標識,             (case when (SELECT count(*) FROM dbo.sysobjects              WHERE (name in (SELECT name FROM dbo.sysindexes              WHERE (id = a.id) AND (indid in              (SELECT indid FROM dbo.sysindexkeys              WHERE (id = a.id) AND (colid in              (SELECT colid FROM dbo.syscolumns WHERE (id = a.id) AND (name = a.name)))))))              AND (xtype = ‘PK‘))>0 then 1 else 0 end) 主鍵,b.name 類型,            h.CHARACTER_MAXIMUM_LENGTH as 長度,h.NUMERIC_PRECISION as 數字長度,            isnull(h.NUMERIC_SCALE,0) as 小數位元,(case when a.isnullable=1 then 1 ELSE 0 end) 允許空,              isnull(e.text,‘‘) 預設值,isnull(g.[value], ‘ ‘) AS [說明]            FROM  dbo.syscolumns a             left join dbo.systypes b on a.xtype=b.xusertype              inner join dbo.sysobjects d on a.id=d.id and d.xtype=‘U‘ and d.name<>‘dtproperties‘             left join dbo.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.class and f.minor_id=0            LEFT JOIN information_schema.COLUMNS h ON h.TABLE_NAME = d.name AND h.COLUMN_NAME = a.name            --where b.name is not null            WHERE d.name=‘StockInfo‘ --如果只查詢指定表,加上此條件            order by a.id,a.colorder

 

sql擷取表結構