private const string str1 = "select * from("; private const string str2 = "select distinct c.colid,c.name,c.xtype,c.xprec,c.xscale,c.isnullable,case when c.autoval is null then 0 else 1 end as autoval,"; private const string str3 = "case when exists(select 1 from (select c.name from sysindexes i join sysindexkeys k on i.id = k.id and i.indid = k.indid join sysobjects o on i.id = o.id join syscolumns c on i.id=c.id and k.colid = c.colid where o.xtype = 'U' and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name) and o.name=@tablename) t where t.name=c.name) then 1 else 0 end as iskey,"; private const string str4 = "case when exists(select 1 from (select col_name(a.parent_obj,b.fkey) as col,object_name(b.rkeyid) as ftab,col_name(b.rkeyid,b.rkey) as fcol from sysobjects a join sysforeignkeys b on a.id=b.constid where a.name in(select name from sysobjects where xtype='f' and parent_obj in(select id from sysobjects where name=@tablename))) f where isnull(f.col,'')=c.name) then 1 else 0 end as isfkey,"; private const string str5 = "c.cdefault,f.ftab,f.fcol "; private const string str6 = "from syscolumns c left join (select col_name(a.parent_obj,b.fkey) as col,object_name(b.rkeyid) as ftab,col_name(b.rkeyid,b.rkey) as fcol from sysobjects a join sysforeignkeys b on a.id=b.constid where a.name in(select name from sysobjects where xtype='f' and parent_obj in(select id from sysobjects where name=@tablename))) f on f.col=c.name "; private const string str7 = "where c.id in(select id from sysobjects where name = @tablename)"; private const string str8 = ") a order by a.colid"; private const string SQL_SELECT_ALL_COLUMN = str1+str2+str3+str4+str5+str6+str7+str8;执行这句,就可以得到一个表@tablename的所有主键,字段类型,长度,外键等信息.
我只要获取自动ID的ID号就可以,不用这么详细的信息,可有简便点的?
1:利用SQL自带的存储过程EXEC sp_pkeys @table_name='表名'2:利用系统表INFORMATION_SCHEMA.KEY_COLUMN_USAGE SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE TABLE_NAME='表名'3: 以下这个查询表结构的语句可能大家都见过:利用syscolumns,sysindexes,sysindexkeys三个表关联可以查出主键SELECT (case when a.colorder=1 then d.name else '' end) N'表名', a.colorder N'字段序号', a.name N'字段名', (case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识', (case when (SELECT count(*) FROM sysobjects WHERE (name in (SELECT name FROM sysindexes WHERE (id = a.id) AND (indid in (SELECT indid FROM sysindexkeys WHERE (id = a.id) AND (colid in (SELECT colid FROM syscolumns WHERE (id = a.id) AND (name = a.name))))))) AND (xtype = 'PK'))>0 then '√' else '' end) N'主键', b.name N'类型', a.length N'占用字节数', COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度', isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数', (case when a.isnullable=1 then '√'else '' end) N'允许空', isnull(e.text,'') N'默认值', isnull(g.[value],'') AS N'字段说明' --into ##txFROM syscolumns a left join systypes b on a.xtype=b.xusertype inner join sysobjects d on a.id=d.id and d.xtype='U' and d.name<>'dtproperties' left join syscomments e on a.cdefault=e.id left join sysproperties g on a.id=g.id AND a.colid = g.smallid order by object_name(a.id),a.colorder 4: select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序 from sysindexes i join sysindexkeys k on i.id = k.id and i.indid = k.indid join sysobjects o on i.id = o.id join syscolumns c on i.id=c.id and k.colid = c.colid where o.xtype = 'U' and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name) order by o.name,k.colid 借用一下
private const string str2 = "select distinct c.colid,c.name,c.xtype,c.xprec,c.xscale,c.isnullable,case when c.autoval is null then 0 else 1 end as autoval,";
private const string str3 = "case when exists(select 1 from (select c.name from sysindexes i join sysindexkeys k on i.id = k.id and i.indid = k.indid join sysobjects o on i.id = o.id join syscolumns c on i.id=c.id and k.colid = c.colid where o.xtype = 'U' and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name) and o.name=@tablename) t where t.name=c.name) then 1 else 0 end as iskey,";
private const string str4 = "case when exists(select 1 from (select col_name(a.parent_obj,b.fkey) as col,object_name(b.rkeyid) as ftab,col_name(b.rkeyid,b.rkey) as fcol from sysobjects a join sysforeignkeys b on a.id=b.constid where a.name in(select name from sysobjects where xtype='f' and parent_obj in(select id from sysobjects where name=@tablename))) f where isnull(f.col,'')=c.name) then 1 else 0 end as isfkey,";
private const string str5 = "c.cdefault,f.ftab,f.fcol ";
private const string str6 = "from syscolumns c left join (select col_name(a.parent_obj,b.fkey) as col,object_name(b.rkeyid) as ftab,col_name(b.rkeyid,b.rkey) as fcol from sysobjects a join sysforeignkeys b on a.id=b.constid where a.name in(select name from sysobjects where xtype='f' and parent_obj in(select id from sysobjects where name=@tablename))) f on f.col=c.name ";
private const string str7 = "where c.id in(select id from sysobjects where name = @tablename)";
private const string str8 = ") a order by a.colid";
private const string SQL_SELECT_ALL_COLUMN = str1+str2+str3+str4+str5+str6+str7+str8;执行这句,就可以得到一个表@tablename的所有主键,字段类型,长度,外键等信息.
WHERE TABLE_NAME='表名'3: 以下这个查询表结构的语句可能大家都见过:利用syscolumns,sysindexes,sysindexkeys三个表关联可以查出主键SELECT
(case when a.colorder=1 then d.name else '' end) N'表名',
a.colorder N'字段序号',
a.name N'字段名',
(case when COLUMNPROPERTY( a.id,a.name,'IsIdentity')=1 then '√'else '' end) N'标识',
(case when (SELECT count(*)
FROM sysobjects
WHERE (name in
(SELECT name
FROM sysindexes
WHERE (id = a.id) AND (indid in
(SELECT indid
FROM sysindexkeys
WHERE (id = a.id) AND (colid in
(SELECT colid
FROM syscolumns
WHERE (id = a.id) AND (name = a.name))))))) AND
(xtype = 'PK'))>0 then '√' else '' end) N'主键',
b.name N'类型',
a.length N'占用字节数',
COLUMNPROPERTY(a.id,a.name,'PRECISION') as N'长度',
isnull(COLUMNPROPERTY(a.id,a.name,'Scale'),0) as N'小数位数',
(case when a.isnullable=1 then '√'else '' end) N'允许空',
isnull(e.text,'') N'默认值',
isnull(g.[value],'') AS N'字段说明'
--into ##txFROM syscolumns a left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype='U' and d.name<>'dtproperties'
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
4:
select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序 from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
where o.xtype = 'U'
and exists(select 1 from sysobjects where xtype = 'PK' and name = i.name)
order by o.name,k.colid
借用一下