-- 查询表中所有的主键
SELECT A.COLUMN_NAME FROM INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE A JOIN ( SELECT * FROM sysobjects WHERE xtype = N'PK' ) B ON object_id(A.CONSTRAINT_NAME) = B.id WHERE a.table_name = '表'
-- 查询表中所列是否自增列
SELECT columnproperty(object_id('表'),'列','IsIdentity') [RESULT]
-- 获取表中各列和类型
SELECT c.name, t.name AS 'type',c.length, c.isnullable, IsPrimaryKey = CASE WHEN EXISTS ( SELECT 1 FROM sysobjects WHERE xtype = 'PK' AND parent_obj = c.id AND name IN ( SELECT [name] FROM sysindexes WHERE indid IN ( SELECT indid FROM sysindexkeys WHERE id = c.id AND colid = c.colid ))) THEN '1' ELSE '0' END FROM syscolumns c INNER JOIN sysobjects o ON c.id = o.id AND o.xtype = 'U' AND o.[name] = '表' INNER JOIN systypes t ON t.xusertype = c.xusertype