获取SqlServer数据表所有字段的信息

获取SqlServer数据表所有字段的信息

2007-04-16 15:06

SELECT TOP 100 PERCENT dbo.sysobjects.name AS tb_name,        dbo.syscolumns.name AS col_name, dbo.systypes.name AS col_type,        dbo.syscolumns.length AS col_len, ISNULL(dbo.sysproperties.[value],        dbo.syscolumns.name) AS col_memo, CASE WHEN SysColumns.name IN           (SELECT id = a.name          FROM syscolumns a INNER JOIN                sysobjects b ON a.id = b.id AND b.xtype = 'U' AND                 b.name <> 'dtproperties'          WHERE EXISTS                    (SELECT 1                   FROM sysobjects                   WHERE xtype = 'PK' AND name IN                             (SELECT name                            FROM sysindexes                            WHERE indid IN                                      (SELECT indid                                     FROM sysindexkeys                                     WHERE id = a.id AND colid = a.colid))) AND                 b.name = SysObjects.Name) THEN 1 ELSE 0 END AS is_keyFROM dbo.sysproperties RIGHT OUTER JOIN       dbo.sysobjects INNER JOIN       dbo.syscolumns ON dbo.sysobjects.id = dbo.syscolumns.id INNER JOIN       dbo.systypes ON dbo.syscolumns.xtype = dbo.systypes.xtype ON        dbo.sysproperties.id = dbo.syscolumns.id AND        dbo.sysproperties.smallid = dbo.syscolumns.colidWHERE (dbo.sysobjects.xtype = 'u' OR       dbo.sysobjects.xtype = 'v') AND (dbo.systypes.name <> 'sysname') AND        (dbo.sysobjects.name LIKE '%') AND (dbo.sysobjects.name = '这里是数据表名')ORDER BY dbo.sysobjects.name, dbo.syscolumns.colid