主键：
@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