SQL server 获得 表的主键,自增键

主键:

@tableName --表名

@id ---表对应的id

   SELECT SYSCOLUMNS.name FROM SYSCOLUMNS,SYSOBJECTS,SYSINDEXES,SYSINDEXKEYS WHERE SYSCOLUMNS.id = object_id(@tableName) AND SYSOBJECTS.xtype = 'PK' AND SYSOBJECTS.parent_obj = SYSCOLUMNS.id AND SYSINDEXES.id = SYSCOLUMNS.id AND SYSOBJECTS.name = SYSINDEXES.name AND SYSINDEXKEYS.id = SYSCOLUMNS.id AND SYSINDEXKEYS.indid = SYSINDEXES.indid AND SYSCOLUMNS.colid = SYSINDEXKEYS.colid

 

自增键:

SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=@tableName AND  COLUMNPROPERTY(OBJECT_ID(@tableName),COLUMN_NAME,'IsIdentity')=1

 

列名说明字段:

select name,id,xtype from dbo.sysobjects where name in (‘@tableName’)

select a.name,value from sys.extended_properties b, dbo.syscolumns a where  a.colid=b.minor_id  and a.id=b.major_id and a.id =@id  and a.iscomputed=0 

 

所有列名:

select columnName,typeName from (select a.*,b.name as typeName,b.length as typeLength,colid as columnId from (select id as objectId,name as columnName,xtype,length as columnLength,colid from dbo.syscolumns where id = @id ) a left join dbo.systypes b on a.xtype=b.xtype) t where typeName <> 'sysname' order by columnId

 

所有可编辑列名: 去掉主键列、自增键列、表内计算列

 

select columnName,typeName from (select a.*,b.name as typeName,b.length as typeLength,colid as columnId from (select id as objectId,name as columnName,xtype,length as columnLength,colid from dbo.syscolumns where id = @id  and iscomputed=0 and name not in(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME=@tableName AND  COLUMNPROPERTY(OBJECT_ID(@tableName),COLUMN_NAME,'IsIdentity')=1)) a left join dbo.systypes b on a.xtype=b.xtype) t where typeName <> 'sysname' order by columnId

转载于:https://www.cnblogs.com/cuozhixu/p/8315546.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值