获取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_key FROM 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.colid WHERE (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